Importing Data from and External Database

Reference URL: http://archive.baarns.com/access/faq/ad_quer.asp

There are three ways to refer to data sources physically located outside an Access database in a SQL statement:

1. Use linked tables
2. Use the IN clause
3. Use direct references to the external tables

The most efficient and easiest way to reference external tables is to use linked tables. Sometimes though the linking must be done dynamically by the user because data sources are always changing. This situation arises when polling for data for summary or collection. Another situation prohibits altogether the dynamic linking or importing of tables. This arises when users are using an Access 2.0 database in an Access 95 environment. Unless the database is converted, users cannot create new objects, thus eliminating linking or importing tables.

A simple solution to this would be prompting the user for the name and location of the datasource via a common dialog or input box. Then execute dynamic SQL which would append the data to a preexisting table.

Since a dynamic SQL statement is being executed, no objects are created.

An example is listed below:

Sub GetOutsideData()

''' Function: GetOutsideData()
'''
''' Comments:    This routine prompts the user for the location of the database
‘’’  containing the data to import.  A dynamic SQL statement is constructed
‘’’  building the connect string into the SQL statement.  The statement is
‘’’  then executed and the data appended to a preexisting table.  It is
‘’’  assumed the user the table and field names of the outside table is
‘’’  constant.
'''
''' Arguments: none
'''
''' Returns:    none
'''
''' Called by:
'''
''' Keywords:   APPEND QUERY
'''
''' Date        Developer                               Action
''' -------------------------------------------------------------------------
''' 8/09/96    Marty Wasznicky                         created

On Error Resume Next

   Dim varReturn As Variant, db As Database
   Dim szdatasource As String, szSql As String, szMsg as string
   Dim szTitle as string, szAnswer as string

   szMsg = "Enter Path to database"
   szTitle = "Run Data Demo"
   szAnswer = InputBox$(szMsg, szTitle)

   Const gcErrDiskorNetwork = 3043

   If Len(szAnswer) < 1 Then Exit Sub

   Set db = CurrentDb()

   szSql = "INSERT INTO tblTest ( PMC ) SELECT DISTINCTROW LIMS_CUST.PMC FROM _
                 LIMS_CUST IN " & Chr$(34) & szAnswer & Chr$(34) & ";"

   db.Execute Trim$(szSql)

   ''' This traps the network error that occurs when the data file is
   ''' located on a network drive instead of the local hard disk.
   ''' This error only occurs in Access 7.  Also this only occurs using a Novell
   ''' network client and the extension to your data file is ".dat"!!!!!

   If Err = gcErrDiskorNetwork Then
       MsgBox "You place data file on local hard disk"
   End If

End Sub