Using VBA as a database tool via ADO

DaveUK31 · May 30th 2016

Hi Guys,

You'll see from the example attached I have the below code which will bring back certain records I require from a tab delimited file where the first column in the txt file matches the reference in cell B2.

This works great however I want it to work via an ADO macro (module 3) as it will work faster on a txt file with massive numbers of records. I am getting an error however on the ".open str" bit of the module 3 code. Can anyone help?

Below is the code I am trying to get to work as I understand for example if my txt file was huge (e.g. 2 million plus records) this would process much faster.

Code

Sub QueryTextFile()
    t = Timer
    Dim cnn As Object
    Dim str As String
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cnn.ConnectionString = "Data Source=C:\Users\Davids Laptop\Documents\Other Ad Hoc\Test Files\;Extended Properties=""text;HDR=Yes;FMT=Delimited;"""
    cnn.Open
      
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    str = "SELECT * FROM [test1.txt] WHERE [aa]=" & Chr(34) & Range("B2") & Chr(34)
  
    With rs
        .ActiveConnection = cnn
        .Open str
        Sheet1.Range("A4").CopyFromRecordset rs
        .Close
    End With
  
    cnn.Close
    MsgBox Timer - t
End Sub

Display More

cytop · May 30th 2016

Re: Using VBA as a database tool via ADO

Please update with links to your posts on other forums (plural)...

The solution is easy...

DaveUK31 · May 30th 2016

Re: Using VBA as a database tool via ADO

Apologies... here you go
http://www.mrexcel.com/forum/e…-reading-text-file-3.html

Note when I take the where clause out I can get this to work for all records however another thing I noticed is it brings back the full compressed string and I would like it to work how the other macro works within the sheet as noted above.

cytop · May 30th 2016

Re: Using VBA as a database tool via ADO

Thank you for that. I know this is really narrow minded of me but is there any reason why you post 1 link? The issue posted is "Using VBA as a database tool via ADO", not your extended issue as posted on MrExcel. Excluding the MrExcel thread, this is posted on at least 2 other forums

The whole idea of adding links is so anyone thinking of replying can check the issue either has not already been resolved, or that the whatever they are going to suggest has not already been suggested elsewhere - this is all explained in the link in my message to you.

I could just post the solution and forget about it, but that just means you keep posting as you do; there's a core principal of forums on the line here - help those who might help you.

DaveUK31 · May 30th 2016

Re: Using VBA as a database tool via ADO

Sorry, here are the other 2, I understand and will remember this in future posts. Any help you can give me will be greatly appreciated to help me with this problem

http://www.excelforum.com/show…51&highlight=#post4399251
http://stackoverflow.com/quest…#comment62523519_37515008

cytop · May 30th 2016

Re: Using VBA as a database tool via ADO

Thank you again. Please do remember links

I can't stand multiple double quote marks to delimit string within strings, so changed your connection string to

Code

cnn.ConnectionString = "Data Source=C:\Users\Davids Laptop\Documents\Other Ad Hoc\Test Files;Extended Properties=" & Chr(34) & "text;HDR=Yes;FMT=Delimited;" & Chr(34)

To my mind, it's easier to read. Also, the trailing '\' is not needed for the Data Source (Mentioned on Excelforum), but it also does not matter if it is included.

For your file, the SQL statement is wrong (Yes, I know that was my suggestion - it was blindly suggested as you had not attached a sample).

The SQL statement should read

Code

str = "SELECT * FROM [test1.txt] WHERE [aa]=" & Range("B2")

as B2 contains numbers, not strings.

The final change, a Tab delimited file needs a 'schema.ini' file (named literally as 'schema.ini') in the same directory as the source file(s). The structure of this file is

Code

[Test1.txt]
Format=TabDelimited
[Test1 (1).txt]
Format=TabDelimited

That's a bit of a pain, but the schema can contain definitions for multiple files - the enclosing '[' & ']' around the files names are needed.

You can use code to create the schema but I seem to remember it must be done before the Connection is opened - not 100% on that, but simple to test.

Final point, you really should qualify the Range address with the sheet name. The code will use the active sheet when executed. Not a problem with your sample if called from a button on the worksheet, but...

DaveUK31 · May 30th 2016

Re: Using VBA as a database tool via ADO

Thank you very much cytop this is excellent.
One last thing- this works on numbers (e.g. 123) but I would also want it to work on character references too (e.g. the ABC at the footer of the text file). Is this simple to do?
I tried changing the below but this still only worked on the numbers

Code

str = "SELECT * FROM [test1.txt] WHERE [aa]=" & CStr(Range("B2"))

cytop · May 30th 2016

Re: Using VBA as a database tool via ADO

You now have a problem in that the SQL statement must contain delimited strings, simply converting the value to a string with cStr() won't work...

Code

str = "SELECT * FROM [test1.txt] WHERE [aa]=" & Chr(34) & Range("B2").Value & Chr(34)

This SQL statement is exactly what I suggested initially.

However, the DB driver does not work like that. It scans the first few rows to determine the data type of each column and proceeds according to that. If your 'ABC' record appeared within the first few rows, it would assume mixed data. this can also be forced using the 'IMEX=1' statement in the connection String.

I'd guess you have no idea where in the list the strings will appear so the only suggestion really is to include either a directive to scan all rows (as in test1(1) below), or define the column types in the schema file, (test1).

Scanning all rows first will introduce a delay while the file is pre-processed.

Code

[Test1.txt]
Format=TabDelimited
Col1=AA Text
Col2=BB Text
Col3=CC Text
[Test1 (1).txt]
Format=TabDelimited
MaxScanRows=0

You are now getting out of the comfort zone for using ADO with this file - If all strings, no problem but mixed datatypes cause problems.

DaveUK31 · May 30th 2016

Re: Using VBA as a database tool via ADO

Thanks cytop I used the schema example above and this is working great across both. Thank you once again for your help & apologies for the cross posting (I won't do that again in the future).

Cheers

cytop · May 30th 2016

Re: Using VBA as a database tool via ADO

Quote

I won't do that again in the future

You could also mention is has been resolved on the other forums - same principal: Don't cause anyone to waste time.

poonsook · Jul 14th 2016

Re: Using VBA as a database tool via ADO

thks

Using VBA as a database tool via ADO

Participate now!

Share