Class: JetODBC in Category SQL Server : SQL Server from Total Visual SourceBook

' Example of CJetODBC
'
' To use this example, create a new module and paste this code into it.
' Then run the procedure by putting the cursor in the procedure and pressing:
'    F5 to run it, or
'    F8 to step through it line-by-line (see the Debug menu for more options)

Private Sub Example_CJetODBC()
  ' Comments: Example of the CJetODBC Class in VBA and VB6.
  '           This example uses a System DSN called 'pubs', which is created when you run the example code.
  '           The DSN should point to your SQL Server 'pubs' database.
  '           See results in the Immediate Window.

  ' This example assumes that the sample files are located in the folder named by the following constant.
  Const cstrSamplePath As String = "C:\Total Visual SourceBook 2013\Samples\"
  Const cstrLocalDatabase As String = cstrSamplePath & "SAMPLE.MDB"

  Dim clsJetODBC As CJetODBC
  Dim dbsLocal As DAO.Database
  Dim dbsRemote As DAO.Database
  Dim fOK As Boolean

  ' Instantiate the class
  Set clsJetODBC = New CJetODBC

  ' Use MakeGenericDSN to create the Pubs DSN
  fOK = clsJetODBC.MakeGenericDSN("", "Pubs", "SQL Server", False)
  If fOK Then
    Debug.Print "Pubs DSN created using MakeGenericDSN."
  Else
    Debug.Print "Pubs DSN could not be created using MakeGenericDSN."
  End If

  ' Now use MakeSQLServerDSN to re-create the Pubs DSN
  ' Note that the server name may be passed as the 4th parameter, or you may provide it on the ODBC dialog.
  fOK = clsJetODBC.MakeSQLServerDSN("Pubs", "Pubs Database", False, "", "Pubs", False)
  If fOK Then
    Debug.Print "Pubs DSN created using MakeSQLServerDSN."
  Else
    Debug.Print "Pubs DSN could not be created using MakeSQLServerDSN."
  End If

  ' First, we'll set the properties that define our ODBC connection.
  clsJetODBC.ConnectDatabaseName = "Pubs"
  clsJetODBC.ConnectDSN = "PUBS"
  clsJetODBC.ConnectUserID = "sa"
  clsJetODBC.ConnectPassword = ""

  ' Show what the connect string will look like.
  Debug.Print "The connect string generated by the class is: " & clsJetODBC.ConnectString

  ' Set the Readonly flag to False
  clsJetODBC.RemoteReadOnly = False

  ' Set the NoPrompt flag to True
  clsJetODBC.RemoteNoPrompt = True

  ' Try to open the remote database as specified by the options in the 'Pubs' DSN.
  fOK = clsJetODBC.OpenRemoteDatabase()
  If fOK Then
    Debug.Print "Remote database defined by Pubs now open."
  Else
    Debug.Print "Remote database defined by Pubs could not be opened."
  End If

  ' Run a Stored Procedure
  fOK = clsJetODBC.RunStoredProcedure("sp_Tables", "authors")
  If fOK Then
    Debug.Print "Stored Procedure run."
  Else
    Debug.Print "Stored Procedure could not be run."
  End If

  ' Now open the local database
  clsJetODBC.OpenLocalDatabase cstrLocalDatabase, False, False

  ' Use the database object to get more information
  Debug.Print "Full path of the local database is: " & clsJetODBC.LocalDatabase.name

  ' This example shows how to use the properties that expose the parameters passed to the OpenLocalDatabase method.
  Debug.Print "The local database was opened with the following parameters: " & vbCrLf & _
              "Exclusive: " & clsJetODBC.LocalExclusive & vbCrLf & _
              "Password: " & clsJetODBC.LocalDatabasePassword & vbCrLf & _
              "ReadOnly: " & clsJetODBC.LocalReadOnly

  ' Run a query on the Authors table and display the rows
  If clsJetODBC.OpenRemoteRecordset("SELECT * FROM authors", False, False, False) Then
    Debug.Print "OpenRemoteRecordset succeeded."
    With clsJetODBC.RemoteRecordset
      .MoveFirst
      Do Until .EOF
        ' Show the author's last name and city
        Debug.Print !au_lname & ":" & !City
        .MoveNext
      Loop
    End With
  Else
    Debug.Print "OpenRemoteRecordset failed."
  End If

  ' Open the same query, but limit the results to 10 records
  Debug.Print "Test of OpenRemoteRecordsetNRecords. Only 10 records should be retrieved."
  clsJetODBC.OpenRemoteRecordsetNRecords "SELECT * FROM authors", 10
  With clsJetODBC.RemoteRecordset
    .MoveFirst
    Do Until .EOF
      ' Show the author's last name and city
      Debug.Print !au_lname & ":" & !City
      .MoveNext
    Loop
  End With

  ' Close the recordset
  clsJetODBC.CloseRemoteRecordset

  ' Run a pass-through query that returns records
  Debug.Print "Test of RunPassThroughToRecordset"
  clsJetODBC.RunPassThroughToRecordset "SELECT * FROM authors"
  With clsJetODBC.RemoteRecordset
    .MoveFirst
    Do Until .EOF
      ' Show the author's last name and city
      Debug.Print ![au_lname] & ":" & ![City]
      .MoveNext
    Loop
  End With

  ' Close the recordset
  clsJetODBC.CloseRemoteRecordset
  Debug.Print "Remote recordset now closed."

  ' Close our local database
  clsJetODBC.CloseLocalDatabase
  Debug.Print "Local database now closed."

  ' Close the remote database
  clsJetODBC.CloseRemoteDatabase
  Debug.Print "Remote database defined by Pubs now closed."

  ' Now, we will open our own local database, and pass it to the class
  Set dbsLocal = DAO.DBEngine.OpenDatabase(cstrLocalDatabase)
  Set clsJetODBC.LocalDatabase = dbsLocal

  ' Next, we will open our own remote database and pass it to the class
  Set dbsRemote = DAO.DBEngine.OpenDatabase("", False, False, "ODBC;DATABASE=pubs;UIS=sa;PWD=;DSN=Pubs")

  Set clsJetODBC.RemoteDatabase = dbsRemote

  ' Show what the class has derived from the connect string we passed.
  Debug.Print "Connect String      : " & clsJetODBC.ConnectString
  Debug.Print "Connect DatabaseName: " & clsJetODBC.ConnectDatabaseName
  Debug.Print "Connect DSN         : " & clsJetODBC.ConnectDSN
  Debug.Print "Connect Password    : " & clsJetODBC.ConnectPassword
  Debug.Print "Connect UserID      : " & clsJetODBC.ConnectUserID

  ' To verify that everything is working, lets retrieve some records
  ' Run a query on the Authors table and display the rows
  Debug.Print "Test of OpenRemoteRecordset"
  clsJetODBC.OpenRemoteRecordset "SELECT * FROM authors", False, False, False

  ' Now, let's define the recordset cache as 30 records
  clsJetODBC.LocalCacheSize = 30
  ' Load the cache
  clsJetODBC.CacheRemoteRecordset

  With clsJetODBC.RemoteRecordset
    .MoveFirst
    Do Until .EOF
      ' Show the author's last name and city
      Debug.Print ![au_lname] & ":" & ![City]
      .MoveNext
    Loop
  End With

  ' Close the recordset
  clsJetODBC.CloseRemoteRecordset

  ' Open the same query, but limit the results to 10 records
  Debug.Print "Test of OpenRemoteRecordsetNRecords. Only 10 records should be retrieved."
  clsJetODBC.OpenRemoteRecordsetNRecords "SELECT * FROM authors", 10
  With clsJetODBC.RemoteRecordset
    .MoveFirst
    Do Until .EOF
      ' Show the author's last name and city
      Debug.Print ![au_lname] & ":" & ![City]
      .MoveNext
    Loop
  End With

  ' Close the recordset
  clsJetODBC.CloseRemoteRecordset

  ' Close our local database (remember that we opened this, so a call to the class CloseLocalDatabase will accomplish nothing.
  dbsLocal.Close
  Set dbsLocal = Nothing

  ' Close our remote database. Since we opened it, we (not the class) need to close it.
  dbsRemote.Close
  Set dbsRemote = Nothing

  ' Close the class
  Set clsJetODBC = Nothing

End Sub

The source code in Total Visual Sourcebook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.

Total Visual SourceBook is written for the needs of a developer using a source code library covering the many challenges you face. Countless developers over the years have told us they learned some or much of their development skills and tricks from our code. You can too!

Additional Resources

• List of All Modules in Total Visual SourceBook
• List of All Code Categories in Total Visual SourceBook
• Sample Module Code from Total Visual SourceBook (Example of Date, Time and Age Calculations)