Copy table from ODBC into MS Access (or close the ODBC Connection)

Posted on 2003-02-26
Medium Priority
Last Modified: 2008-01-16
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"
Set rst = cnn.Execute(strSQL)
'Additional code to perform a specific function

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

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

Thank you.
Question by:btaplin
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8038531
Just a thought... have you tried setting the rst object to nothing as well?

Set rst = Nothing

Set cnn = Nothing

Author Comment

ID: 8045299
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.

Accepted Solution

SpideyMod earned 0 total points
ID: 8045466
PAQ'd points refunded.

Community Support Moderator @Experts Exchange

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question