How to use ODBC to get data from Access through Excel VBA

[qjd2004]

Hiya,

I'm getting access data through excel vba. Everything works fine. I'm also using ADOX for DLL & Security to do stuff in access without opening the access databases in question.

Now my end user is saying they won't have access installed. Can I still get data from the access databases if I used ODBC? If so

(a) what references do I need on the project, are there any DLLs that will need putting on the client's machine?
(b) how do I change my code (below) to work with ODBC?
(c) how do I make the new DB an ODBC source?

Can anyone please help me out of a sticky situation?

Private Sub FetchRecords(filename As String, specCond As String, ContractID As String)
'Get data from access db and' transfer to the live worksheet

Dim con As ADODB.Connection
Dim sSQL As String
Dim rs As ADODB.Recordset
Dim col As Long, rw As Long
Dim fn
Dim strAccesDB
Dim rectype
Set con = New ADODB.Connection

'Specify which SQL is used to get records from accessdb
    If specCond = "" Then
        sSQL = "SELECT * FROM Sender WHERE ClientID = " & ContractID
    Else
        sSQL = "SELECT * FROM Sender WHERE ClientID = " & ContractID _
             & " AND Client_Ref = " & specCond
    End If
    
'put the filename arg into the connection string
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
                        & filename

'Open the new connection
con.Open
Set rs = con.Execute(sSQL)

'etc etc

 

[qjd2004]

I have a feeling I've answered the 1st part of this question myself!

I just copied the Access type library from a machine where it was installed, registered it and the connection opens and the data comes through!

I'm not sure this is the correct way of doing it however, and I do get a message from Windows saying that, although the DLL was registered successfully, the DLLRegisterServer entry point was not found.

But the VBA module works and I think I can get the ADOX to work (so I don't have to open access to insert a link table).