Database Property Example

  Database Property Example

This example uses the Database property to show how code that used to access ODBC data through the Microsoft Jet database engine can be converted to use ODBCDirect Connection objects.

The OldDatabaseCode procedure uses a Microsoft Jet-connected data source to access an ODBC database.

Sub OldDatabaseCode()

   Dim wrkMain As Workspace
   Dim dbsPubs As Database
   Dim prpLoop As Property

   ' Create Microsoft Jet Workspace object.
   Set wrkMain = CreateWorkspace("", "admin", "", dbUseJet)

   ' Open a Database object based on information in
   ' the connect string.

   'Note: The DSN referenced below must be configured to 
   '       use Microsoft Windows NT Authentication Mode to 
   '       authorize user access to the Microsoft SQL Server.
    Set dbsPubs = wrkMain.OpenDatabase("Publishers", _
        dbDriverNoPrompt, False, _
        "ODBC;DATABASE=pubs;DSN=Publishers")

   ' Enumerate the Properties collection of the Database 
   ' object.
   With dbsPubs
      Debug.Print "Database properties for " & _
         .Name & ":"

      On Error Resume Next
      For Each prpLoop In .Properties
         If prpLoop.Name = "Connection" Then
            ' Property actually returns a Connection object.
            Debug.Print "  Connection[.Name] = " & _
               .Connection.Name
         Else
            Debug.Print "  " & prpLoop.Name & " = " & _
               prpLoop
         End If
      Next prpLoop
      On Error GoTo 0

   End With

   dbsPubs.Close
   wrkMain.Close

End Sub

The NewDatabaseCode example opens a Connection object in an ODBCDirect workspace. It then assigns the Database property of the Connection object to an object variable with the same name as the data source in the old procedure. None of the subsequent code has to be changed as long as it doesn't use any features specific to Microsoft Jet workspaces.

Sub NewDatabaseCode()

   Dim wrkMain As Workspace
   Dim conPubs As Connection
   Dim dbsPubs As Database
   Dim prpLoop As Property

   ' Create ODBCDirect Workspace object instead of Microsoft 
   ' Jet Workspace object.
   Set wrkMain = CreateWorkspace("", "admin", "", dbUseODBC)

   ' Open Connection object based on information in
   ' the connect string.
   ' Note: The DSN referenced below must be configured to 
   '       use Microsoft Windows NT Authentication Mode to 
   '       authorize user access to the Microsoft SQL Server.
    Set conPubs = wrkMain.OpenConnection("Publishers", _
        dbDriverNoPrompt, False, _
        "ODBC;DATABASE=pubs;DSN=Publishers")
   ' Assign the Database property to the same object 
   ' variable as in the old code.
   Set dbsPubs = conPubs.Database

   ' Enumerate the Properties collection of the Database 
   ' object. From this point on, the code is the same as the 
   ' old example.
   With dbsPubs
      Debug.Print "Database properties for " & _
         .Name & ":"

      On Error Resume Next
      For Each prpLoop In .Properties
         If prpLoop.Name = "Connection" Then
            ' Property actually returns a Connection object.
            Debug.Print "  Connection[.Name] = " & _
               .Connection.Name
         Else
            Debug.Print "  " & prpLoop.Name & " = " & _
               prpLoop
         End If
      Next prpLoop
      On Error GoTo 0

   End With

   dbsPubs.Close
   wrkMain.Close

End Sub

关于OpenDatabase第二个参数说明，在WorkSpace为dbUseJet时，上面例子里的那个dbDriverNoPrompt应该不起任何作用的，使用True或者False才有意义。只要在dbUseODBC时，那几个选项才能发挥作用，试验所得，呵呵。同样OpenConnection时也可以直接使用OpenDatabase，和dbUseJet时使用完全一样，不过OpenConnection函数只要在使用ODBCDirect workspace才可以使用，在Jet workspace时不能使用

For Microsoft Jet workspaces, you can use the following values for the options argument.

Setting	Description
True	Opens the database in exclusive mode.
False	(Default) Opens the database in shared mode.

For ODBCDirect workspaces, the options argument determines if and when to prompt the user to establish the connection. You can use one of the following constants.

Constant	Description
dbDriverNoPrompt	The ODBC Driver Manager uses the connection string provided in dbname and connect. If you don't provide sufficient information, a run-time error occurs.
dbDriverPrompt	The ODBC Driver Manager displays the ODBC Data Sources dialog box, which displays any relevant information supplied in dbname or connect. The connection string is made up of the DSN that the user selects via the dialog boxes, or, if the user doesn't specify a DSN, the default DSN is used.
dbDriverComplete	(Default) If the connect and dbname arguments include all the necessary information to complete a connection, the ODBC Driver Manager uses the string in connect. Otherwise it behaves as it does when you specify dbDriverPrompt.
dbDriverCompleteRequired	This option behaves like dbDriverComplete except the ODBC driver disables the prompts for any information not required to complete the connection.

Remarks