Copy table from ODBC into MS Access (or close the ODBC Connection)
Posted on 2003-02-26
I need to copy a set of tables from our DOS-based accounting software through an ODBC data source into Access 2000.
I can use the following code to achieve this:
DoCmd.TransferDatabase acImport, _
"ODBC", "ODBC;" & ImpDSN & ";DBQ=" & ImpDBQ & "," & ImpPath & _
",2,G:\3RDPARTY\WORKING,SYS,FUNCTION", acTable, SourceTable, _
ImpTable, False, True
My problem is that I have my data segmented into 5 different databases, one for each of our 5 regional offices, in different folders, with different file extensions, etc.
I run out of licences to access the data after trying to access my third data source. We have two licenses but 'should' only need one in actuality, as I am the only user who is accessing the data.
Access leaves the connections to the data sources open, even though the tables that I need are already imported from the first two data sources.
Currently, I have to write code into 5 different Access databases and transfer the imported tables I need into a sixth database. Access drops the connections to the data sources when Access itself closes. Having to do this is a tremendous inconvenience, but I can access all 5 data sources this way.
What I have realized though, is that I can create an object that is a connection to the data source, access the tables through that connection, and then set the object equal to nothing which effectively closes the connection. I can then run through all 5 datasets only using 'one license'. The following code does just that:
Dim cnn As Object, strSQL As String, rst As Object
Dim RegOff(5) As String, i As Byte
RegOff(1) = "BAR"
RegOff(2) = "WAT"
RegOff(3) = "MTL"
RegOff(4) = "MIS"
RegOff(5) = "KNG"
For i = 1 To UBound(RegOff)
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "DSN=<>" & RegOff(i)& "_ALL_32;UID=SYS;PWD=FUNCTION"
strSQL = "SELECT * FROM AR61ACST"
Set rst = cnn.Execute(strSQL)
'Additional code to perform a specific function
Set cnn = Nothing
What I need is a SQL Statement or other simple code that will copy the ODBC tables into Access using the "ADODB.Connection Object":
Something like this perhaps (this doesn't work but it should explain the concept):
strSQL = "INSERT INTO NEWTABLE SELECT * FROM AR61ACST"
(I do not wish to use code to loop through the entire recordset and transfer each record one by one if at all possible)
Is there a way to drop the connection to the data source a different way, so that I can use the "DoCmd.TransferDatabase acImport" code that I have listed above, to create a loop that will access my data without using up all my licenses?
Any help would be greatly appreciated.