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:
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.
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:
If anyone can offer any suggestions on what to try then anything is welcome, thanks!
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!