Getting Data From Access

[Goofy]

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

 

[moon]

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

 

[Goofy]

Thats really great, thanks for your help. The only thing missing here is how
to add the parameters to the query because it has two.

Cheers

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

 

[moon]

Thats really great, thanks for your help. The only thing missing here is
how to add the parameters to the query because it has two.

Cheers

I know. Sorry.
I saw it after posting


Params can be passed in the SQL-string, like:
db.Open "SELECT * FROM [QUERY] WHERE fieldname=fieldvalue", dc

 

[Goofy]

Unfortunately, this wont do it. The reason is that the query is a union of
three queries and each have a where clause. In order to facilitate this I
really do need to use parameters.

Thanks in Anticipation.

Thats really great, thanks for your help. The only thing missing here is
how to add the parameters to the query because it has two.

Cheers

I know. Sorry.
I saw it after posting


Params can be passed in the SQL-string, like:
db.Open "SELECT * FROM [QUERY] WHERE fieldname=fieldvalue", dc

 

[moon]

Definitely no clue, but maybe this will help:
http://www.access-programmers.co.uk/forums/showthread.php?t=92676

Unfortunately, this wont do it. The reason is that the query is a union of
three queries and each have a where clause. In order to facilitate this I
really do need to use parameters.

Thanks in Anticipation.

Thats really great, thanks for your help. The only thing missing here is
how to add the parameters to the query because it has two.

Cheers

I know. Sorry.
I saw it after posting


Params can be passed in the SQL-string, like:
db.Open "SELECT * FROM [QUERY] WHERE fieldname=fieldvalue", dc