"Goofy" schreef in bericht
...
I have an access database which has some queries in it, they have each have
two parapeters in them for year and week_no.

I need in VBA to be able to call a macro to open those queries haviong
passed the parameter values to them.

Can anyone offers sim simple code to do this or point me at an appropriate
reference.

Thanks In Advance


'Excel 2000 here
'Reference:
'Microsoft ActiveX Data Objects 2.8 Library

Public Sub GetAccQueryData()
Dim wb As Workbook
Dim ws As Worksheet
Dim db As ADODB.Recordset
Dim dc As ADODB.Connection
'rows & columns
Dim r, c As Integer
Set wb = ThisWorkbook
Set ws = wb.Sheets(1)
Set db = New ADODB.Recordset
Set dc = New ADODB.Connection
ws.Activate
'start in row 2 (row 1 = header)
r = 2: c = 1
dc.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=YOUR ACCESS DATABASE.mdb"
db.Open "SELECT * FROM [QUERY]", dc
db.MoveFirst
While Not db.EOF
ws.Cells(r, c).Value = db.Fields(0).Value
ws.Cells(r, (c + 1)).Value = db.Fields(1).Value
ws.Cells(r, (c + 2)).Value = db.Fields(2).Value
ws.Cells(r, (c + 3)).Value = db.Fields(3).Value
r = r + 1
db.MoveNext
Wend
dc.Close
Set dc = Nothing
Set db = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub