oblique.ch

ADOX FAQ

Welcome to the "unofficial" ActiveX Data Objects (ADO) Frequently Asked Questions (FAQ) web page.  These FAQs are from the Microsoft ADO Newsgroup and are compiled by oblique.ch.

Do you know of an ADOX FAQ which is not listed here?  If so, please email to oblique.ch and he will add it to this FAQ web page.

Table of Contents

Q1) Where can I get the latest version of ADOX? 
Q2) How do I create a new Access database?
Q3) How do I create a Autonumber column in a new Access database?
Q4) Can I create a SQL Server database with ADOX?   
Q5) Which ADOX objects, collections, properties, and methods are supported by OLE DB Providers?
Q6) How can I check if a table exists in a database?
Q7) How do I compact an Access database?
Q8) How to create a GUID column in an Access database that automatically generates a GUID?
Q9) How do I create an Access table that is linked to a SQL Server table?

Q1) Where can I get the latest version of ADOX?

ADOX is part of Microsoft Data Access Components (MDAC).  
You can download the latest version of MDAC from Microsoft's Data web site.

Once installed, you can find the ADOX ReadMe at:
\Program Files\Common Files\System\ado\adoxreadme.txt

You can find the on-line ADOX documentation at the Microsoft MSDN web site.

 

Q2) How do I create a new Access database?

Using the Catalog's Create method and specify the "Engine Type" in the connection string.  

For example:

oCat.Create  "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=.\new.mdb;" & _
              "Jet OLEDB:Engine Type=5;"

Where:
    Engine Type = 4 creates an Access 97 database in 3.5 format
    Engine Type = 5 creates an Access 2000 database in 4.0 format  (default)

Note: Access 97 (3.5 format) will not be able to open up an Access 2000 (4.0 format) database. However, Access 2000 will be able to open up an Access 97 or 2000 database.  

 

Q3) How do I create a AutoNumber column in a new Access 97 database?

The secret is to set the JET OLEDB Engine Type to 4 in order to create an Access 97 database, then set the column's Autoincrement property to True when creating the column, plus make it a primary key.

Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim oColumn As ADOX.Column
Dim oKey As ADOX.Key

' Delete any previous temp file
On Error Resume Next
Kill ".\new35.mdb"
On Error GoTo 0

' Create a new database in 3.5 format (Access 97)
Set oCat = New ADOX.Catalog
oCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=.\new35.mdb;" & _
            "Jet OLEDB:Engine Type=4;"

' Create a new Table
Set oTable = New ADOX.Table
oTable.Name = "WebSite"
oCat.Tables.Append  oTable

' Create a new AutoNumber ID Column
Set oColumn = New ADOX.Column
With oColumn
    .Name = "WebSiteID"
    .Type = adInteger
    Set .ParentCatalog = oCat ' Must set before setting properties
    .Properties("Autoincrement") = True
End With
oCat.Tables("WebSite").Columns.Append  oColumn

' Create a new Primary Key for the table
Set oKey = New ADOX.Key
With oKey
    .Name = "PrimaryKey"
    .Type = adKeyPrimary
    .RelatedTable = "WebSite"
    .Columns.Append "WebSiteID"
End With
oCat.Tables("WebSite").Keys.Append  oKey

Also see Q252908

 

Q4) Can I create a SQL Server database with ADOX ?

No, you cannot use ADOX to create a SQL Server database.  However, you can use one of the following methods:

If using an ADO Connection

Dim oConn As ADODB.Connection
Dim sDatabaseName As String

sDatabaseName = "test1"

Set oConn = New ADODB.Connection
oConn.Open "Provider=SQLOLEDB;Data Source=(local);User ID=sa;Password=;"
oConn.Execute "CREATE DATABASE " & sDatabaseName

If using SQLDMO Object Library

Dim oSQLServer As SQLDMO.SQLServer
Dim oDatabase As SQLDMO.Database
Dim oDBFileData As SQLDMO.DBFile
Dim oLogFile As SQLDMO.LogFile
Dim sDatabaseName As String
Dim sDatapath As String

sDatabaseName = "test1"
sDatapath = "C:\Program Files\Microsoft SQL Server\MSSQL\Data"

' Create a new SQL Server object and connect
Set oSQLServer = New SQLDMO.SQLServer
'  oSQLServer.LoginSecure = True       ' Use integrated security
'  oSQLServer.Connect "(local)"
oSQLServer.Connect "(local)", "sa", "" ' Use standard security

' Create a new Database object
Set oDatabase = New SQLDMO.Database
oDatabase.Name = sDatabaseName

' Create a new DB File object
Set oDBFileData = New SQLDMO.DBFile
With oDBFileData
    .Name = sDatabaseName & "_data"
    .PhysicalName = sDatapath & "\" & sDatabaseName & "_data.mdf"
    .PrimaryFile = True
    .FileGrowthType = SQLDMOGrowth_MB
    .FileGrowth = 1
End With

' Create a new LogFile object
Set oLogFile = New SQLDMO.LogFile
With oLogFile
    .Name = sDatabaseName & "_log"
    .PhysicalName = sDatapath & "\" & sDatabaseName & "_log.ldf"
End With

' Add the DB File and Log file to the database object
oDatabase.FileGroups("PRIMARY").DBFiles.Add oDBFileData
oDatabase.TransactionLog.LogFiles.Add oLogFile

' Add the database to SQL Server (create the database)
oSQLServer.Databases.Add oDatabase

' Close the connection
oSQLServer.Close

' Clean Up
Set oLogFile = Nothing
Set oDBFileData = Nothing
Set oDatabase = Nothing
Set oSQLServer = Nothing

 

Q5) Which ADOX objects, collections, properties, and methods are supported by OLE DB Providers?

