Microsoft Jet honors the security system in all external data sources that support security. When the external data source is secured by a password, your application must supply a password to establish a connection. You can do this by prompting the user for this information and building it into the connection string, or you can hard-code the password into your application.
Note If you store sensitive data in the external data source, you shouldn’t hard-code passwords in your Microsoft Jet application.
Use the PWD argument to pass the password to the external data source, as in the following example. You must follow the password argument with a semicolon.
Set tdf = dbs.CreateTableDef("FirstQuarterSales1996", 0, "Q1Sales", _
"FoxPro 3.0;DATABASE=C:\JetBook\Samples\FoxTables\Sales;PWD=RollsRoyce;")
Note You can use the password argument to specify database passwords only; you cannot use the password to log on to a network. If your application requires network access, you must establish these connections before attempting to access the external data.
You cannot use the PWD argument to decrypt Microsoft Excel worksheets or workbooks; you must unprotect and save the worksheet or workbook in Microsoft Excel before you can open it with the Microsoft Excel IISAM driver.
If you don’t want to hard-code sensitive ODBC database passwords into your application or require your users to enter a password each time they start the application, you can set the Attributes property of the TableDef object to dbAttachSavePWD. This saves the ODBC password provided by the user as part of the table definition. Each time the user subsequently opens the remote table through the TableDef object, the password information is automatically applied. The following line of code sets the Attributes property to save a password with a TableDef object:
tdf.Attributes = dbAttachSavePWD
If your remote data is particularly sensitive, you may not want to allow users to save their passwords. To change this behavior, you must modify the MSysConf table in your remote database to disallow local storage of login IDs and passwords (by default, local storage is allowed). If your application has already been deployed with password saving enabled, you can still disallow local user ID and password saving with MSysConf; ODBC will automatically prompt users for any missing authentication information when they attempt to open the remote database.
See Also For more information about the MSysConf table, see Chapter 9, “Developing Client/Server Applications.”