Posts by DaveUK31

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

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"))

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

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.

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