Advice re specific ADO questions

[swallis]

I want to use a table in Access as a repository for several million records, from which I can bring all Fields and all Records for selected Criteria back to Excel. The criteria will be a list of about 150 names, which could mean up to 4000+ records. Thanks to help from this forum I have an OBDC (Ms-Query) solution which works, but ADO is supposed to be faster and (?) better, so I've managed to produce the following code which also works. Testing with a database of 330,000 records gives almost no difference in search time between the 2 solutions. My testing has been inexact, but it seems the number of Names in the list is the main time factor, number of Records transferred is irrelevant and the size of the Database seems to add a few seconds with each doubling. Before I commit to many hours of loading data, I'm hoping someone can spare the time to consider these circumstances and advise on the following:

1 - Would a DAO solution be likely to speed things up?
2 - Moving a name from the list to another cell, then searching seems cumbersome, as does opening and closing the ADO (Or OBDC) connection for each search, but I've not found any other way. I'm hoping for ideas here?
3 - Is there a way to Copy ALL fields other than listing them individually in the Sql query? I'm expecting to change number, position and titles, but will always take all fields back to excel.
4 - Cursor type 0 is supposed to be faster than 1. Is it appropriate here - I don't really understand what "forward only" means in this context? (Testing doesn't seem to change results or time.)

Thanks for any response and for any other advice or suggestions.

Steve W

Sub LoopyLoo3()
Dim t As Single
t = Timer
Application.ScreenUpdating = False
Dim RngA, celA As Range
Dim I As Variant
Set RngA = Range("ar2", Range("ar" & Rows.Count).End(xlUp))
Set I = Rows(1)
Set celA = Range("aq1")
For Each I In RngA
     If celA.Value <> I.Value Then celA.Value = I.Value
     Call ADOQuery
Next I
Sheets("Form").Columns("A:AP").AutoFit
Application.ScreenUpdating = True
MsgBox Timer - t
End Sub
Sub ADOQuery()
    Dim con         As Object
    Dim rs          As Object
    Dim AccessFile  As String
    Dim strTable    As String
    Dim SQL         As String
    Dim I           As Integer
    Dim Hrse        As String
    Hrse = Range("AQ1")
    Hrse = Replace(Hrse, "'", "''")
    AccessFile = "C:Racing\Racing 2016\Racing Form.mdb"
    strTable = "Form2"
    
    On Error Resume Next
    Set con = CreateObject("ADODB.connection")
    If Err.Number <> 0 Then
        MsgBox "Connection was not created!", vbCritical, "Connection Error"
        Exit Sub
    End If
    On Error GoTo 0
        
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & AccessFile
    SQL = "SELECT Form2.HORSE, Form2.FIN, Form2.STR, Form2.MARG, Form2.DATE, Form2.TRACK, Form2.M, Form2.RNO, Form2.PRIZE, Form2.PRWIN, Form2.EVENT, Form2.CLASS, Form2.AGE, Form2.REST, Form2.G, Form2.DIST, Form2.TIME, Form2.SDIST, Form2.STIME, Form2.OP, Form2.MP, Form2.SP, Form2.WGT, Form2.[ALL], Form2.LIM, Form2.JOCKEY, Form2.BP, Form2.SD, Form2.TW, Form2.EI, Form2.FO, Form2.F1, Form2.OTHER1, Form2.WGT1, Form2.F2, Form2.OTHER2, Form2.WGT2, Form2.F3, Form2.OTHER3, Form2.WGT3, Form2.TRT, Form2.WRT FROM " & strTable & " WHERE Horse='" & Hrse & "'"
    
    On Error Resume Next
    Set rs = CreateObject("ADODB.Recordset")
    If Err.Number <> 0 Then
        Set rs = Nothing
        Set con = Nothing
        MsgBox "Recordset was not created!", vbCritical, "Recordset Error"
        Exit Sub
    End If
    On Error GoTo 0
    
    rs.CursorLocation = 3
    rs.CursorType = 1
    
    rs.Open SQL, con
 
    '(error trap for no records in recordset deleted as I expect this sometimes)
    
    Sheets("Form").Range("A" & Rows.Count).End(xlUp).Offset(1).CopyFromRecordset rs
    
    rs.Close
    con.Close
    Set rs = Nothing
    Set con = Nothing
End Sub

 

[swallis]

While hoping for a response here, I've been searching for a DAO solution, but it seems that it has to be run from within Access. Is that correct or am I not looking in the right places?

 

[Derek Brown]

Both ADO and DAO can be used in Excel - you just need to select the appropriate reference(s) in the VBE (Tools | References):
Microsoft ActiveX Data Objects 6.1 Library
Microsoft DAO 3.6 Object Library
Although the following link refers to Microsoft Access, it is very much relevant to Microsoft Excel.
Choosing between DAO and ADO - Access wiki - Access Help and How-to - Microsoft Office by UtterAccess.com
You can select all records using "SELECT * FROM".

 

[swallis]

Thanks for the response Derek. The answer for all records is great. Thanks also for the reference. I know I'm fighting above my weight when I don't even understand most of the "Pros" & "Cons" for each. I will go and play further with it (now that DAO 3.6 is checked).

In the meantime, if you (or anyone else) could look at Question 2 I'd really appreciate it. I'm sure there must be some way to copy multiple Recordsets while the connection is open???

 

[Derek Brown]

I have not yet been able to find a DAO example amongst my code but if I were using ADO I would try something like the following.
Note that I have edited this code without testing it.

' Not tested
Sub MyProc2()
Dim wb As Workbook
Dim ws As Worksheet
Dim ws2 as Worksheet
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strDB_Path As String
Dim strSQL As String
Set wb = ThisWorkbook
Set ws = wb.Worksheets("My Sheet")
Set ws2 = wb.Worksheets("My Sheet 2")
strDB_Path = "C:\MyDatabases"
'
Set objConn = New ADODB.Connection
objConn.Provider = "Microsoft.ACE.OLEDB.12.0"
objConn.Open strDB_Path
If objConn.State = adStateOpen Then
	' First table:
	ws.Activate
	strSQL = "SELECT * FROM MyDatabaseTable;"
	Set objRS = New ADODB.Recordset
	objRS.Open strSQL, objConn, adOpenForwardOnly
	On Error Resume Next
	objRS.MoveFirst
	If Err.Number = 0 Then
		On Error GoTo 0
		ws.Cells(2, 1).CopyFromRecordset objRS
	End If
	On Error Resume Next
	objRS.Close
	Set objRS = Nothing
	' Second table:
	ws2.Activate
	strSQL = "SELECT * FROM MyDatabaseTable2;"
	Set objRS = New ADODB.Recordset
	objRS.Open strSQL, objConn, adOpenForwardOnly
	On Error Resume Next
	objRS.MoveFirst
	If Err.Number = 0 Then
		On Error GoTo 0
		ws2.Cells(2, 1).CopyFromRecordset objRS
	End If
	On Error Resume Next
	objRS.Close
	Set objRS = Nothing
	'
	objConn.Close
End If
Set objConn = Nothing
Set ws = Nothing
Set ws2 = Nothing
Set wb = Nothing
End Sub

 

[swallis]

Thanks Derek. That does what I want - hold the connection open while performing more than 1 search. However I'm searching a single table in access and bringing results back to a single Sheet in Excel. On that sheet is the list of names I want the records for (will eventually be on another sheet, or possibly another workbook). Somehow I want to loop through that list while the connection is open. I've been playing with it since I got your response to no avail and now I'm brain dead. Any help appreciated.

Steve W