How to create a database from scratch with ado.

[daamazing1]

I can connect to databases with ado fine, but I need to be able to create a new database with ado, I can't seem to find any info on how to do this?

[Clunietp]

'uses ADO 2.x for DDL and security
Dim strConnectionStringOfNewDB As String
Dim objCat As ADOX.Catalog

'instantiate catalog object
Set objCat = New ADOX.Catalog

'connection string of new db -- includes driver/db type and location
strConnectionStringOfNewDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\NewDB.mdb"

'create it
objCat.Create strConnectionStringOfNewDB

'cleanup
Set objCat = Nothing

[Chris]

You need to reference your project to the ADO 2.x for DDL and security

Dim MyTbl As ADOX.Table
Dim MyCol As ADOX.Column
Dim MyIdx As ADOX.Index

'Create a New Database Q.mdb
Set MyCat = New ADOX.Catalog
MyCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\Q.mdb" & ";Jet OLEDBatabase Password= <Your Password>"
Set MyCat = Nothing

'Create a new Table
Set MyCat = New Catalog
Set MyTbl = New Table
Set MyIdx = New Index

MyCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & App.Path & "\Q.mdb" & _
";Jet OLEDBatabase Password= <Your Password>"

With MyTbl
'Assign the new table name
.Name = "<Your Table Name>"

'Define new create column into the table
.Columns.Append "uName", adWChar, 100
.Columns.Append "uPhone", adWChar, 17
.Columns.Append "uAddress", adWChar, 255
.Columns.Append "uGender", adSmallInt
.Columns.Append "uTitle", adSmallInt

'Define the Index key
With MyIdx
.Name = "<Your Index Name>"
.PrimaryKey = True
.Columns.Append "uName"
.Columns.Append "uPhone"
End With

'Append the new create Index into the new table
.Indexes.Append MyIdx

'Append the new create Table into the Q.mdb database
MyCat.Tables.Append MyTbl
End With
Set MyIdx = Nothing
Set MyTbl = Nothing

End Sub


Note
The follwoing is the data constant use in the ADO Ext.2.1

Constant Description
adTinyInt >> Exact numeric value, precision 3 scale 0.
adSmallInt >> Exact numeric value, precision 5 scale 0.
adInteger >> Exact numeric value, precision 10 scale 0.
adBigInt >> Exact numeric value, precision 19 scale 0.
adUnsignedTinyInt >> Unsigned version of adTinyInt.
adUnsignedSmallInt >> Unsigned version of adSmallInt.
adUnsignedInt >> Unsigned version of adInteger.
adUnsignedBigInt >> Unsigned version of adBigInt.
adSingle >> Single precision floating point number.
adDouble >> Double precision floating point number.
adCurrency >> Currency type.
adDecimal >. Variant decimal type.
adNumeric >> Numeric type.
adBoolean >> Variant Boolean type. 0 is false and ~0 is true.
adUserDefined >> User-defined data type of variable length.
adVariant >> Automation Variant.
adGuid >> Globally Unique Identifier.
adDate >> Automation date.
adDBDate >> Database date data structure.
adDBTime >> Database time data structure.
adDBTimestamp >> Database timestamp structure.
adBSTR >> Pointer to a BSTR.
adChar Fixed-length character string.
adVarChar >> Variable-length character string.
adLongVarChar >> Long variable-length character string.
adWChar >> Wide fixed-length character string.
adVarWChar >> Wide variable-length character string.
adLongVarWChar >> Long, wide variable-length character string.
adBinary >> Fixed-length binary data.
adVarBinary >> Variable-length binary data.
adLongVarBinary >> Long variable-length binary data.

All the Help can be abtain from the Microsoft ActiveX Data Objects (ADO)