17.2. Controlling the Logon Process

The only way to deftly handle logon errors when your application starts is by controlling the logon process to the back-end database. Sometimes the database server is down or there are network connectivity issues. If you don't control the connection process upon startup, then users may get a very unpleasant and confusing error message. Additionally, if you control the logon process then you can store the supplied username and password information for connecting to the same data store subsequently without having to re-ask the user for security credentials. The process and code needed will vary depending on whether you are using an ADP or MDB file

17.2.1. Using Linked Tables with MDB Files

If you don't have a custom startup form with an MDB file when using linked tables, then you can't control the logon process. If your application uses SQL Security, then your users may get prompted for passwords at random points in the application when Access first tries to use a given linked table. It is easy to create a custom startup login form that looks similar to the one shown in Figure 17-20 and prompts for username and password.

Using this simple form, you can then run code for the Next button to refresh the table links, save the username and password to global variables, or whatever your specific application needs to do. In the case of linked tables, you will likely need code at some point to create a DSN to store connection information for the tables. The following code illustrates how to programmatically create a DSN:

Public Sub pCreateNewDSN()
  Dim strDSNName As String
  Dim strDriverName As String
  Dim strDescription As String
  Dim strServer As String
  Dim strDatabase As String

  'This is the DSN name you want to use when
  'referencing this DSN in your code
  strDSNName = "MyDSN"

 'The name of the ODBC Driver used for the connection
 strDriverName = "SQL Server"

 ' This is for any optional description you might want to
 ' view in the ODBC Driver Manager program
 strDescription = "Test DSN Description"

 ' In the case of SQL Server, the following line if for the
 ' network name of the server you want to connect to
 strServer = "(local)"

 ' Then name of the Default database on the server you want
 ' used for this DSN. If you don't specify, then SQL

Figure 17.20. Figure 17-20

' Statements may end up getting executed in the wrong database
 strDatabase = "NorthwindCS"

 DBEngine.RegisterDatabase strDSNName, strDriverName, _
   True, "Description=" & strDescription & _
   Chr(13) & "Server=" & strServer & _
   Chr(13) & "Database=" & strDatabase
End Sub

Once the DSN has been created you can then reference that DSN in code to create and refresh linked tables. The following code demonstrates how to create linked tables based on the DSN created above:

Public Sub pCreateLinkedTable()
' NOTE: This code required the DAO object library to function
' If you are using the Northwind.mdb sample file then this
' reference should already be present. If you are not sure,
' you should check your references under Tools->references
' in the VBA environment and make the sure the reference to
' Microsoft DAO 3.6 Object library is checked

Dim strDSNName As String
Dim strAppName As String
Dim strDatabase As String
Dim strUID As String
Dim strPW As String
Dim strRemoteTableName As String
Dim strLocalTableName As String
Dim strConnection As String
Dim daoTableDef As DAO.TableDef

' This must reference an existing DSN
strDSNName = "MyDSN"

' The application name can be used for tracing and
' troubleshooting the source of problems on the server.
' This can be anything you want but usually the more
' specific the better.
strAppName = "Microsoft Access 2003"

' The database where the table resides on SQL Server
strDatabase = "NorthwindCS"

' User name for logging into the database server.
' This could captured by a logon form and stored in a global
' variable.
strUID = "SA"

' Password for logging in to the database server
' This could captured by a logon form and stored in a global
' variable.
strPW = "password"

' Then name of the table on the remote server
strRemoteTableName = "Customers"

' The name of the table we want create in the local file
' that links to strRemotetableName
strLocalTableName = "NewTable"

' This will build the ODBC connection string for our new table
strConnection = "ODBC:" & _
       "DSN=" & strDSN & ";" & _
       "APP=" & strAppName & ";" & _
       "DATABASE=" & strDatabase & ";" & _
       "UID=" & strUID & ";" & _
       "PWD=" & strPW & ";" & _
       "TABLE=" & strRemoteTableName

' This creates a new table object and adds it to the local
' database. If your tables already exist, then you would
' skip this code and use code to refresh the links, instead
Set daoTableDef = CurrentDb.CreateTableDef(strLocalTableName _
, dbAttachSavePWD, strRemoteTableName, strConnection)
CurrentDb.TableDefs.Append daoTableDef

'Clean up
 Set daoTableDef = Nothing
End Sub

Alternatively, if your tables already exist in the database and you just need to refresh the links, then the following code would be substituted at the end of the above procedure:

