Textfile-based database from Excel 2010 VBA

Question

I am trying to establish a databases which uses mere text files as datasources for its tables. This seems to be the only option because I cannot install anything different on corporate PCs. I have tried to use a variety of connection strings to initialize the database (see code below).

I have two issues: firstly, if I manage to connect with strConnectionString2, there is the error that the decimal separator (coma) matches text delimiter (coma). It is NOT an option to change anything in registry because the PCs are heavily protected.

Second issue: if I use ConnectionString3, I get an error message 'Data source name too long'. When I try using a shorter one, it says that the data source cannot be found. The system is 64 bit Windows 7

Any help will be very much appreciated. Alternative methods are also accepted but they cannot involve installing any additional software and changing anything in directory. Changing decimal separator in Excel is not an option as well becuase it mihgt crash down other workflows.

Sub testDatabase()

Dim strDatabaseDirectory As String
Dim strConnectionString1 As String
Dim strConnectionString2 As String
Dim strConnectionString3 As String
Dim dbMurenaDatabase As ADODB.Connection
Dim rcsRecords As ADODB.Recordset

strDatabaseDirectory1 = ThisWorkbook.path & "\database\"
strDatabaseDirectory2 = "Libraries\Documents"
strDatabaseDirectory3 = "C:\db"
strConnectionString1 = "Data Source='" & strDatabaseDirectory & "';Delimiter=';'"
strConnectionString2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabaseDirectory1 & ";" & _
                        "Extended Properties=""" & "text;HDR=Yes;FMT=Delimited""" & ";"
strConnectionString3 = "Data Source='" & strDatabaseDirectory3 & "';Delimiter=';';" & _
                        "Has Quotes=True;Skip Rows=0;Has Header=True;Comment Prefix='#';" & _
                        "Column Type=String,String,String,Int32,Boolean,String,String;" & _
                        "Trim spaces=False;Ignore Empty Lines=True;"

Set dbMurenaDatabase = New ADODB.Connection
dbMurenaDatabase.Open strConnectionString3

dbMurenaDatabase.Execute "CREATE TABLE [firstTable.txt] (FirstCol TEXT, SecCol TEXT)"
dbMurenaDatabase.Execute "INSERT INTO [firstTable.txt] Values ('smth', 'smth2');"
dbMurenaDatabase.Execute "SELECT * FROM firstTable.txt"

End Sub

Have you tried Get&Transform? Seems a good fit for your case — OwlsSleeping
– OwlsSleeping, Commented Sep 27, 2018 at 11:07
@OwlsSleeping thank you, maybe it would make sense to use this instead but I work with Excel 2010 — Serhii
– Serhii, Commented Sep 27, 2018 at 11:21
I had the same issue with corporate PCs, the only solution I found was to use xml files (One file for each table). Regarding the tables relationships, well, you need to be a good VBA coder ;) — Terminader
– Terminader, Commented Sep 27, 2018 at 11:27
@NaderNagazi but you didn't connect to them with a database library? just used XPath or something to query the XMLs? — Serhii
– Serhii, Commented Sep 27, 2018 at 12:12

Serhii · Accepted Answer · 2018-09-27 13:09:46Z

I know that VBA is dying and no one will probably find it useful but here is the solution.

The driver to use. The driver I used was Microsoft Text ODBC Driver. what it does, it creates a database whose data storage are mere text files.

It is declared as follows:

Dim strDatabaseDirectory As String
Dim strConnectionString1 As String
Dim strConnectionString2 As String
Dim strConnectionString3 As String
Dim dbMurenaDatabase As ADODB.Connection
Dim rcsRecords As ADODB.Recordset

strDatabaseDirectory1 = ThisWorkbook.path & "\database\"
strConnectionString1 = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" &    strDatabaseDirectory1 & ";" & _
                        "Extensions=asc,csv,tab,txt;"

Now that you have initialized the database, you have not set the parameters for the tables yet. This potentially leads to conflicts if your decimal separator and database delimiter are the same. the solution is super simple: you have to set all the parameters in an .ini file (just create a text file and change extension). An example of how to do it is here: https://www.connectionstrings.com/microsoft-text-odbc-driver/info-and-download/

There you set a delimiter and fields properties. CREATE TABLE from VBA didn't work for me because there you can't specify delimiters. So you create all the text files (tables), the .ini file, and everything works just fine.

Collectives™ on Stack Overflow

Textfile-based database from Excel 2010 VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related