Creating A Database In VB

I am trying to create a database through my code. The CreateDatabase works fine.
But when I try to create the first field - Set fields(0) = table.CreateField ("ID", dbLong)
I get a "Type Mismatch Error", I cannot figure out why this is happening.
I have posted my code below, please look at it and tell what is wrong. Thanks!!!

private Sub cmdCreateDB_Click()
Dim db as Database
Dim table as TableDef
Dim idx as Index
Dim fields(10) as Field
Dim fieldidx as Field
Dim path as string
Dim i as Integer

on error GoTo ErrHandler

'//Ask User for Path And Name Of Database
path = InputBox("Enter The Path And Name Of The Database to Create.", "Create Database")

'//Create Database
set db = CreateDatabase(path, dbLangGeneral, dbEncrypt)

'//Create Table In Database
set table = db.CreateTableDef("Inspection")

'//Create Fields In Table
'//Primary Key
set fields(0) = table.CreateField("ID", dbLong)
fields(0).Attributes = fields(0).Attributes + dbAutoIncrField
set idx = table.CreateIndex("PrimaryKey")
set fieldidx = idx.CreateField("ID", dbLong)
idx.fields.Append fieldidx
idx.Primary = true

'//Data Fields
set fields(1) = table.CreateField("Data1", dbText, 255)
set fields(2) = table.CreateField("Data2", dbText, 255)
set fields(3) = table.CreateField("Data3", dbText, 255)
set fields(4) = table.CreateField("Data4", dbText, 255)
set fields(5) = table.CreateField("Data5", dbText, 255)
set fields(6) = table.CreateField("Data6", dbText, 255)
set fields(7) = table.CreateField("Data7", dbText, 255)
set fields(8) = table.CreateField("Data8", dbText, 255)

'//Inspection Status
set fields(9) = table.CreateField("InspStatus", dbText, 50)

'//date
set fields(10) = table.CreateField("date", dbDate)

'//Append Field Objects to Table
i = 0
for i = 0 to 10
table.fields.Append fields(i)
next i

'//Save Table Def
db.TableDefs.Append table
db.TableDefs.Refresh

Exit Sub

ErrHandler:
ProcessError ("frmAccess.cmdCreateDB_Click")

End Sub

Try this (DAO)

Sub CreateDB(sDBPath As String)

Dim tdExample As TableDef
Dim fldForeName As Field
Dim fldSurname As Field
Dim fldDOB As Field
Dim fldFurtherDetails As Field
Dim dbDatabase As Database
Dim sNewDBPathAndName As String

' Set the new database path and name in string (using time:seconds for some randomality
sNewDBPathAndName = sDBPath ' Create a new .MDB file (empty at creation point!)
Set dbDatabase = CreateDatabase(sNewDBPathAndName, dbLangGeneral, dbEncrypt)

' Create new TableDef (table called 'Example')
Set tdExample = dbDatabase.CreateTableDef("Example")

' Add fields to tdfTitleDetail.
Set fldForeName = tdExample.CreateField("Fore_Name", dbText, 20)
Set fldSurname = tdExample.CreateField("Surname", dbText, 20)
Set fldDOB = tdExample.CreateField("DOB", dbDate)
Set fldFurtherDetails = tdExample.CreateField("Further_Details", dbMemo)
' Append the field objects to the TableDef
tdExample.Fields.Append fldForeName
tdExample.Fields.Append fldSurname
tdExample.Fields.Append fldDOB
tdExample.Fields.Append fldFurtherDetails

' Save TableDef definition by appending it to TableDefs collectio
' n.
dbDatabase.TableDefs.Append tdExample
MsgBox "New .MDB Created - '" & sNewDBPathAndName & "'", vbInformation
' Now look for the new .MDB using File Manager!

End Sub


Iouri Boutchkine
[email protected]

Using ADO

Make sure you have a reference set to the ADOX object library then use the following code.

Dim objCatalog as ADOX.Catalog

Set objCatalog = New ADOX.Catalog
objCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\mynewdb.mdb"


'to create tables

Dim catAccess As New ADOX.Catalog
Dim tblValExp As New ADOX.Table
Dim cnnAccess As New ADODB.Connection
Dim rstValExp As New ADODB.Recordset
catAccess.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb"
catAccess.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb"
With tblValExp
.Name = "tblValExp1"
.Columns.Append "Stock Name", adVarWChar, 40
.Columns.Append "Stock Symbol", adVarWChar, 6 '1
.Columns.Append "Stock Price", adSingle '2
.Columns.Append "Est Date", adVarWChar, 10 '3
.Columns.Append "Est Year", adVarWChar, 6 '4
.Columns.Append "Price High(0)", adSingle '5
.Columns.Append "Price High(1)", adSingle '6
.Columns.Append "Price High(2)", adSingle '7
.Columns.Append "Price High(3)", adSingle '8
.Columns.Append "Price High(4)", adSingle '9
.Columns.Append "Price High(5)", adSingle '10
End With
catAccess.Tables.Append tblValExp
Set tblValExp = Nothing
Set catAccess = Nothing
cnnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb;Persist Security Info=False"
rstValExp.Open "SELECT * FROM tblValExp1", cnnAccess, adOpenKeyset, adLockOptimistic
rstValExp.AddNew
rstValExp.Fields(0).Value = "Name"
rstValExp.Fields(1).Value = "Symbol"
rstValExp.Fields(2).Value = 1.5
rstValExp.Fields(3).Value = Format(Now(), "dd/mm/yyyy")
rstValExp.Fields(4).Value = "2000"
rstValExp.Fields(5).Value = 1.5
rstValExp.Fields(6).Value = 1.5
rstValExp.Fields(7).Value = 1.5
rstValExp.Fields(8).Value = 1.5
rstValExp.Fields(9).Value = 1.5
rstValExp.Fields(10).Value = 1.5
rstValExp.Update
rstValExp.MoveFirst
Set rstValExp = Nothing
Set cnnAccess = Nothing






Iouri Boutchkine
[email protected]

When I try using your sub I get the same error
"Type Mismatch" when trying to create a Field.

Using this method, how can I make the first field
the primary key and have it set as type autonumber?

I figured out the problem. I am referencing the
DAO 3.6 Library. The problem was I needed to
move it up on the priority in the project
references. Thanks Iouri for your code examples.

I figured out the problem with my original code.
I had to move my reference to the DAO Library
up on the priority list in the project references.
Thanks for your quick replies!

Stupid me! Moving the priority for the DAO Library
worked but all I needed to do was add the
DAO indentifier to my declarations. I am using
the ADO Library as well and if you do not include
the Identifier, which ever Library has the most
priority is the one referenced in the declarations
All that is needed is to change my declarations
to the ones below. I must have had a brain fart!

Dim db as DAO.Database
Dim table as DAO.TableDef
Dim idx as DAO.Index
Dim fields(10) as DAO.Field
Dim fieldidx as DAO.Field

Create autonumber field

Set col = New ADOX.Column
Set col.ParentCatalog = cat
With col
.Type = adInteger
.Name = "ID"
.Properties("Autoincrement") = True
End With
tbl.Columns.Append col


Iouri Boutchkine
[email protected]

Already out of vote!
Cheers
Cesare Imperiali

Special thanks to Lothar "the Great" Haensler. Come back soon, you Guru.