Import ODBC Data with DSN-less

[r15gsy]

I am trying to import data from multiple ODBC sources. They all have a similar structure but are all independent accounting databases for different companies.

I can get the import working by setting up a DSN entry, but would have to setup/amend the DSN entries when companies change. But the following code does work:

Private Sub cmdImportODBC_Click()
Dim strODBC As String

strODBC = "ODBC;Provider=MSDASQL.1;DSN=ACCACA;UID=myname;PWD=mypass"

DoCmd.TransferDatabase acImport, "ODBC Database", strODBC, acTable, "AUDITJNL", "ACA_AUDITJNL"
End Sub

But I want to change the DSN=ACCACA to a DSN-less entry which I can program from a table with the stored connection parameters.

I have worked out the correct DSN parameters and tried the following code, but get an error 3000 reserved error.

strODBC = "ODBC;Provider=MSDASQL.1;DRIVER={Accounts v15};UID=myname;PWD=mypass;DIR=c:\programdata\accounts\aca\"

To double check the connection string settings, I have connected using ADODB which connects fine and returns the fields. But I don't know how to transfer a whole table by ADODB. But here is the code which works fine:

Private Sub Command0_Click()

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim sConnString  As String

sConnString = "Provider=MSDASQL.1;DRIVER={Accounts v15};UID=myname;PWD=mypass;DIR=c:\programdata\accounts\aca\"

conn.Open sConnString
Set cmd.ActiveConnection = conn

cmd.CommandText = "SELECT * FROM AUDITJNL"
cmd.CommandType = adCmdText
Set rs = cmd.Execute

Do While Not rs.EOF
 
Debug.Print rs.Fields("DATE")
rs.MoveNext
Loop

Set rs = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing

End Sub

If anyone can offer any suggestions on what to try then anything is welcome, thanks!

 

[jsteph]

r15gsy,
What I always do is use a .reg file like below. For remote intallations I put in in a .cmd or .bat file for the user to run along with whatever copy/move .mdb scripting commands I might have.

The below would be what you'd put in a .reg file, which can change based on the needs you have (ie, different server names, users, etc.) This assumes MS Sql server.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ACCACA]
"Driver"="C:\\WINDOWS\\system32\\SQLSRV32.dll"
"Description"="Some Accounting System"
"Server"="Accounting_Server"
"LastUser"="myname"
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
"ACCACA"="SQL Server"

If I'm running this on my machine I may want to keep the DSN name the same in the above file and just and change users or servername, since the I may have tables linked using a standard DSN Name.

But due to Access caching connections Access would have to close first because without direct api calls I haven't been able to get Access to see a change in the Registry to a same-named DSN like this without closing the app.
--Jim