Source Property

Applies To   Action query, Crosstab query, Parameter query, Select query, SQL-Specific query.

Description

You can use the Source property to specify the source connection string and source database for a query's source table or query. This is necessary if you're accessing data in an external table without linking the table.

Tip Using the Source property and the SourceConnectStr property to access ODBC tables is much slower than using linked tables.

Setting

You set the Source property by using a string expression. You can't set this property in code directly. It's set when you create a new query by using an SQL statement. The FROM clause in the SQL statement corresponds to the Source property setting.

The following examples show how you can use the Source property to connect to several different types of external databases.

Type of database

Specify this

Sample

Setting

Microsoft Access

The path and database name. Microsoft Access adds the file name extension automatically.

C:\Accts\Customers

dBASE

The database type and path. For a list of specifiers, see the DAO Connect property.

dBASE IV;DatabaseTable=C:\DBDATA


(continued)

SQL Server (ODBC)

The name of the source database and any additional information required by the product, such as a logon identification (ID) and password. You can also use the ODBC Connection String Builder to create the connection string.

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


You can set this property by using a query's field list property sheet.

You can also set it in SQL view of the Query window by using the FROM and IN clauses in the SQL statement.

Remarks

When all the source tables in a query come from the same external database, use the query's SourceConnectStr and SourceDatabase properties instead of setting the Source property for each source table or query.

See Also   Connect property ("DAO Language Reference"), DestConnectStr, DestinationDB, DestinationTable properties, SourceConnectStr, SourceDatabase properties.

Example

The following example sets the RowSource property for the lstContact list box control to a field in a dBASE IV table.

Dim strGetSQL AS String
strGetSQL = "SELECT Customer.COMPANYNAM, Customer.PHONE FROM Customer " _
    & "IN 'c:\dbdata'[dBASE IV;];"
Me.lstContact.RowSource = strGetSQL
In the next example, the source of data for the query is a dBASE IV table named Customer in the C:\Dbdata folder. You can enter this SQL statement in SQL view of a Query window.

SELECT Customer.COMPANYNAM, Customer.PHONE
FROM Customer IN 'c:\dbdata'[dBASE IV;];