Excel Pull from Access

jonnyp138

Board Regular
Joined
May 2, 2015
Messages
50
Hi All - I currently have a vba macro setup to import data from MSACCESS into Excel using the following command which works great

Set ShDest = Sheets("Excel Sheet Name")

Set cnn = New ADODB.Connection
MyConn = TARGET_DB

With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MyConn
End With

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:="[Database Table]", ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdTable

'clear existing data on the sheet
ShDest.Activate
Range("A1").CurrentRegion.Offset(1, 0).Clear

'create field headers
i = 0
With Range("A1")
For Each fld In rst.Fields
.Offset(0, i).Value = fld.Name
i = i + 1
Next fld
End With

'transfer data to Excel
Range("A2").CopyFromRecordset rst

' Close the connection
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing



The database contains all of our customer information and this query pulls all the data out of the table into Excel - What I can't figure out is how to use this to only pull the data for specific customers only.

Can anyone assist?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Unfortunately the amount of data I am pulling is too much and I need to add a SQL query to only pull what I need however my SQL isn't up to much, can you tell me where I would modify the above code to insert the SQL query?

Thanks
 
Upvote 0
You would replace Source:="[Database Table]" with a valid SQL statement.

To get the general syntax for that create a query in the Access database that returns the fields you want from your source table, maybe add some example query, and then goto SQL view.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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
Back
Top