Using Excel To Read Access Without MS Access On Computer

Question

I have written code that joins two table in access, using criteria supplied from drop down lists in excel and then returns the data to a specific location on the spreadsheet (titles already on the sheet). This works fine on my box and others with MS Access on the machine, but the purpose of writing this was to give people (associates) that dont have the MS Access on their machines (which is most of them) to be able to do simple queries to the database. When we try to run this on a machine without MS Access, we are getting the error message "Compile Error: Cant find project or library." Since this works fine on any machine so far that has Access, but not the others I am wondering if this is not possible without the actual Access software. Any help or insight would be appreciated.

Tom

Answer 1

you need to use ADO and the "jet" provider. This will allow you to query an access database without having access installed

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"

set objRecordset=Server.CreateObject("ADODB.recordset")
objRecordset.activeconnection = conn
objRecordset.source = "select * from table where field1 = 'asdf'"
objRecordset.open

'do you work here

objRecordset.close
conn.close