VBA in Excel to import Access Database

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!!

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

Public Sub CopyRST()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim DB
Dim vProvid

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
 
 uid = "HomerJSimpson"
 pwd = ""
 DB = "\\myserver\production\Generic104J.mdb"
 vProvid = "Microsoft.Jet.OLEDB.4.0"     '  "SQLOLEDB"
 
  
With con
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("User ID").Value = uid
    .Properties("Password").Value = pwd
    
    .Open "Data Source=" & DB 
End With
 
Set rs = con.Execute("QueryA")
ActiveWorkbook.Worksheets("Sheet1").Range("A1").CopyFromRecordset rs

rs.Close
con.Close

set rs = nothing
set con = nothing
End Sub

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.

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

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