Copy table from ODBC into MS Access (or close the ODBC Connection)
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,SY
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:
Function AccessCustomerTables()
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.Connec
cnn.Open "DSN=<>" & RegOff(i)& "_ALL_32;UID=SYS;PWD=FUNCT
strSQL = "SELECT * FROM AR61ACST"
Set rst = cnn.Execute(strSQL)
'Additional code to perform a specific function
rst.Close
cnn.Close
Set cnn = Nothing
Next i
End Function
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)
Or......
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.
Thank you.
I can use the following code to achieve this:
DoCmd.TransferDatabase acImport, _
"ODBC", "ODBC;" & ImpDSN & ";DBQ=" & ImpDBQ & "," & ImpPath & _
",2,G:\3RDPARTY\WORKING,SY
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:
Function AccessCustomerTables()
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.Connec
cnn.Open "DSN=<>" & RegOff(i)& "_ALL_32;UID=SYS;PWD=FUNCT
strSQL = "SELECT * FROM AR61ACST"
Set rst = cnn.Execute(strSQL)
'Additional code to perform a specific function
rst.Close
cnn.Close
Set cnn = Nothing
Next i
End Function
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)
Or......
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.
Thank you.
ASKER
Yes I had tried to close the recordset as well, without success.
However ..... I got something to work although I don't understand the unusual set of circumstances.
I had tried the TransferDatabase method as noted earlier, connecting with a User DSN data source. The connection did not release and my code failed as I had run out of licences.
I had also realized that I could setup a File DSN connection, which would allow me to manually connect to all 5 data sources. I could then link to my external tables.
I opened the first linked table "AR61ACST_BAR" (linked to File DSN "BAR_ALL_32.dsn"). I received the correct information, Access displayed approximately 3000 customers.
I then closed the first linked table and opened the second linked table "AR61ACST_WAT" (linked to File DSN "WAT_ALL_32.dsn"). I received incorrect information, Access displayed the data from the first linked table, approximately 3000 customers. The second linked table should have contained approximately 500 customers.
To double-check my DSN connection I closed Access to release the connection(s) to my data source(s)
I then opened the second linked table first "AR61ACST_WAT" (linked to File DSN "WAT_ALL_32.dsn"). This time I received the correct information, Access displayed approximately 500 customers.
I then closed the second linked table and opened the first linked table "AR61ACST_BAR" (linked to File DSN "BAR_ALL_32.dsn"). I received incorrect information, Access displayed the data from the second linked table, approximately 500 customers. The first linked table should have displayed approximately 3000 customers.
The data returned was the exact reversal of the first instance. It seems that the connection is being cached in some way that I don't understand.
I used the following code, using the same File DSN connections and it works !!!
(I had actually written very similar code quite sometime ago and had forgotten about it, silly me):
Sub ImportCustomers()
Dim RegOff(5) As String, i As Byte
Dim UIDPass As String, DataDict As String, ImpDBQ As String
Dim ImpTable As String, SourceTable As String, j As Byte
RegOff(1) = "BAR"
RegOff(2) = "WAT"
RegOff(3) = "MTL"
RegOff(4) = "MIS"
RegOff(5) = "KNG"
For i = 1 To 5
ImpDBQ = "AR61A*"
ImpDSN = "FILEDSN=" & RegOff(i) & "_ALL_32.DSN"
ImpDBQ = "AR61A*"
ImpPath = "G:\ASP\DATA,AIR"
DataDict = "G:\3RDPARTY\WORKING"
UIDPass = "SYS,FUNCTION"
SourceTable = "AR61ACST"
ImpTable = SourceTable & "_" & RegOff(i)
'*** Delete Existing Tables
On Error Resume Next
DoCmd.DeleteObject acTable, ImpTable
On Error GoTo 0
'*** Import Tables
DoCmd.TransferDatabase acImport, _
"ODBC", "ODBC;" & ImpDSN & ";DBQ=" & ImpDBQ & "," & ImpPath & _
",2," & DataDict & "," & UIDPass, acTable, SourceTable, _
ImpTable, False, True
Next i
End Sub
This imports all the tables correctly.
However ..... I got something to work although I don't understand the unusual set of circumstances.
I had tried the TransferDatabase method as noted earlier, connecting with a User DSN data source. The connection did not release and my code failed as I had run out of licences.
I had also realized that I could setup a File DSN connection, which would allow me to manually connect to all 5 data sources. I could then link to my external tables.
I opened the first linked table "AR61ACST_BAR" (linked to File DSN "BAR_ALL_32.dsn"). I received the correct information, Access displayed approximately 3000 customers.
I then closed the first linked table and opened the second linked table "AR61ACST_WAT" (linked to File DSN "WAT_ALL_32.dsn"). I received incorrect information, Access displayed the data from the first linked table, approximately 3000 customers. The second linked table should have contained approximately 500 customers.
To double-check my DSN connection I closed Access to release the connection(s) to my data source(s)
I then opened the second linked table first "AR61ACST_WAT" (linked to File DSN "WAT_ALL_32.dsn"). This time I received the correct information, Access displayed approximately 500 customers.
I then closed the second linked table and opened the first linked table "AR61ACST_BAR" (linked to File DSN "BAR_ALL_32.dsn"). I received incorrect information, Access displayed the data from the second linked table, approximately 500 customers. The first linked table should have displayed approximately 3000 customers.
The data returned was the exact reversal of the first instance. It seems that the connection is being cached in some way that I don't understand.
I used the following code, using the same File DSN connections and it works !!!
(I had actually written very similar code quite sometime ago and had forgotten about it, silly me):
Sub ImportCustomers()
Dim RegOff(5) As String, i As Byte
Dim UIDPass As String, DataDict As String, ImpDBQ As String
Dim ImpTable As String, SourceTable As String, j As Byte
RegOff(1) = "BAR"
RegOff(2) = "WAT"
RegOff(3) = "MTL"
RegOff(4) = "MIS"
RegOff(5) = "KNG"
For i = 1 To 5
ImpDBQ = "AR61A*"
ImpDSN = "FILEDSN=" & RegOff(i) & "_ALL_32.DSN"
ImpDBQ = "AR61A*"
ImpPath = "G:\ASP\DATA,AIR"
DataDict = "G:\3RDPARTY\WORKING"
UIDPass = "SYS,FUNCTION"
SourceTable = "AR61ACST"
ImpTable = SourceTable & "_" & RegOff(i)
'*** Delete Existing Tables
On Error Resume Next
DoCmd.DeleteObject acTable, ImpTable
On Error GoTo 0
'*** Import Tables
DoCmd.TransferDatabase acImport, _
"ODBC", "ODBC;" & ImpDSN & ";DBQ=" & ImpDBQ & "," & ImpPath & _
",2," & DataDict & "," & UIDPass, acTable, SourceTable, _
ImpTable, False, True
Next i
End Sub
This imports all the tables correctly.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing