Best way to SELECT query a DB table using VBA ?

[JumboCactuar]

Hi,

i have used both of the following which pretty much output the same result:

But im not sure which is best to use? Or maybe a more efficient way
Never used recordset before today

Sub SelectData1()

  Dim cn      As Object   'Connection
    Dim rs      As Object   'Recordset
    Dim vAry    As Variant  'Variant Array

    MyConn = "C:\TEST\EXAMPLEDB.MDB"
    Set cnn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
   
    SQLQUERY = "Select * From TABLE1"
  
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & MyConn & ""
    rs.Open SQLQUERY, cnn
   
    Range("A2").CopyFromRecordset rs
  
    rs.Close
    cnn.Close
  
    Set rs = Nothing
    Set cn = Nothing
   
End Sub

Sub SelectData2()

MyConn = "C:\TEST\EXAMPLEDB.MDB"

SQLQUERY = "Select * From TABLE1"

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=" & MyConn & ";DefaultDir=" & myDBdir & ";DriverId=2;FIL=MS Access;MaxBufferSize" _
        ), Array("=2048;PageTimeout=5;")), Destination:=Range("$a$1")).QueryTable
        .CommandText = SQLQUERY
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
   
Range("A1:Z10000").Value2 = Range("A1:Z10000").Value2 'This just gets rid of the listobject
   
End Sub

 

[DJ Peterson]

You did not say if this is actually working or not. I've only been using Recordsets for a little while. Did you remember to add the proper driver using Tools - References from the VBA screen? I sometimes forget to do this...

 

[JumboCactuar]

You did not say if this is actually working or not. I've only been using Recordsets for a little while. Did you remember to add the proper driver using Tools - References from the VBA screen? I sometimes forget to do this...

Hi
Yes they both work the same, no references needed

Just really want to know pros/cons of the 2