Class to support client/server operations using Microsoft Jet to manage the ODBC layer
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
|
Sets the name of the database in use in establishing the 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
|
Gets 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
|
Gets 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
|
Gets 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
|
Sets the user name to use to establish the remote connection. This value must specify a valid server login account name.
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
|
Gets the size of the local cache. The minimum and maximum values for this property are defined by constants in the class declarations.
|
|
LocalDatabase
|
Property
|
Sets a pointer to an open local Jet database. Use this property instead of the OpenLocalDatabase to set the database
to one you already have open. Note that if you open a database outside of the class, and pass that database to
the class using this property, the class will not close the database, even if you call the CloseLocalDB() method.
|
|
LocalDatabasePassword
|
Property
|
Gets 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
|
Returns 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
|
Returns 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
|
Gets 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
|
Sets 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
|
Sets the read-only state for subsequent calls to the OpenRemoteDatabase method
|
|
RemoteRecordset
|
Property
|
Gets 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
|
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.
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.
|
|
BuildConnectString
|
Private
|
Builds a valid Jet Connect string to identify the ODBC data source based on the DSN settings current in 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.
|
|
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
|
|
CloseLocalDatabase
|
Method
|
Closes 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
|
Closes 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
|
Closes 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
|
Creates a persistent DSN on the current system. Use this programmatic method as an alternative to the ODBC Data Source Administrator.
|
|
MakeSQLServerDSN
|
Method
|
Creates 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
|
Opens 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
|
Opens the named remote database using the connection properties currently set in the class.
|
|
OpenRemoteRecordset
|
Method
|
Opens 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
|
Opens a dynaset-type recordset object on the specified remote datasource and retrieves only the specified number of records
|
|
ParseConnectString
|
Private
|
Parses the current connect string into its constituent parts and sets the class properties accordingly
|
|
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
|
All Our Microsoft Access Products
(885K)
