Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Data From Access
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Data From Access
"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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Data From Access
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 "moon" wrote in message . .. "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Data From Access
"Goofy" schreef in bericht ... 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 :-p Params can be passed in the SQL-string, like: db.Open "SELECT * FROM [QUERY] WHERE fieldname=fieldvalue", dc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Data From Access
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. "moon" wrote in message . .. "Goofy" schreef in bericht ... 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 :-p Params can be passed in the SQL-string, like: db.Open "SELECT * FROM [QUERY] WHERE fieldname=fieldvalue", dc |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Data From Access
Definitely no clue, but maybe this will help:
http://www.access-programmers.co.uk/...ad.php?t=92676 "Goofy" schreef in bericht ... 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. "moon" wrote in message . .. "Goofy" schreef in bericht ... 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 :-p Params can be passed in the SQL-string, like: db.Open "SELECT * FROM [QUERY] WHERE fieldname=fieldvalue", dc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can Excel access data from Access?! | Excel Discussion (Misc queries) | |||
Importing data from Access to Excel, but I need to vary the table from Access | Excel Programming | |||
Calculate data from Access and return to Access | Excel Programming | |||
Transfer Data from Excel 2000 to the Access data base | Excel Programming | |||
Access data -work in Excel- save in Access | Excel Programming |