Total Visual SourceBook

Total Visual SourceBook CD and Printed Manual

Microsoft Access/ Office 2016, 2013, 2010, and 2007 Version
is Shipping!

New features in Total Visual SourceBook for Access, Office 2007 and VB6

Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!

Separate version for Access/Office 2003, 2002, and 2000.

Separate version for:
Access 97/95


View all FMS products for Microsoft AccessAll Our Microsoft Access Products

SourceBook Info:

Why SourceBook?

 

"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."

Van T. Dinh, Microsoft MVP

 

 

 

Microsoft Access, Office/VBA, VB6 Modules royalty-free source code libraryMicrosoft Access Modules royalty-free source code libraryMicrosoft Access VBA and VB6 Module Library Free trial of Total Visual SourceBook for Microsoft Access, Office, VB6 and VBA

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

Class to support client/server operations using Microsoft Jet to manage the ODBC layer in VBA and VB6.

This class supports client/server operations using Microsoft Jet to manage the ODBC layer. When using Visual Basic to deploy client/server applications, you have a variety of development choices. You can use DAO and Jet, DAO and ODBC Direct, RDO and the Remote Data Control, or program directly against the API. This class exposes functionality to use DAO and Jet against ODBC data sources. For information on the other choices, see the other classes in the Database category. Many client server applications developed using Microsoft Access and Visual Basic use the DAO/Jet method for remote data access. Using Microsoft Jet, you make calls to the remote server using standard DAO procedures. Microsoft Jet manages the translation between the MDB file format objects and the server objects.

Procedure List

Procedure Name

Type

Description

