[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

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

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

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.
0
btaplin
Asked:
btaplin
1 Solution
 
SamEdneyCommented:
Just a thought... have you tried setting the rst object to nothing as well?

rst.Close
Set rst = Nothing

cnn.Close      
Set cnn = Nothing
   
0
 
btaplinAuthor Commented:
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.
0
 
SpideyModCommented:
PAQ'd points refunded.

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now