Is it possible to use Access data w/o importing it?

[ricky252525]

Hi folks-- Just wondering if there is any way that I can use data from an Access table in my Excel functions without importing the Access data into the wookbook.

I set up a connection to the Access table that I need, but don't know what the syntax would be to reference those fields in functions in my Excel sheet (or if it's even possible).

My problem is that the Access table is large and I want to keep the size of the Excel workbook small.

Thanks!

 

[ranman256]

You can access the data via ADO code...

Public Sub GetRstData()
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 = "ShanDonovan"
 pwd = ""
 DB = "C:\Generic104J.mdb"
 vProvid = "Microsoft.Jet.OLEDB.4.0"     '  "SQLOLEDB"
 
 'con.Open "Provider=SQLOLEDB; Server=db.someserver.uk; Database=DB; User ID=" & uid & "; Password=" & pwd & ";"
 'con.Provider = "Microsoft.Jet.OLEDB.4.0"
 '"Provider=" & vProvid; Server=db.someserver.uk; Database=DB; User ID=" & uid & "; Password=" & pwd & ";"
  
With con
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("User ID").Value = uid
    .Properties("Password").Value = pwd
    
    .Open "Data Source=" & DB & ";Jet OLEDB:System Database=\\gad.intra.net\amer\UMA\Secured All UMA.mdw"
End With
 
Set rs = con.Execute("qsNames_simps")
'now access the records
'copy data from recordset
'ActiveWorkbook.Worksheets("Sheet1").Range("A1").CopyFromRecordset rs
rs.Close
con.Close
End Sub