DestConnectStr, DestinationDB, DestinationTable Properties

Applies To

Query.

Description

You can use these properties with make-table or append queries only.

DestinationDB — You can use this property to specify the type of application used to create an external database.
DestConnectStr — You can use this property to specify the connection string for the name of the database that will contain the new table (for make-table queries) or the name of the database that contains the table to which data will be appended (for append queries).
DestinationTable — You can use this property to specify the name of the new table or the name of the table to be appended to that will hold the results of the query.

Note The DestConnectStr, DestinationDB, and DestinationTable properties apply only to make-table and append queries.

Setting

You set the DestConnectStr, DestinationDB, and DestinationTable properties using a string expression.

The default setting for DestinationDB property is “(current)”, which refers to the currently active database.

You can set these properties in the query property sheet, or in SQL view of the Query window.

In the SQL statement for an append query, the table name in the INSERT INTO statement corresponds to the DestinationTable property setting. The IN clause corresponds to the DestinationDB and DestConnectStr property settings.

In the SQL statement for a make-table query, the table name in the INTO clause corresponds to the DestinationTable property setting. The IN clause corresponds to the DestinationDB and DestConnectStr property settings.

Note Microsoft Access sets these properties automatically based on the information you enter in the query property sheet or in SQL view of the Query window.

Remarks

When you choose Make Table or Append on the Query menu, Microsoft Access prompts you for the information needed to set these properties. Microsoft Access uses the value you enter in the Table Name box to set the DestinationTable property, and it uses the information you type in the File Name box to set the DestConnectStr and DestinationDB properties.

To use the property sheet to specify a table in a Microsoft Access database, enter the name of the table in the Destination Table box (for example, Clients). In the Destination DB box, enter the path and database name without the filename extension (for example, C:\Accounts\Customers). Microsoft Access adds the extension automatically. You don’t need to set the DestConnectStr property.

To specify a table in a database created by a different product, such as Paradox®, enter the name of the table in the Destination Table box. In the Destination DB box, enter the path (for example, C:\Paradoxdata). In the Dest Connect Str box, enter the specifier for the database type (for example, Paradox 3.5). For a list of specifiers, see the Connect property.

To specify an Open Database Connectivity (ODBC) database, enter the name of the database in the Dest Connect Str box along with additional information, such as a logon identification (ID) and password, as required by the product. For example, you might use the following setting for a Microsoft SQL Server database.

ODBC;DSN=salessrv;UID=jace;PWD=password;DATABASE=sales;

For more information about ODBC drivers, such as Microsoft SQL Server, see the online Help provided with the driver.

You don’t need to set the DestinationDB property for ODBC databases.

Example

The following example appends the records in the Customers table to the Clients table in a Microsoft Access database named Clients in the C:\Data directory. You can enter this SQL statement in SQL view of the Query window.

INSERT INTO Clients IN 'C:\Data\Clients.mdb'DISTINCTROW Customers.[CompanyName], Customers.PhoneCustomers;

The next example creates a new table named Contacts in a Paradox version 3.5 database in the C:\Paradoxdata directory.