Converting DAO DDL elements to ADOX

See Also

Microsoft Access includes additional libraries to use when converting DAO's Data Definition Language (DDL) functionality to the ActiveX Data Objects DDL functionality.

Note   ADO DDL is not referenced by default in Microsoft Access 2000. You must first create a reference to MSADOX.DLL by selecting References on the Tools menu and selecting Microsoft ADO Ext. 2.1 for DDL and Security.

DAO to ADO object map

DAO ADO(ADOX)
DBEngine None
Workspace None
Database Catalog
QueryDef(Simple Select) View
QueryDef(Action/Paramaterized) Procedure
TableDef Table
Parameter Parameter
Field Column

DAO to ADO security constants map

DAO ADO(ADOX) Note
dbSecNoAccess adRightNone User has no access to document
dbSecFullAccess adRightFull Has full object access
dbSecDelete adRightDelete Can delete object
dbSecReadSec adRightReadDesign Can read security-related information
dbSecWriteSec adRightWriteDesign Can change access
dbSecWriteOwner adRightWriteOwner Can change owner
dbSecCreate adRightCreate or adRightWithGrant Can create new documents
dbSecReadDef adRightRead or adRightReadDesign Read the definition of a table or query
dbSecWriteDef adRightUpdate or adRightWriteDesign Alter the definition of a table or query
dbSecRetrieveData adRightRead Read data stored ina table or query. Implicitly grants read permission to the definition.
dbSecInsertData adRightInsert Insert new rows into table or query
dbSecReplaceData adRightUpdate Modify table or query data
dbSecDeleteData adRightDelete or adRightWithGrant Delete rows from table or query

Jet Field Data Types DAO Data types ADO Data Types
Yes/No dbBoolean adBoolean
Number (FieldSize = Byte) dbByte adUnsignedTinyInt
AutoNumber
(FieldSize = Long Integer)		 dbLong adInteger
Currency dbCurrency adCurrency
Date/Time dbDate adDate
Number
(FieldSize = Double)		 dbDouble adDouble
Number or AutoNumber
(FieldSize =Replication ID)		 dbGUID adGUID
Number
(FieldSize = Long Integer)		 dbLong adInteger
OLE Object dbLongBinary adLongVarBinary
Memo dbMemo adLongVarWChar
Number
(FieldSize = Single)		 dbSingle adSingle
Number
(FieldSize = Integer)		 dbInteger adSmallInt
Text dbText adVarWChar
Hyperlink dbMemo adLongVarWChar
Numeric (FieldSize=Decimal) dbDecimal adDecimal

  1. DAO
  1. ADO
Creating a Database 
Dim wsp As Workspace, dbs As Database
Set wsp = DBEngine(0)
Set dbs = wsp.CreateDatabase("Newdb.mdb")
 
Dim cat As New ADOX.Catalog
cat.Create "Provider = Microsoft.Jet.OLEDB.4.0;" _ 
       & "Data Source = Newdb.mdb"
Creating a Table 
Dim dbs As Database 
Dim tdf As TableDef, fld As Field    

Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("NewTable")
Set fld = tdf.CreateField("FName", dbText, 50)
tdf.Fields.Append fld
dbs.TableDefs.Append tdf    
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table

Set cat.ActiveConnection = CurrentProject.Connection
tbl.Name = “NewTable”
tbl.Columns.Append “FName”, adVarWChar, 50
Cat.Tables.Append tbl
Creating a Query 
Dim dbs As Database 
Dim qdf As QueryDef

Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("NewQuery", _       
     "Select * From Employees")
dbs.QueryDefs.Append qdf    
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command

Set cat.ActiveConnection = CurrentProject.Connection
cmd.CommandText “Select * From Employees”
Cat.Views.Append "NewQuery", cmd