When you know information about the ODBC data source and can hard-code it into the application, you can preconnect to the data source. To do this, include the ODBC DSN and database information as part of your application’s code and require the user to supply his or her authentication information at run time. Construct a form to gather the user name and password and build a connection string containing these values, as in the following example:
' Create connection string. strConnect = "ODBC;DATABASE=pubs;DSN=Pubs;" _ & "UID=" & strUserName & ";" & "PWD=" & _ strPassword & ";" ' Open ODBC data source. Set dbs = OpenDatabase("", False, False, strConnect)
Note By default, if there is no explicit user name and password specified at connect time, Microsoft Jet will attempt to connect to an ODBC data source with the same user name and password supplied by the user to initialize the Microsoft Jet engine. To change this default and avoid the additional overhead of a failed logon attempt against ODBC data sources that use different user names or passwords, your application should set the Microsoft Jet TryJetAuth setting in the \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\ODBC key of the Windows Registry to 0. This disables the automatic authentication attempt.