Other Utilities via ADO

The rest of the chapter provides some useful utilities. Thinking about the application you provided for your clients, they now have an Access database located on their network, but possibly no copy of Access. It would be ideal if you could deliver changes to the Access database on the fly as their application opens up. In the case study in Chapter 25, "Add-Ins," I discuss burying an Update macro that gets called with Workbook_Open. The update macro might be responsible for making database enhancements.

Checking for Existence of Tables

If the application needs a new table in the database, you can use the code in the next section. However, because we have a multi-user application, only the first person who opens the application has to add the table on the fly. When the next buyer shows up, the table may have already been added by the first buyer's application.

This code uses the OpenSchema method to actually query the database schema:

Function TableExists(WhichTable) Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim fld As ADODB.Field TableExists = False

1 Path to Transfers.mdb is on Menu MyConn = "J:\transfers.mdb"

Set cnn = New ADODB.Connection

With cnn

.Provider = "Microsoft.Jet.OLEDB.4.0" .Open MyConn End With

Set rst = cnn.OpenSchema(adSchemaTables)

Do Until rst.EOF

If LCase(rst!Table_Name) = LCase(WhichTable) Then TableExists = True GoTo ExitMe End If rst.MoveNext

Loop

ExitMe:

rst.Close Set rst = Nothing ' Close the connection cnn.Close

End Function

0 0

Post a comment