(Declarations) Declarations Declarations and private variables for the CJetODBC class
ConnectDatabaseName Property Get the name of the database in use by the current remote connection. This property represents the name of the remote database. When the class is initialized, this property has no value. Before calling the OpenRemoteDatabase method, set this property to the name of the remote database you want to connect to. If you don't specify a value for this property, the DatabaseName specified by the current DSN (specified by the ConnectDSN property) will be used. If the current DSN does not have a valid DatabaseName value, calls to the OpenRemoteDatabase will cause the ODBC driver to display a dialog, prompting for the information. For more information on how the Connect properties work, see the documentation for this class.
ConnectDSN Property Get the name of the DSN used in the current remote connection This property represents the DSN name to use when connecting to a remote database. When the class is initialized, this property has no value. Before calling the OpenRemoteDatabase method, set this property to the name of the DSN you want to use. For more information on how the Connect properties work, see the documentation for this class.
ConnectPassword Property Get the password in use by the current remote connection. This property represents the password used to connect to the remote database. When the class is initialized, this property has no value. Before calling the OpenRemoteDatabase method, set this property to the password for the user account you wish to use. If you don't specify a value for this property, the password specified by the current DSN (specified by the ConnectDNS property) will be used. If the current DSN does not have a valid password value, calls to the OpenRemoteDatabase will cause the ODBC driver to display a dialog, prompting for the information.
ConnectString Property Get the Connect string used by Jet to establish the current remote connection. This value is the value of the DAO OpenDatabase Connect parameter. Its value is built from the ConnectPassword, ConnectUserID, ConnectDatabaseName and ConnectDSN properties.
ConnectUserID Property Get the user name in use by the current remote connection. When the class is initialized, this property has no value. Before calling the OpenRemoteDatabase method, set this property to the user account you wish to use. If you don't specify a value for this property, the user id specified by the current DSN (specified by the ConnectDNS property) will be used. If the current DSN does not have a valid user id value, calls to the OpenRemoteDatabase will cause the ODBC driver to display a dialog, prompting for the information.
LocalCacheSize Property Get the size of the local cache. The minimum and maximum values for this property are defined by constants in the class declarations.
LocalDatabase Property Get a handle to the local Jet database Use the LocalDatabase property to get a handle to the local database open in the class, or to assign a database you have opened with the class. When the class is first initialized, this property is undefined. When you call the OpenLocalDatabase method, this property contains a handle to the database object opened by the class. You can use this property just as you would use any other DAO Database object: you can access the properties and methods of a Database object that aren't directly exposed as methods and properties of the class. For example, the class doesn't expose the Version of the database as a property, but you can use the class LocalDatabase property to reference the Version property of the Database object: Dim MyODBC As New CJetODBC MyODBC.OpenLocalDatabase "C:\NWIND.MDB", False, False ' Now show the version of the local database Debug.Print "Version: " & MyODBC.LocalDatabase.Version You can also use this property to take a database you already have open, and pass it to the class. In this case, you don't call the OpenLocalDatabase method. Instead, you open the database in your code, and then set the class Database property to your database: Dim MyODBC As New CJetODBC Dim dbsMine As DAO.Database Set dbsMine = DAO.DBEngine.OpenDatabase ("C:\NWIND.MDB") Set MyODBC.Database = dbsMine
LocalDatabasePassword Property Get the password used to open the local database. This property only contains a value if the local database was opened with the OpenLocalDatabase method.
LocalExclusive Property Get the Exclusive state of the local database if it was opened with OpenLocalDatabase. If the local database was not opened with OpenLocalDatabase, the value of this property has no meaning.
LocalReadOnly Property Get the read-only state of the local database if it was opened with OpenLocalDatabase. If the local database was not opened with OpenLocalDatabase, the value of this property has no meaning.
RemoteDatabase Property Get a handle to the remote Jet database. Use this property to get a handle to the remote database open in the class, or to assign a database you have opened with the class. When the class is first initialized, this property is undefined. When you call the OpenRemoteDatabase method, this property contains a handle to the remote database object opened by the class. You can use this property just as you would use any other DAO Database object: you can access the properties and methods of a Database object that aren't directly exposed as methods and properties of the class. For example, the class doesn't expose the Version of the database as a property, but you can use the class RemoteDatabase property to reference the Version property of the Database object: Dim MyODBC As New CJetODBC Dim fRet As Boolean ' Set the properties to define the database MyODBC.ConnectDatabaseName = "Pubs" MyODBC.ConnectDSN = "PUBS" MyODBC.ConnectUserID = "sa" MyODBC.ConnectPassword = "" ' Open the remote database fRet = MyODBC.OpenRemoteDatabase() ' Now show the version of the remote database Debug.Print "Version: " & MyODBC.RemoteDatabase.Version You can also use this property to take a database you already have open, and pass it to the class. In this case, you don't call the OpenRemoteDatabase method. Instead, you open the database in your code, and then set the class Database property to your database: Dim MyODBC As New CJetODBC Dim dbsRemote As DAO.Database Set dbsRemote = DAO.DBEngine.OpenDatabase("", False, False, "ODBC;DATABASE=pubs;UIS=sa;PWD=;DSN=Pubs") Set MyODBC.RemoteDatabase = dbsRemote
RemoteNoPrompt Property Get the NoPrompt state of the class. This value is used by the OpenRemoteDatabase method to tell the ODBC driver to prompt or suppress prompts when establishing the connection.
RemoteReadOnly Property The read-only state of the remote database, if it was opened using the OpenRemoteDatabase method. If the OpenRemoteDatabase method has not been called, this property's value has no meaning.
RemoteRecordset Property Get a handle the current remote recordset. Use this property to gain direct access to the remote recordset open in the class. This is typically needed when you use the class to open the recordset for you, and then need to do something with the recordset that isn't directly supported by the class. For example, to get the count of records in a remote recordset, use the following technique Dim MyODBC As New CJetODBC Dim fRet As Boolean ' Set the properties to define the database MyODBC.ConnectDatabaseName = "Pubs" MyODBC.ConnectDSN = "PUBS" MyODBC.ConnectUserID = "sa" MyODBC.ConnectPassword = "" ' Open the remote database fRet = MyODBC.OpenRemoteDatabase() ' Open the remote recordset MyODBC.OpenRemoteRecordset "SELECT * FROM authors", False, False ' Move to the last record and display the count of records. MyODBC.RemoteRecordset.MoveLast Debug.Print "There are " & MyODBC.RemoteRecordset.RecordCount & " records in this set."
TuneMaxBufferSize Property Set the Jet Engine's MaxBufferSize tuning value, which controls the size of the database engine internal cache, measured in kilobytes (K). MaxBufferSize must be an integer value greater than or equal to 512. This property sets the Jet Engine's MaxBufferSize tuning value which controls the size of the database engine internal cache, measured in kilobytes (K). MaxBufferSize must be an integer value greater than or equal to 512. When Jet starts, it determines the default value for MaxBufferSize based on the following formula: MaxBufferSize = ((Total MB RAM - 12 MB) / 4) + 512 KB So, if your system has 32MB of RAM, the formula would be ((32MB - 12 MB) / 4) + 512 KB which would result in a MaxBufferSize cache of 5632 KB. Jet will not set a default larger than 13,824K. This keeps the engine from claiming too much memory on systems with a large amount of RAM. In general, setting MaxBufferSize to a value above 8 MB will not result in increased performance. Additionally, an buffer that is too large can cause performance degradations because of the increased processor time needed to manage the cache. Finally, remember that the Windows operating system itself has both cache memory and virtual memory. If you specify a large cache for Jet and the operating system begins to run low on real memory, it will start swapping the Jet cache memory to the virtual memory paging file. This scenario results in the eliminates the performance enhancements available through the cache.
Class_Initialize Initialize Set initial values to defaults which may be overridden with property settings
Class_Terminate Terminate Clean up resources used by the class
CacheRemoteRecordset Method Loads the local cache with records from the server. Use this method to tell the Jet Engine to retrieve the specified number of records from the server to its local cache. In many cases, this can result in dramatic performance increases in your application, especially when your application needs to move forward and backward through a recordset. The number of records to load into the cache is specified by the LocalCacheSize property. The default value for this property, as set in the class Initialize event, is 100 records. If you want to use a value other than the default, set the LocalCacheSize property before calling this method. The varStart parameter tells Jet which record to start caching at. You must specify a valid DAO Recordset bookmark value. Leave this a parameter blank to start caching at the current record. As your code moves through the recordset, the contents of the local record cache remain valid until you move out of the boundaries of the cache. For example, if you cache 20 records starting at the beginning of the recordset, the cache becomes inactive once you move to the 21st record.
CloseLocalDatabase Method Close the local Jet database. If you have not called the OpenLocalDatabase method, or you have set the class LocalDatabase property to another database, this method doesn't close anything.
CloseRemoteDatabase Method Close the remote database object opened with the OpenRemoteDatabase method. If you have not called the OpenRemoteDatabase method, or you have set the class RemoteDatabase property to another database, this method doesn't close anything.
CloseRemoteRecordset Method Close the class recordset. Use this method to close the remote recordset opened with any of the following class methods: - OpenRemoteRecordset - OpenRemoteRecordsetNRecords - RunPassThroughToRecordset If you have not called one of these methods, this method doesn't close anything.
MakeGenericDSN Method Create a persistent DSN on the current system. Use this programmatic method as an alternative to the ODBC Data Source Administrator.
MakeSQLServerDSN Method Create a persistent Microsoft SQL Server DSN on the current system. Use this programmatic method as an alternative to the ODBC Data Source Administrator. This function is similar to the MakeGenericDSN method, except that is specifically for creating DSNs for Microsoft SQL Server.
OpenLocalDatabase Method Open the named Access/Jet database as the local database. This is necessary if the server calls you are making require a local database handle. For example, to open a local recordset based on server data, you need a local database open.
OpenRemoteDatabase Method Open the named remote database using the connection properties currently set in the class.
OpenRemoteRecordset Method Open a recordset object on the specified remote datasource. What is actually happening is that Jet is using a temporary database/table setup to store the local recordset. Nothing is actually created on the server.
OpenRemoteRecordsetNRecords Method Open a dynaset-type recordset object on the specified remote datasource and retrieves only the specified number of records
RunPassThroughQueryNoResults Method Runs the specified query by passing it directly to the server. Jet makes no attempt to translate the SQL, or interpret the results. The SQL passed must be syntactically legal for the server. This method assumes that no rows are returned. If any rows are returned, this method ignores them.
RunPassThroughToRecordset Method Runs the specified query by passing it directly to the server and assigns the returned rows the to the class recordset.Jet makes no attempt to translate the SQL, or interpret the results. The SQL passed must be syntactically legal for the server.
RunStoredProcedure Method Runs the named stored procedure on the remote server
BuildConnectString Private Builds a valid Jet Connect string to identify the ODBC data source based on the DSN settings current in the class.
ParseConnectString Private Parses the current connect string into its constituent parts and sets the class properties accordingly

Example Code for Using Class: JetODBC

' 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

Microsoft Access Module LibraryOverview of Total Visual SourceBook

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.

Additional Resources