Importing data from multiple ODBC databases

[Guest]

Hi,

I'm developing an Access database that has to import data from multiple
SQLBase databases into a new table. This is the function i wrote to do this:

Function updateODBC(Optional ByVal Database As Variant)
Dim cn As ADODB.Connection
Dim sSQL As String, sODBCString As String

If IsMissing(Database) Then Administratie = DEFAULT_DB

sODBCString = "ODBC;DSN=Unit 4 Multivers;DB=" & Database &
";SRVR=MVSERV;UID=ODBC;PWD=ODBC;"
Set cn = Application.CurrentProject.Connection
Debug.Print sODBCString

sSQL = "SELECT * INTO " & _
"[tblU4MGrootboek] " & _
"FROM " & _
"[" & sODBCString & "].[PUBLIC.GROOTBOEK];"

cn.Execute sSQL
cn.Close
Set cn = Nothing
End Function

Before i execute this function i delete the table 'tblU4MGrootboek'. The
first time i execute this function it works fine. Next time when i execute it
with a different database it imports the table from the first database!? It
looks like it doesn't refresh the ODBC connection and the connection to the
first database is still open and used.
Does anyone have a suggestion how i can resolve this behavior?

Thanx,
Dennis

 

[John Nurick]

Hi Dennis,

It looks as if you have multiple SQLBase database each containing a
table PUBLIC.GROOTBOEK and you want to import the contents of all these
tables into your Access table tblU4MGrootboek.

But you're building a SELECT ... INTO statement, which is a make-table
query. You should first create tblU4MGrootboek (manually or by importing
data from one database) and then execute a series of append queries,
something like this:

"INSERT INTO tblU4MGrootboek " _
& "SELECT * FROM [" _
& sODBCString & "].[PUBLIC.GROOTBOEK];"

Hi,

I'm developing an Access database that has to import data from multiple
SQLBase databases into a new table. This is the function i wrote to do this:

Function updateODBC(Optional ByVal Database As Variant)
Dim cn As ADODB.Connection
Dim sSQL As String, sODBCString As String

If IsMissing(Database) Then Administratie = DEFAULT_DB

sODBCString = "ODBC;DSN=Unit 4 Multivers;DB=" & Database &
";SRVR=MVSERV;UID=ODBC;PWD=ODBC;"
Set cn = Application.CurrentProject.Connection
Debug.Print sODBCString

sSQL = "SELECT * INTO " & _
"[tblU4MGrootboek] " & _
"FROM " & _
"[" & sODBCString & "].[PUBLIC.GROOTBOEK];"

cn.Execute sSQL
cn.Close
Set cn = Nothing
End Function

Before i execute this function i delete the table 'tblU4MGrootboek'. The
first time i execute this function it works fine. Next time when i execute it
with a different database it imports the table from the first database!? It
looks like it doesn't refresh the ODBC connection and the connection to the
first database is still open and used.
Does anyone have a suggestion how i can resolve this behavior?

Thanx,
Dennis