Using TransferDatabase Macro to Attach to SQL Server Data

Summary:

This article describes how to use the TransferDatabase macro action to
attach to data in SQL Server tables and what information is necessary
to execute this process.

This article assumes the reader is fluent with Microsoft Access Macro
code and its operation.

More Information:

The macro action TransferDatabase can be used to import from, export
to, or attach to external data sources. This article specifically
describes how to attach to data in a SQL Server table.

When you create a macro with the TransferDatabase action, you will
need to supply the macro-specific arguments:

Transfer Type:  For this type of transfer, choose ATTACH.
Database Type:  This is the type of the external database source that
                is being attached. In this case, select <SQL DATABASE>.
Database Name:  With SQL Server, a connect string supplies the
                necessary information.
Object Type:    The type of object being attached in this case is a
                TABLE.
Source:         The name of the table in the SQL Server table goes
                here.
Destination:    This is the name of the table as it is seen in the
                Microsoft Access database.
Structure Only: Depends on whether the data should be attached, also.
                In most cases (as well as this case) this should be
                NO.

The connect string (Database Name) is basically a string of items or
parameters that define the remote data source and the user's
identification.

The following are items within the connect string:

  DATA SOURCE NAME (DSN):   The name of the SQL Server machine.
  DATABASE QUALIFIER (DBQ): The name of the SQL Server database.
  WORKSTATION ID (WSID):    The name of the workstation that is making
                            the connection (optional).
  USER ID (UID):            The login id*.
  PASSWORD (PWD):           The user password*.

* This item is optional within the connect string. If it is not
present, the system will prompt the user with a dialog box to enter
this information at the time the macro is executed.

Note: SQL Server is case sensitive when dealing with password
information.

The following is a sample connect string to SQL Server. Note that it
starts with "ODBC", which tells the system that it will use the ODBC
driver (as opposed to an installable ISAM driver).

  ODBC;DSN=ServerX;DBQ=Pubs;WSID=wrkstn1;USID=JohnDoe;PWD=MARCH

This string would be placed in the Database Name argument of the
TransferDatabase action.