Retrieving data from Access DB in Excel VBA code
I have an Excel 2007 spreadsheet, MySheet.xlsm, and an Access 2007 dabase, MyDB.accdb. In cell A1 of Sheet1 I have a value.
I'd like to have a piece of VBA code which I runs the following query in Excel and displays the results on the sheet beginning at cell A3, say:
SELECT field1,field2,field3 FROM MyTable where field1 = X
where X is the value in cell A1 of Sheet1.
I'd like to have a piece of VBA code which I runs the following query in Excel and displays the results on the sheet beginning at cell A3, say:
SELECT field1,field2,field3 FROM MyTable where field1 = X
where X is the value in cell A1 of Sheet1.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That is because you didn't read what I wrote :)
>>>>You can set reference by clicking on VBA menu Tools~~>Reference and then selecting the Microsoft ActiveX Data Objects xx Library.
Sid
>>>>You can set reference by clicking on VBA menu Tools~~>Reference and then selecting the Microsoft ActiveX Data Objects xx Library.
Sid
ASKER
Sorry :). I've done that, but I'm now getting a different error, on line 22:
Run-time error '-21467259 (90004005)':
Unrecognized database format 'C:\Temp\MyDB.accdb'
The directory and file name are correct, so I'm not sure what could be causing the error.
Run-time error '-21467259 (90004005)':
Unrecognized database format 'C:\Temp\MyDB.accdb'
The directory and file name are correct, so I'm not sure what could be causing the error.
Ok Let me test it.
Sid
Sid
Can you empty the database (Just leave 2 records) and upload it here so that I don't have to recreate another one for testing?
Sid
Sid
Oh wait...
Try this
Replace line 21
.Provider = "Microsoft.Jet.OLEDB.4.0"
with
.Provider="Microsoft.ACE.O
Sid
Try this
Replace line 21
.Provider = "Microsoft.Jet.OLEDB.4.0"
with
.Provider="Microsoft.ACE.O
Sid
ASKER
Thanks, it works now!
Gr8 :)
Sid
Sid
ASKER
Compile error:
User-defined type not defined