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: JetDatabase in Category Access/Jet Databases : Database from Total Visual SourceBook

Using DAO with Microsoft Access Jet database objects for VBA and VB6.

Microsoft Jet contains a versatile Database object that represents an open database. The term "database" is a broad term that can represent any one of the following: - A Jet or Access Database (.mdb, .mda, .accdb, .accda). - A Jet workgroup information file (.mda, .mdw). - An ODBC database, represented by a DSN. - A file system directory, such as a directory of xBASE or Paradox files. This class works with DAO/Jet objects at the database level. The class itself maps on top of a Jet/Access database and provides methods and properties for working with the objects contained in that database.

Procedure List

Procedure Name

Type

Description

(Declarations) Declarations Declarations and private variables for the CJetDatabase class
DatabaseFormat Property Get the Access database format for the current database combining the disjointed values of the AccessVersion and Version properties. This returns "Access 97", "Access 2000", "Access 2002/2003", "Access 2007", "Access 2010" or "Access 2013" "Access 2002/2003" also includes MDBs created in Access 2007 and later.
AccessVersion Property Get the AccessVersion property of the current database. This returns 07.53 for Access 97, 08.50 is for 2000, 09.50 is for Access 2002/2003/2007/2010/2013 databases (including ACCDBs). Note that the value returns is a string. If you want to convert it to a number, use the Val() function on the result. If you want to differentiate between Access MDB vs. ACCDB use the DatabaseFormat property instead. The property may not exist if the database was created programmatically (e.g. VB6) and never opened in Access.
Database Property Get the DAO Database object of the currently opened database which you can use to get and set database properties. You shouldn't close the database object through the Database property using the DAO close method because class will no longer have the necessary state to perform operations. Instead, perform the CloseDB method if you had the class open the database, or close your own database variable that was passed to the class.
DatabasePassword Property Get The database password used to open the database. This property only has a value if the OpenDB method was called and a password was specified. The property cannot 'extract' the database password used to open a database outside of the class
IsAccessDatabase Property Determine whether the current database was created by, or has been opened by, Microsoft Access. When Microsoft Access creates a database, it adds many Access-specific objects and properties. These include DAO container objects for the Access-specific objects such as forms and reports, and system tables to hold Access-specific object binary data. When you create a database using VBA/VB6, or the DAO CreateDatabase method, these objects are not created, and the database is considered to be a pure-Jet database. The first time you open such a database in Access, Access adds its objects and properties, and from then on, those objects and properties live in that database. This property makes it easy to differentiate pure-Jet databases from Access databases. It works by disabling error handling and trying to access the forms container. In a pure-Jet database, this container doesn't exist, so we can check for an error and determine the status of the database.
LastErrorDescription Property Get the description of the last error that occurred. This value is useful while debugging to determine why an operation failed. This value is the same as the first member in the DAO.DBEngine.Errors collection.
LastErrorNumber Property Get the number of the last error that occurred. This value is useful while debugging to determine why an operation failed. This value is the same as the Number property of first member in the DAO.DBEngine.Errors collection.
name Property Get the name of the current database, without the path
OpenExclusive Property Determine whether or not the database was opened by the class in exclusive mode. The value of this property only has meaning if the OpenDB method was called. This property cannot 'extract' the Exclusive setting of a database opened outside of the class.
OpenReadOnly Property Determine whether or not the database was opened by the class in ReadOnly mode. The value of this property only has meaning if the OpenDB method was called. This property cannot 'extract' the ReadOnly setting of a database opened outside of the class.
Path Property Get the path of the current database, without the file name
Version Property Get the Jet version of the database. If you want the Access version of the database, use the DatabaseFormat property. Note the version property returns a string. If you want to convert this to a number, use the Val() function on the result.
OpenDB Method Open the named Access/Jet database, and keeps the database open until the class is de-instantiated. If you already have a database variable open and want to use this class, you can set the class Database property to an open DAO Database object variable. See the Database property of this class for more information.
CloseDB Method Close the current open database. Note that this only works if the class opened the database through the OpenDB() method. If the class has a database open that was passed to it through the Database property, or the OpenDB() method has not yet been called, this method silently fails.
Class_Terminate Terminate Release resources used by the class.
GetFileName Private Get the file name with extension without the leading drive and folder names. This is a private class helper function.
GetFileNameNoExt Private Get the name of a fully qualified file name with no extension. This is a private class helper function.
GetPathFromFullPath Private Get the path (drive and folders) without the file name from a fully qualified file name. This is a private class helper function.
Compact Method Close the currently open database, compacts it, then reopens it. Compacting a database accomplishes several things: - Moves data pages for each table into adjacent locations to speed table access. - Discards unused space left over from object and record deletions, and resizes the database accordingly. - Resets the increment value for AutoNumber (counter) fields so the next number used is one more than the current highest number for each field. - Rebuilds the statistics for tables that are used to generate query optimization plans. All queries are flagged as uncompiled so the next time they run they will use the updated statistics. This method makes it easy to compact the database currently open by the class. It does this by saving the options used to open the database, such as the read-only flag and the database password. It then closes the database, compacts it into a new file, and then renames the new file to the original file name. Finally, it re-opens the database with the same parameters specified when the class OpenDB method was originally called. Note that this method closes the database and re-opens it. Therefore, you should only use this method when your call to the class used the OpenDB method. If you call the class and don't use the OpenDB method, electing instead to set the class Database property to a database you already have open, this method will likely fail, because it will not be able to close your database.
Decrypt Method Close the currently open database, decrypts it, then reopens it. Decrypting a database reverses the act of encrypting a database. For more information on database encryption, see the EncryptDatabase method of this class, or search DAO online help for "EncryptDatabase". This method makes it easy to decrypt the database currently open by the class. It does this by saving the options used to open the database, such as the read-only flag and the database password. It then closes the database, decrypts it into a new file, and then renames the new file to the original file name. Finally, it re-opens the database with the same parameters specified when the class OpenDB method was originally called. Note that this method closes the database and re-opens it. Therefore, you should only use this method when your call to the class used the OpenDB method. If you call the class and don't use the OpenDB method, electing instead to set the class Database property to a database you already have open, this method will likely fail, because it will not be able to close your database. This operation requires exclusive access to the database. If other users have the database open, or other processes or applications on your computer have the database open, the operation will fail.
Encrypt Method Close the currently open database, compacts it, encrypts it, then reopens it. Encryption is one of the security features available to Microsoft Jet databases. Encrypting a database makes the contents of the database file unreadable from external sources. Un-encrypted Jet databases contain viewable data that can be seen at the operating system level with tools such as hex editors. While the data is difficult to read (because of embedded high-order bits), it is nonetheless decipherable. On the other hand, encrypted databases are completely unreadable because every byte in the database file has been encrypted using the RSA RC4 algorithm with a 32-bit key for every 2K page. To encrypt a database, you must be logged in as a member of the Admins group of the workgroup information file that was in use when the database was created. Encrypted databases have a 10-15% performance degradation over unencrypted databases. Also, since encryption works by removing repeated patterns in data, an encrypted database is essentially uncompressible using popular compression utilities such as Zip, ARJ, and ARC. Similarly, encrypted databases take more space on compressed archives such as tape drives and compressed disk drives. Note that encrypting a database does not add any additional security as far as applications such as Access and Visual Basic are concerned--an encrypted database can be opened just like any other database without Jet user-level security. The only difference is that the database file cannot be read using external tools such as hex editors. For full data security, you must implement user-level Security.
PasswordChange Method Close the current openly database, compact it, change the password, then reopen it.
PasswordRemove Method Close the current openly database, compact it, remove the password, then reopen it
CloseCompactOpen Private Close, compact or repair, then open the specified database. Note that Access cannot compact the current database from VBA code. Set the database's Compact on Close option to do that.
EmptyAllTables Method Delete all records from all tables in the current open database. **CAUTION: this deletes all data from the database!
GetAccessFormCount Method Get the number of Microsoft Access forms in the database.
GetAccessMacroCount Method Get the number of Microsoft Access macros in the database.
GetAccessModuleCount Method Get the number of Microsoft Access modules in the database.
GetAccessReportCount Method Get the number of Microsoft Access reports in the database.
GetDatabaseIndexCount Method Get the count of indexes in the database on all tables. This method does not include indexes on system tables, but does include any system-created indexes on non-system tables. For example, if you create a table with only one index, and then enter that table in a relationship with another table where the relationship link is on an unindexed field, Jet creates an index on your behalf to support the relationship. For this reason, the count returned by this method may be more than the number of indexes you explicitly created on the database's tables.
GetObjectCount Method Get the number of primary objects in the database (doesn't include indexes, fields, relationships, etc.). The count for tables includes the system tables (those whose names begin with MSys).
GetQueryCount Method Get the count of queries in the currently open database. When you create objects with DAO, the collection object that holds that object is not automatically refreshed. This means that when you programmatically create objects, or other users create objects on a shared database, the collection holding the object may not show the most recent additions and deletions. For example, if your code creates a Table object and appends that object to the database's TableDefs collection, the object is not visible to your code, or to other users until a Refresh is issued. To ensure that you are looking at the most recent state of an object's collection, call this procedure with the fRefresh parameter set to True. ' Params : fRefresh True to refresh the database's relations collection, False to get the current state
GetQueryType Method Get the type of the specified query. Microsoft Jet supports several types of query objects. The type of the query is identified by a long integer value in its Type property. This procedure takes the value of the Type property and translates it to the English language name for the type of the query.
GetRelationCount Method Get the count of table relationships in the currently open database. When you create objects with DAO, the collection object that holds that object is not automatically refreshed. This means that when you programmatically create objects, or other users create objects on a shared database, the collection holding the object may not show the most recent additions and deletions. For example, if your code creates a Table object and appends that object to the database's TableDefs collection, the object is not visible to your code, or to other users until a Refresh is issued. To ensure that you are looking at the most recent state of an object's collection, call this procedure with the fRefresh parameter set to True.
GetTableCount Method Get the count of tables in the currently open database. When you create objects with DAO, the collection object that holds that object is not automatically refreshed. This means that when you programmatically create objects, or other users create objects on a shared database, the collection holding the object may not show the most recent additions and deletions. For example, if your code creates a Table object and appends that object to the database's TableDefs collection, the object is not visible to your code, or to other users until a Refresh is issued. To ensure that you are looking at the most recent state of an object's collection, call this procedure with the fRefresh parameter set to True.
GetTableIndexCount Method Get the count of indexes on the specified table
IndexFieldsToArray Method Populate an array with a list of fields in the specified index.
IndexFieldsToString Method Populate a string with a delimited list of fields in an index.
IsValidJetName Method Determine if the passed name conforms to Jet naming rules. Jet and DAO use the following rules: - Names can be up to 64 characters long. - Names can include any combination of letters, numbers, spaces, but cannot contains periods (.), exclamation points (!), accent grave (`) or brackets ([ ]). - Names cannot begin with leading spaces. - Names cannot include control characters (ASCII values 0 through 31). Note that even though Jet and DAO allow you to use spaces in object names, you should avoid doing this. Spaces in object names are not legal in other systems such as SQL server, and trying to integrate/upsize Jet database with spaces in names is a very difficult and tedious process. Additionally, spaces in object names require special delimiting when you refer to those objects in code.
ObjectExists Method Determine if the named object exists in the currently opened database object.
ObjectsToArray Method Populate the supplied array with a list of object names of the specified type.
ObjectsToString Method Get a string with a list of object names of the specified type.
QueryFieldsToArray Method Populate an array with a list of fields in a query.
QueryFieldsToString Method Populate a string with a delimited list of fields in a query.
QueryParametersToArray Method Populate an array with a list of parameters in a query.
QueryParametersToString Method Populate a string with a delimited list of parameters in a query.
RelationFieldsToArray Method Populate an array with a list of fields in a relation
RelationFieldsToString Method Populate a string with a delimited list of fields in a relation.
TableFieldsToArray Method Populate an array with a list of fields in a table
TableFieldsToString Method Populate a string with a delimited list of fields in a table.
FieldTypeString Private Convert a field type ID (number) to a string.
TableIndexesToArray Method Populate an array with a list of indexes in a table.
TableIndexesToString Method Populate a string with a delimited list of indexes in a table.

Example Code for Using Class: JetDatabase

'
' Example of the CJetDatabase Class
'
' 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_CJetDatabase()
  ' Comments: Examples of using the CJetDatabase class to use DAO with Microsoft Access Jet database objects for VBA and VB6.
  '           See the Immediate Window for results.

  ' This example assumes that the sample files are located in the folder named by this constant.
  Const cstrSamplePath As String = "C:\Total Visual SourceBook 2013\Samples\"
  Const cstrSampleDatabase As String = cstrSamplePath & "SAMPLE.MDB"
  Const cstrSampleQuery As String = "DAOSampleQuery1"
  Const cstrSampleQuery2 As String = "DAOSampleQuery2"
  Const cstrSampleTable1 As String = "Categories"
  Const cstrSampleTable2 As String = "DAOSample1"

  Dim clsJetDatabase As CJetDatabase
  Dim dbs As DAO.Database
  Dim lngRet As Long
  Dim strTmp As String
  Dim strRelName As String
  Dim arrStrings() As String
  Dim lngCounter As Long

  ' Instantiate the class
  Set clsJetDatabase = New CJetDatabase
  Debug.Print "CJetDatbase class instantiated."

  ' Specify the database to use, which must be done before the other methods can be called.
  ' A benefit of using this class is that you don't need to specify the database when calling each of its methods.
  ' In Access, to use the current database, pass CurrentDb.Name instead of cstrSampleDatabase
  If clsJetDatabase.OpenDB(cstrSampleDatabase, False, False) Then
    ' If the database has ever been opened by Access, or it was created by Access, the following call will identify the version of Access used.
    Debug.Print "Access Version: " & clsJetDatabase.AccessVersion

    ' Jet database version
    Debug.Print "Jet Version: " & clsJetDatabase.Version

    ' Database format in a recognizable string
    Debug.Print "Access Database Format: " & clsJetDatabase.DatabaseFormat

    ' Use the Database property of the class to expose the underlying database object.
    ' Using this property, you have full access to the methods and properties of the underlying database object.
    Debug.Print "The full path of the database is: " & clsJetDatabase.Database.name

    ' Get other miscellaneous pieces of information
    Debug.Print "Name returns: " & clsJetDatabase.name

    ' To see the LastErrorDescription and LastErrorNumber properties in action, we'll disable error-trapping and trigger an error.
    On Error Resume Next

    ' The following call will always fail: Jet databases don't have a container object named 'Ice Cream'.
    lngRet = clsJetDatabase.Database.Containers("Ice Cream").Documents.Count

    Debug.Print "Last Error Description: " & clsJetDatabase.LastErrorDescription
    Debug.Print "Last Error Number: " & clsJetDatabase.LastErrorNumber

    On Error GoTo 0

    Debug.Print "Path returns: " & clsJetDatabase.Path

    Debug.Print "IsAccessDatabase: this database " & IIf(clsJetDatabase.IsAccessDatabase, "was", "wasn't") & " created by or opened by Microsoft Access."

    ' Now assign a password, and then remove it.
    If clsJetDatabase.PasswordChange("password") Then
      Debug.Print "Password changed to 'password'"
      If clsJetDatabase.PasswordRemove Then
        Debug.Print "Password removed"
      Else
        MsgBox "Password could not be removed. Open the database and remove it manually."
      End If
    Else
      Debug.Print "Password could not be assigned."
    End If

    ' Now try to compact the database. This will close the database, compact it and reopen it.
    ' (can't be done if running this in Access and trying to compact the current database)
    If clsJetDatabase.Compact Then
      Debug.Print "Compact: successful"
    Else
      Debug.Print "Compact: failed"
    End If

    ' Try to encrypt the database (can't be done if running this in Access on the current database)
    If clsJetDatabase.Encrypt Then
      Debug.Print "Encrypt: successful"
    Else
      Debug.Print "Encrypt: failed"
    End If

    ' Try to decrypt the database (can't be done if running this in Access on the current database)
    If clsJetDatabase.Decrypt Then
      Debug.Print "Decrypt: successful"
    Else
      Debug.Print "Decrypt: failed"
    End If

    ' The following example empties all tables in the current database. It
    ' is commented out because it is potentially dangerous. Uncomment and run
    ' this example part only when the class points to a temporary copy database.
    'If MsgBox("Are you sure you want to empty all tables in the current database?", vbCritical + vbYesNo) = vbYes Then
    'clsJetDatabase.EmptyAllTables
    'End If

    ' Get the counts for various object types.
    Debug.Print "Total Access Objects: " & clsJetDatabase.GetObjectCount
    Debug.Print "Count of Tables: " & clsJetDatabase.GetTableCount(True)
    Debug.Print "Count of Queries: " & clsJetDatabase.GetQueryCount(True)
    Debug.Print "Count of Forms: " & clsJetDatabase.GetAccessFormCount
    Debug.Print "Count of Reports: " & clsJetDatabase.GetAccessReportCount
    Debug.Print "Count of Macros: " & clsJetDatabase.GetAccessMacroCount
    Debug.Print "Count of Modules: " & clsJetDatabase.GetAccessModuleCount
    Debug.Print "Count of Relations: " & clsJetDatabase.GetRelationCount(True)
    Debug.Print "Count of Indexes: " & clsJetDatabase.GetDatabaseIndexCount

    Debug.Print "Count of Indexes in the [" & cstrSampleTable1 & "]: " & clsJetDatabase.GetTableIndexCount(cstrSampleTable1)

    Debug.Print "Type of query [" & cstrSampleQuery & "] is: " & clsJetDatabase.GetQueryType(cstrSampleQuery)

    ' Show the fields in the PrimaryKey index of a sample table.
    lngRet = clsJetDatabase.IndexFieldsToArray(cstrSampleTable2, "PrimaryKey", arrStrings())
    Debug.Print "IndexFieldsToArray(): there are " & lngRet & " fields in " & " the Primary Key index."
    For lngCounter = 0 To lngRet - 1
      Debug.Print "  Field (" & lngCounter & ") " & arrStrings(lngCounter)
    Next lngCounter

    ' Show the fields in the PrimaryKey index of a sample table using the string method.
    lngRet = clsJetDatabase.IndexFieldsToString(cstrSampleTable2, "PrimaryKey", strTmp)
    Debug.Print "IndexFieldsToString(): " & lngRet & " fields in " & "the Primary Key index."

    ' Test valid names for Jet objects
    strTmp = "[bad.name with.dots]"
    Debug.Print strTmp & IIf(clsJetDatabase.IsValidJetName(strTmp), " is", " isn't") & " a valid Jet Name."

    strTmp = "This is a valid name"
    Debug.Print strTmp & IIf(clsJetDatabase.IsValidJetName(strTmp), " is", " isn't") & " a valid Jet Name."

    ' Test the ObjectExists method
    Debug.Print "ObjectExists: table [" & cstrSampleTable1 & "] " & _
      IIf(clsJetDatabase.ObjectExists(cjoTypeTable, cstrSampleTable1), "exists", "doesn't exist") & " in the current database."

    Debug.Print "The Foobar table " & _
      IIf(clsJetDatabase.ObjectExists(cjoTypeTable, "Foobar"), "exists", "doesn't exist") & " in the current database."

    ' Get a list of Access forms using the array method
    lngRet = clsJetDatabase.ObjectsToArray(cjoTypeAccessForm, arrStrings())
    Debug.Print "ObjectsToArray(): there are " & lngRet & " Access Forms in the database: "
    For lngCounter = 0 To lngRet - 1
      Debug.Print "  Form (" & lngCounter & ") " & arrStrings(lngCounter)
    Next lngCounter

    ' Get a list of queries using the string method
    strTmp = ""
    lngRet = clsJetDatabase.ObjectsToString(cjoTypeQuery, strTmp)
    Debug.Print "ObjectsToString(): there are " & lngRet & " queries in the database: " & strTmp

    ' Show the fields in the sample query using the array method.
    lngRet = clsJetDatabase.QueryFieldsToArray(cstrSampleQuery, True, arrStrings())
    Debug.Print "QueryFieldsToArray(): there are " & lngRet & " fields in the [" & cstrSampleQuery & "] query: "
    For lngCounter = 0 To lngRet - 1
      Debug.Print "  Field (" & lngCounter & ") " & arrStrings(lngCounter)
    Next lngCounter

    ' Show the fields in the sample query using the string method.
    strTmp = ""
    lngRet = clsJetDatabase.QueryFieldsToString(cstrSampleQuery, True, strTmp)
    Debug.Print "QueryFieldsToString(): there are " & lngRet & " fields in the [" & cstrSampleQuery & "] query: " & strTmp

    ' Show the parameters in the sample query
    lngRet = clsJetDatabase.QueryParametersToArray(cstrSampleQuery2, arrStrings)
    Debug.Print "QueryParametersToArray(): there are " & lngRet & " parameters in the [" & cstrSampleQuery2 & "] query: "
    For lngCounter = 0 To lngRet - 1
      Debug.Print "  Parameter (" & lngCounter & ") " & arrStrings(lngCounter)
    Next lngCounter

    ' Show the parameters in the sample query using the string method
    strTmp = ""
    lngRet = clsJetDatabase.QueryParametersToString(cstrSampleQuery2, strTmp, ";")
    Debug.Print "QueryParametersToString(): there are " & lngRet & " parameters in the [" & cstrSampleQuery2 & "] query: " & strTmp

    Set dbs = DAO.OpenDatabase(cstrSampleDatabase)
    ' Get the name of the first relation to use for the following examples
    If dbs.Relations.Count > 0 Then
      strRelName = dbs.Relations(0).name

      ' Show the fields in the sample relation using the Array method.
      lngRet = clsJetDatabase.RelationFieldsToArray(strRelName, arrStrings)
      Debug.Print "RelationFieldsToArray(): there are " & lngRet & " field(s) in the [" & strRelName & "] relation: "
      For lngCounter = 0 To lngRet - 1
        Debug.Print "  Relation (" & lngCounter & ") " & arrStrings(lngCounter)
      Next lngCounter

      ' Show the fields in the sample relation using the string method.
      strTmp = ""
      lngRet = clsJetDatabase.RelationFieldsToString(strRelName, strTmp)
      Debug.Print "RelationFieldsToString(): There are " & lngRet & _
        " fields in the [" & strRelName & "] relation: " & strTmp
    End If
    dbs.Close

    ' Show the fields in a table using the array method
    lngRet = clsJetDatabase.TableFieldsToArray(cstrSampleTable1, True, arrStrings)
    Debug.Print "TableFieldsToArray(): there are " & lngRet & " fields in the [" & cstrSampleTable1 & "] table: "
    For lngCounter = 0 To lngRet - 1
      Debug.Print "  Field (" & lngCounter & ") " & arrStrings(lngCounter)
    Next lngCounter

    ' Show the fields in a table using the string method
    strTmp = ""
    Debug.Print "TableFieldsToString method:"
    lngRet = clsJetDatabase.TableFieldsToString(cstrSampleTable1, True, strTmp, "|")
    Debug.Print "TableFieldsToString9): there are " & lngRet & " fields in the [" & cstrSampleTable1 & "] table: " & strTmp

    ' Before we do the rest of the examples, let's close the database that we had the class open with the OpenDB method.
    clsJetDatabase.CloseDB
    Debug.Print "Closing the database."

    ' Now let's open a database within our module
    Set dbs = DAO.OpenDatabase(cstrSampleDatabase)
    Debug.Print "Opening our own database."

    ' Since the class needs an open database to work, let's associate our database with the class by setting the Database property of the class to our database.
    Set clsJetDatabase.Database = dbs
    Debug.Print "Passing our database to the class."

    ' All subsequent operations that the class makes on the database will
    ' happen through our instance of the open database.

    ' Show the indexes on a table using the array method
    lngRet = clsJetDatabase.TableIndexesToArray(cstrSampleTable2, arrStrings())
    Debug.Print "TableIndexesToArray(): there are " & lngRet & " indexes on the [" & cstrSampleTable2 & "] table: "
    For lngCounter = 0 To lngRet - 1
      Debug.Print "  Index (" & lngCounter & ") " & arrStrings(lngCounter)
    Next lngCounter

    ' Show the indexes on a table using the string method.
    strTmp = ""
    Debug.Print "TableIndexesToString method: "
    lngRet = clsJetDatabase.TableIndexesToString(cstrSampleTable2, strTmp, "|")
    Debug.Print "TableIndexesToString(): there are " & lngRet & " fields in the [" & cstrSampleTable2 & "] table: " & strTmp

    ' Since we (and not the class) opened the current database, let's close it
    dbs.Close
    Debug.Print "Closing the database."
    Set dbs = Nothing

    ' Close the class
    clsJetDatabase.CloseDB
    Debug.Print "CloseDB(): closed the database."
  End If

  Set clsJetDatabase = 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