' This code assumes that the tables have already been created.
' If not, then you would need to run code to create the
' tables instead
Set daoTableDef = CurrentDb.TableDefs(daoTableDef)
daoTableDef.Connect = strConnection
daoTableDef.RefreshLink

17.2.2. Using Access Projects

Since Access projects don't store tables in the ADP file, you just need to reconnect the ADP to the SQL Server database used by the application. You can use a similar startup form as suggested for an MDB to collect a username and password or you can just run code automatically if you only allow integrated security. Whether or not you use a login form, code to reconnect the ADP would be similar to the following:

Public Sub pConnectADP()
  Dim strServerName As String
  Dim strDatabase As String
  Dim strUN As String
  Dim strPW As String
  Dim boolUseIntegratedSecurity As Boolean
  Dim strConnect As String

  ' Required. This is the network name of the SQL Server.
  ' "(local)" can be used to reference a default SQL Server
  ' installation on the local machine
  strServerName = "(local)"

  ' Required. This is the database you want the ADP to be based on.
  strDBName = "NorthwindCS"

  ' Optional. The SQL Server username
  ' Not needed if using integrated security
  strUN = "SA"

  ' Optional. The password for the username above.
  ' Not needed if using integrated security
  strPW = "Password"

  ' Use this flag to signify whether the connection string should
  ' contain a username and password or use integrated security
  boolUseIntegratedSecurity = True

  ' This is the full connection string for the ADP.
  ' The Provider, Data Source, and Initial Catalog arguments are

' required.
strConnect = "Provider=SQLOLEDB.1" & _
  ";Data Source=" & strServerName & _
  ";Initial Catalog=" & strDBName

'Add the necessary argument if using integrated security
If boolUseIntegratedSecurity Then
  strConnect = strConnect & ";integrated security=SSPI"
' Add the username and password arguments if using SQL Server Security
Else
  strConnect = strConnect & ";user id=" & strUN & _
    ";password=" & strPW
End If
' Open the connection.
' If there is already an existing connection open then this will
' change it.
Application.CurrentProject.OpenConnection strConnect
End Sub

Unfortunately, one of the limitations of the CurrentProject.OpenConnection method is that you can't specify the advanced connection properties programmatically, such as Application Name or Connect Timeout. In addition, since Access does not expose these properties programmatically, there is no convenient method for changing them after the connection is made, either. The properties that can't be specified are the ones displayed on the Advanced and All tabs of the Connection Properties dialog box for the ADP. To view these properties and change them manually, select Connection from the File menu, as shown in Figure 17-21.

If the normal server happens to be down and your code generates an error, it would be useful to have a convenient way to specify an alternate server, which may not be known when the application was developed. One way of accomplishing this is to use a UDL file to store the connection information for your SQL Server. However, the CurrentProject.OpenConnection method does not accept UDL files as a parameter directly, so code will be needed to retrieve the connection information by using a regular ADO connection and then passing the information to the ADP.

Dim cnnTest As ADODB.Connection
Set cnnTest = New ADODB.Connection

' Change the following path to your actual path to Test.udl
' This could also be stored as a property of the ADP so it
' could be configured by an administrator instead of being
' hardcoded.
cnnTest.Open "File Name=\ServerShareTest.udl;"

' Now pass the connection string of the ADO connection to the ADP.
Application.CurrentProject.OpenConnection cnnTest.ConnectionString

' Test the connection
If CurrentProject.IsConnected = False Then
   'It didn't work. Handle accordingly
End If

Figure 17.21. Figure 17-21

' Clean up
cnnTest.Close
Set cnnTest = Nothing

One more step needs to be taken for this to work seamlessly. If you just close the ADP in a normal fashion, the connection information will be stored and Access will attempt to reconnect the next time the ADP is opened. Since Access does this before your own code has a chance to run, you won't be able to trap any errors that occur. To prevent this from occurring (as best you can), run the following line of code from the Close event of the last form to be closed:

CurrentProject.OpenConnection ""

This not only closes the connection but clears the connection information from the file so that the ADP will open in a disconnected state the next time around. This will allow your code to run and reconnect the ADP as you wish. A good place to run the code is from the Close event of the initial logon form. If you hide the logon form instead of closing it, this will guarantee that the code will run no matter how the ADP was closed.

The only exception to this is when a power or abnormal close of the application occurs, perhaps because the application froze. In such cases, the previous connection information will still be present in the ADP because that was not a chance to remove it. However, this exception normally just causes there to be two logon prompts the next time the ADP is open: the default Access prompt and then the one presented by the application's code. If the application is using integrated security then the users will likely not even notice any difference.