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