Access & Excel

[chaddresnick]

Can an Excel spreadsheet reference an Access Table for it's data? Sort of like a vlookup, but instead of referencing another spreadsheet, I'd like to pull data in from a database.

[ranman256]

yes, but its better to do it the other way, read Excel from access...but
in excel code you must establish an ADO connection to get to the recordsets:

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("qsNames_simps")
ActiveWorkbook.Worksheets("Sheet1").Range("A1").CopyFromRecordset rs

rs.Close
con.Close

set rs = nothing
set con = nothing
End Sub