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 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:
|
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 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!
Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!
"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