Import Access Query to Excel with Excel VBA

Jose Miguel

Board Regular
Joined
Feb 3, 2005
Messages
99
I am using the following:

Sub GetQueryDef()
'This sub will get data from an Existing QueryDef in my
'database and place the data on sheet1.

Dim db As Database
Dim Qd As QueryDef
Dim rs As Recordset
Dim Ws As Object
Dim i As Integer
Dim Path As String

'Set the Path to the database. This line is useful because
'if your database is in another location, you just need to change
'it here and the Path Variable will be used throughout the code.
'

Path = "G:\London\Home Depot\Common\Jose Guido\CreditAppTrack 11-21-06 Original DO NOT USE.mdb"

'Set Ws
Set Ws = Sheets("SameDayDecision")

'This set of code will activate Sheet1 and clear any existing data.
'After clearing the data, it will select cell A8.
Ws.Activate
Range("A8").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A8").Select

'Set the Database and QueryDef. This QueryDef exists in the
'database.
Set db = Workspaces(0).OpenDatabase(Path, True, False)
Set Qd = db.QueryDefs("qry_Ops_SameDayDeci")
'Create a new Recordset from the Query based on the stored
'QueryDef.
Set rs = Qd.OpenRecordset()

'This loop will collect the field names and place them in the first
'row starting at "A8."
For i = 0 To rs.Fields.Count - 1
Ws.Cells(1, i + 1).Value = rs.Fields(i).Name
Next

'This line simply sets the font to bold for the headers.
Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, rs.Fields.Count)).Font.Bold _
= True

'The next line will get the data from the recordset and copy it
'into the Worksheet (Sheet1).

Ws.Range("A9").CopyFromRecordset rs

'This next code set will just select the data region and auto-fit
'the columns
Sheets("SameDayDecision").Select
Range("A8").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A8").Select

Qd.Close
rs.Close
db.Close

End Sub

However, I keep getting runtime error

"Error 3061, too few parameters. Expected 2."

The debugger is stopping at

Set rs = Qd.OpenRecordset()

Am I missing something when defining

db.QueryDefs("qry_Ops_SameDayDeci")

?

Please help!

Thanks.

Jose
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,450
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
Hi Jose

Try changing this line:
Code:
Set rs = Qd.OpenRecordset()

to this:
Code:
Set rs = Qd.OpenRecordset(dbOpenSnapshot, dbReadOnly)

Also, at the moment the query headers are being put into row 1. If you want the headers in row 8 then change this line:
Code:
Ws.Cells(1, i + 1).Value = rs.Fields(i).Name
to this:
Code:
Ws.Cells(8, i + 1).Value = rs.Fields(i).Name

and change this line:
Code:
 Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, rs.Fields.Count)).Font.Bold _
= True
to this:
Code:
 Ws.Range(Ws.Cells(8, 1), Ws.Cells(1, rs.Fields.Count)).Font.Bold _
= True

HTH, Andrew
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,204
Messages
5,857,907
Members
431,907
Latest member
RNN

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