Query.
You can use the Source property to specify the source connection string and source database for a query’s input table or query. This is necessary if you are accessing data in an external table without linking the table.
Note Using the Source property and the SourceConnectStr property to access ODBC tables is much slower than using linked tables.
You set the Source property using a string expression. 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 filename extension automatically. |
C:\Accts\Customers |
|
dBASE® |
The database type and path. For a list of specifiers, see the Connect property. |
dBASE IV;DatabaseTable=C:\DBDATA |
|
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;=password;DATABASE=sales; |
You can set this property in 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.
When all the input 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 input table or query.
DestConnectStr, DestinationDB, DestinationTable Properties; SourceConnectStr, SourceDatabase Properties.
You can’t set this property in code directly. It’s set when you create a new query using an SQL statement. The FROM clause in the SQL statement corresponds to the Source property setting.
The following example sets the ControlSource property for a text box to a field in a dBASE IV® table.
Dim strGetSQL AS String= "SELECT DISTINCTROW Customers.[Company Name], _
FROM [dBASE IV; DATABASE = c:\dbdata].Customers;"!Customers.txtCompanyName.ControlSource = strGetSQL
In the next example, the source of data for the query is a dBASE IV table named Customers in the C:\Dbdata directory. You can enter this SQL statement in SQL view of a Query window.
SELECT DISTINCTROW Customers.[Company Name], Customers.Phone[dBASE IV; DATABASE = c:\dbdata].Customers;