Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Import ODBC Data with DSN-less

Status
Not open for further replies.

r15gsy

IS-IT--Management
Jan 9, 2008
22
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:

Code:
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.

Code:
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:

Code:
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!
 
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.
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top