VBA in Excel to import Access Database

[jmzis4me]

Hello Lifesavers,


I'm hoping this is simple, as I believe it is common....

I have an Access Query, we'll just call it QueryA.

All I want to do is have VBA code on an ActiveX button so when it presses, it runs QueryA from Access, then pastes the results on an excel sheet. We'll say I want it to paste it onto Sheet1 in Book1.

I have the QueryA SQL code (from what I read that might be needed), and the path to the file on hand. But outside of that, I have no clue where to start with the VBA code.

PLEASE HELP!

Thank you!!

[ranman256]

You have to have the ADO objects checked via the VBE menu, tools, references.

Please Login or Register  to view this content.

[jmzis4me]

Thank you, I put that in and checked off the box to allow it. For these:

.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("User ID").Value = uid
.Properties("Password").Value = pwd

.Open "Data Source=" & DB

Am I supposed to overwrite with my information in any of the quotations here? I was unsure and when I tried to run it it stopped me at the .Open giving me a runtime error "The workgroup information file is missing". I am sure its a simple obvious thing, but I haven't seen this code before so I'm intuitive things, are probably not for me.
Thank you again for your help.

[jmzis4me]

Also, just to verify, you don't Dim DB or vProvid as anything?

[jmzis4me]

Nevermind. Got it. I didn't need a userid or password so that was messing it up. Perfect. Thanks!