It depends on the OLE DB Provider that you are using with ADOX.

The "OLE DB Provider for Microsoft Jet" fully supports all ADOX.  The "Microsoft OLE DB Provider for SQL Server",  the "Microsoft OLE DB Provider for ODBC", and the "Microsoft OLE DB Provider for Oracle" all support ADOX, except as noted below.

Microsoft OLE DB Provider for SQL Server:

All ADOX objects, collections, properties, and methods are supported except:
Catalog:   Create method.
Tables:   Properties for existing tables are read-only 
             (Properties for new tables can be read/write).
Views:   Not supported.
Procedures:  Append method, Delete method, Command property.
Keys:   Append method, Delete method.
Users:   Not supported.
Groups:   Not supported.

Microsoft OLE DB Provider for ODBC

All ADOX objects, collections, properties, and methods are supported except:
Catalog:   Create method.
Tables:   Append method, Delete method. Properties for existing tables are read-only 
             (Properties for new tables can be read/write).
Views:   Append method, Delete method, Command property.
Procedures:   Append method, Delete method, Command property.
Indexes:   Append method, Delete method.
Keys:   Append method, Delete method.
Users:   Not supported.
Groups:   Not supported.

Microsoft OLE DB Provider for Oracle

All ADOX objects, collections, properties, and methods are supported except:
Catalog:   Create method.
Tables:   Append method, Delete method. Properties for existing tables are read-only 
             (properties for new tables can be read/write).
Views:   Append method, Delete method, Command property.
Procedures:   Append method, Delete method, Command property.
Indexes:   Append method, Delete method.
Keys:   Append method, Delete method.
Users:   Not supported.
Groups:   Not supported

 

Q6) How can I check if a table exists in a database?

Use one of the following methods:

Use ADOX

Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim sTable As String
Dim bFoundTable As Boolean

sTable = "customers"

Set oCat = New ADOX.Catalog
oCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=.\advworks.mdb;"

bFoundTable = False
For Each oTable In oCat.Tables
    If UCase(oTable.Name) = UCase(sTable) Then
        bFoundTable = True
        Exit For
    End If
Next

If bFoundTable Then
    MsgBox "Table Found"
Else
    MsgBox "Table not found"
End If

Use ADO

Dim oConn As ADODB.Connection
Dim oRs As ADODB.Recordset

Set oConn = New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source=.\advworks.mdb;"

Set oRs = oConn.OpenSchema(adSchemaTables, _
          Array(Empty, Empty, "customers", "TABLE"))
If Not oRs.EOF Then
    MsgBox "Table Found"
Else
    MsgBox "Table not found"
End If

 

Q7) How do I compact an Access database?

You can't using ADOX.    However, you can by using the "Microsoft Jet and Replication Objects 2.5 Library" (JRO).

Dim oJetEngine As JRO.JetEngine
Dim sSourceConn As String
Dim sDestConn As String

Set oJetEngine = New JRO.JetEngine

' Engine Type = 4 compacts an Access database in 3.5 format
' Engine Type = 5 compacts an Access database in 4.0 format (default)

sSourceConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=.\advworks.mdb;" & _
              "Jet OLEDB:Engine Type=5;"

sDestConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=.\advworks1.mdb;" & _
            "Jet OLEDB:Engine Type=5;"

' Compact the database (makes a new copy)
oJetEngine.CompactDatabase sSourceConn, sDestConn

For more information, see:  Q230501  and  Q273956

 

Q8) How to create a GUID column in an Access database that automatically generates a GUID?

The secret is create a GUID column type and to set it's Jet OLEDB property "AutoGenerate" to True.

Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim oCol As ADOX.Column
Dim sTableName As String

' Create and open a new ADOX Catalog connection
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=.\advworks.mdb;" & _
                        "User ID =admin;" & _
                        "Password=;"
' Create a new Table
Set oTable = New ADOX.Table
sTableName = "table1"
oTable.Name = sTableName
oCat.Tables.Append  oTable

' Create a new GUID Column that AutoGenerates a GUID
Set oCol = New ADOX.Column
With oCol
    .Name = "GUID_ID"
    .Type = adGUID
    .ParentCatalog = oCat
    .Properties("Jet OLEDB:AutoGenerate") = True
End With

' Add the column to the Table
oCat.Tables(sTableName).Columns.Append  oCol

 

Q8) How do I create an Access table that is linked to a SQL Server table?

The secret is to use "ODBC;" in your SQL Server connection string for the linked Access table (otherwise you will get a "80004005 - Could not find installable ISAM" error message). Plus you need to set the JET OLEDB properties for the "linked" table.

Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim sConnString As String

' Set SQL Server connection string used in linked table.
' Note, must have ODBC; in string, or else you will get
' the error: "80004005 - Could not find installable ISAM."
sSConnString = "ODBC;" & _
               "Driver={SQL Server};" & _
               "Server=(local);" & _
               "Database=pubs;" & _
               "Trusted_Connection=Yes;"
               "Uid=sa;" & _
               "Pwd=;"

' Create and open an ADOX connection to Access database
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=.\advworks.mdb;"

' Create a new Table object
Set oTable = New ADOX.Table
With oTable
    .Name = "authors"
    Set .ParentCatalog = oCat
    .Properties("Jet OLEDB:Create Link") = True
    .Properties("Jet OLEDB:Remote Table Name") = "authors"
    .Properties("Jet OLEDB:Link Provider String") = sConnString
End With

' Add Table object to database
oCat.Tables.Append  oTable
oCat.Tables.Refresh

Also see Q240222

 

 Do you know of an ADOX FAQ which is not listed here?  If so, please email to oblique.ch and he will add it to this FAQ web page.

© 2003 - oblique.ch