Best way to SELECT query a DB table using VBA ?

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
720
Office Version
  1. 365
Platform
  1. Windows
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


VBA Code:
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

VBA Code:
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

DJ Peterson

New Member
Joined
Jan 23, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2016
Messages
720
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,340
Members
414,059
Latest member
Amro El ghazawei

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top