ACC1x: Using TransferDatabase Macro to Attach to SQL Server Data

ID: Q88658

The information in this article applies to:

Microsoft Access versions 1.0, 1.1

SUMMARY

This article describes how to use the TransferDatabase macro action to attach data in backend SQL tables.

MORE INFORMATION

You can use the TransferDatabase macro action to import from, export to, or attach an external data source. This article describes how to attach data in a SQL Server table.

When you create a macro that uses the TransferDatabase macro action, you need to supply the following 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 window.

 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 a string of items or parameters that defines the remote data source and the user's identification. The connect string includes the following items:


 DATA SOURCE NAME (DSN):        The name of the SQL Server computer.

 DATABASE QUALIFIER (DATABASE): 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 that password information is case sensitive in SQL Server.

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


   ODBC;DSN=ServerX;Database=Pubs;WSID=wrkstn1;UID=JohnDoe;PWD=MARCH

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

Keywords : kbusage OdbcOthr
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbhowto