10.8 Track Which Users Have a Shared Database Open

14.1 Dynamically Link SQL Server Tables at Runtime

14.1.1 Problem

Your Access SQL Server database uses linked tables and views in SQL Server. You have set up security and permissions in SQL Server and want to make sure that each user's linked tables are attached under their own permissions, not another user's permissions. In addition, you don't want the users to be prompted for an additional login ID and password each time they use a table.

14.1.2 Solution

If you link SQL Server tables from an Access database using the File figs/u2192.gif Get External Data menu commands, you will be prompted to use or create a Data Source Name (DSN). The main drawback to DSNs is that they need to be installed on every user's machine. A better solution is to use VBA code to link or relink tables. You can supply connection information in the Connection string without having to create a DSN.

This technique uses DAO to create new TableDef objects in each database when the application starts up. The startup form for the application has a dialog where the user can supply a login and password to be used to connect to SQL Server. The list of table names is stored in a local Access ( Jet) database.

To add this technique to your application, follow these steps:

  1. Create a table to hold the names and properties of the SQL Server tables to which your application will link. In the 14-01.MDB sample database, the local table is named tblSQLTables. The column definitions are listed in Table 14-1.

     

    Table 14-1. Column definitions for tblSQLTables

    Column name

    Data type

    Primary key?

    Required?

    SQLTable

    Text 50

    Yes

    Yes

    SQLDatabase

    Text 50

    No

    Yes

    SQLServer

    Text 50

    No

    Yes

     

  2. Enter data in the table. Figure 14-1 shows the datasheet view of the table used to store data about the tables that are linked from the Northwind database on the local SQL Server.

    Figure 14-1. tblSQLTables has entries to link to the tables in the Northwind database

    figs/acb_1401.gif

  3. Create the startup form. The example shown in Figure 14-2 uses an option group to determine whether integrated security (Windows 2000 or Windows NT authentication) or a SQL Server login and password is being used. If a SQL Server login is selected, users can enter their logins and passwords in the text boxes.

    Figure 14-2. The startup form allows users to supply login information for the linked tables

    figs/acb_1402.gif

  4. Once you've created the form and the necessary controls, you'll need to write the code to set up the links. In design view, select the OnClick event of the Connect command button and choose Event Procedure. This will open the VBA code window.

  5. You'll need to set a reference to the DAO 3.6 Object Library by choosing Tools figs/u2192.gif References... and checking the Microsoft DAO 3.6 Object Library, as shown in Figure 14-3.

    Figure 14-3. Setting a reference to the DAO object library

    figs/acb_1403.gif

  6. Here's the complete code listing for the cmdConnect_Click event procedure:

    Private Sub cmdConnect_Click(  )     Dim db As DAO.Database     Dim tdf As DAO.TableDef     Dim rst As DAO.Recordset     Dim strServer As String     Dim strDB As String     Dim strTable As String     Dim strConnect As String     Dim strMsg As String      On Error GoTo HandleErr     ' Build base authentication strings.     Select Case Me!optAuthentication         ' Windows/NT login         Case 1            strConnect = "ODBC;Driver={SQL Server};Trusted_Connection=Yes;"         ' SQL Server login         Case 2            strConnect = "ODBC;Driver={SQL Server};UID=" _             & Me.txtUser & ";PWD=" & Me.txtPwd & ";"     End Select          ' Get rid of any old links.     Call DeleteLinks          ' Create a recordset to obtain server object names.     Set db = CurrentDb     Set rst = db.OpenRecordset("tblSQLTables", dbOpenSnapshot)     If rst.EOF Then         strMsg = "There are no tables listed in tblSQLTables."         GoTo ExitHere     End If          ' Walk through the recordset and create the links.     Do Until rst.EOF         strServer = rst!SQLServer         strDB = rst!SQLDatabase         strTable = rst!SQLTable         ' Create a new TableDef object.         Set tdf = db.CreateTableDef(strTable)         ' Set the Connect property to establish the link.         tdf.Connect = strConnect & _             "Server=" & strServer & _             ";Database=" & strDB & ";"         tdf.SourceTableName = strTable         ' Append to the database's TableDefs collection.         db.TableDefs.Append tdf         rst.MoveNext     Loop          strMsg = "Tables linked successfully."          rst.Close     Set rst = Nothing     Set tdf = Nothing     Set db = Nothing      ExitHere:     MsgBox strMsg, , "Link SQL Tables"     Exit Sub HandleErr:     Select Case Err         Case Else             strMsg = Err & ": " & Err.Description             Resume ExitHere     End Select End Sub

The completed application is shown in 14-01.MDB, which contains the local table used to store data about the tables that are linked from the Northwind SQL Server database. A startup form contains the relinking code.

14.1.3 Discussion

The first step in linking SQL Server tables is to build the ODBC Connection string that will be used to link the tables. You could use a DSN, but you'd have to create the DSN if it didn't exist. We find it easier to simply build a dynamic string with all the required information. The first part of the string contains connection information that will be the same for every table:

Select Case Me!optAuthentication     ' Windows/NT login     Case 1        strConnect = "ODBC;Driver={SQL Server};Trusted_Connection=Yes;"     ' SQL Server login     Case 2        strConnect = "ODBC;Driver={SQL Server};UID=" _         & Me.txtUser & ";PWD=" & Me.txtPwd & ";" End Select

The next step is to delete any old linked SQL Server tables by calling the DeleteLinks procedure:

Call DeleteLinks

The DeleteLinks procedure walks through the current database's TableDefs collection, deleting only linked ODBC tables. Here's the complete listing:

Private Sub DeleteLinks(  )     ' Delete any leftover linked tables from a previous session.          Dim tdf As DAO.TableDef          On Error GoTo HandleErr     For Each tdf In CurrentDb.TableDefs         With tdf         ' Delete only SQL Server tables.             If (.Attributes And dbAttachedODBC)  = dbAttachedODBC Then                 CurrentDb.Execute "DROP TABLE [" & tdf.Name & "]"             End If         End With     Next tdf      ExitHere:     Set tdf = Nothing     Exit Sub HandleErr:     MsgBox Err & ": " & Err.Description, , "Error in DeleteLinks(  )"     Resume ExitHere     Resume End Sub

The next step is to create a recordset that lists the table names, the SQL Server database name, and the SQL Server itself. If no tables are listed, the procedure terminates. This portion of code is as follows:

Set db = CurrentDb Set rst = db.OpenRecordset("tblSQLTables", dbOpenSnapshot) If rst.EOF Then     strMsg = "There are no tables listed in tblSQLTables."     GoTo ExitHere End If

Next, walk through the recordset, creating a new TableDef object for each table listed. The Connect property is set to the base connection string with the server and database name concatenated. The TableDef object's SourceTableName is set to the table name in the database, and the TableDef object is appended to the TableDefs collection. This portion of code resides in the following Do Until loop:

Do Until rst.EOF     strServer = rst!SQLServer     strDB = rst!SQLDatabase     strTable = rst!SQLTable     ' Create a new TableDef object.     Set tdf = db.CreateTableDef(strTable)     ' Set the Connect property to establish the link.     tdf.Connect = strConnect & _         "Server=" & strServer & _         ";Database=" & strDB & ";"     tdf.SourceTableName = strTable     ' Append to the database's TableDefs collection.     db.TableDefs.Append tdf     rst.MoveNext Loop

Once the TableDefs are appended, the cleanup code runs and the user is notified that the tables have been successfully linked:

    strMsg = "Tables linked successfully."          rst.Close     Set rst = Nothing     Set tdf = Nothing     Set db = Nothing      ExitHere:     MsgBox strMsg, , "Link SQL Tables"     Exit Sub

The technique discussed here for relinking tables works well in any version of SQL Server and is not specific to either Access 97, 2000, or 2002. Any time you use DAO in your code, you need to open the Tools figs/u2192.gif References... dialog in the Visual Basic editor and make sure that a reference is set for the Microsoft DAO library: the version of DAO used in Access 2000 or 2002 is 3.6.

 

Although you can link SQL Server tables using ADOX, the SQL Server tables are then read-only in Access.

 



Access Cookbook
Access Data Analysis Cookbook (Cookbooks)
ISBN: 0596101228
EAN: 2147483647
Year: 2005
Pages: 174

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net