Problem Creating Database (DAO/JET)

[Zaf Khan]

Well I've come across my first problem, and am using DAO until I get used to it then will try to do ADO as I find the ado a bit too deep.

Anyway, here's my code:
It keeps giving me an error where I've highlighted the text:
Also, what i'm doing is creating a database via VB, but it keeps giving me an error saying something about wrong type,
The line causing the problems is at the bottom of the text and looks:

'---------------------------------------------------------
ESmartDB.TableDefs.Append ClientsTable
'---------------------------------------------------------





Option Explicit
'the workspace
Dim ESmartWS As Workspace

'the clients database
Dim ESmartDB As Database

'the clients table
Dim ClientsTable As TableDef

'the recordset
Dim ClientRecord As Recordset

'the clients table fields
Dim myClientField(35) As Field

'the index (primary key)
Dim myClientsIndex As Index

'the index field
Dim myCliIdxField(1) As Field



'the sub
Sub MakeEuroSmartDb()

Dim counta As Integer

'--> init database
'init workspace
Set ESmartWS = DBEngine.Workspaces(0)
'init database
Set ESmartDB = ESmartWS.CreateDatabase(App.Path & "\EuroSmart.mdb", dbLangGeneral, dbVersion30)
'init table
Set ClientsTable = ESmartDB.CreateTableDef("ClientsDatabaseTable")
'--> set fields
'index
Set myClientField(0) = ClientsTable.CreateField("Index", dblong)
myClientField(0).Attributes = dbAutoIncrField
'company
Set myClientField(1) = ClientsTable.CreateField("Company", dbtext)
myClientField(1).Size = 50
'outlet
Set myClientField(2) = ClientsTable.CreateField("Outlet", dblong)
'title
Set myClientField(3) = ClientsTable.CreateField("Title", dblong)
'contact
Set myClientField(4) = ClientsTable.CreateField("Contact", dbtext)
myClientField(4).Size = 50
'street
Set myClientField(5) = ClientsTable.CreateField("Street", dbtext)
myClientField(5).Size = 30
'district
Set myClientField(6) = ClientsTable.CreateField("District", dbtext)
myClientField(6).Size = 30
'city
Set myClientField(7) = ClientsTable.CreateField("City", dbtext)
myClientField(7).Size = 30
'county
Set myClientField(8) = ClientsTable.CreateField("County", dbtext)
myClientField(8).Size = 30
'postcode
Set myClientField(9) = ClientsTable.CreateField("PostCode", dbtext)
myClientField(9).Size = 10
'country
Set myClientField(10) = ClientsTable.CreateField("Country", dbtext)
myClientField(10).Size = 30
'tel-1
Set myClientField(11) = ClientsTable.CreateField("tel1Number", dbtext)
myClientField(11).Size = 30
'loc-1
Set myClientField(12) = ClientsTable.CreateField("tel1Location", dblong)
'tel-2
Set myClientField(13) = ClientsTable.CreateField("tel2Number", dbtext)
myClientField(13).Size = 30
'loc-2
Set myClientField(14) = ClientsTable.CreateField("tel2Location", dblong)
'tel-3
Set myClientField(15) = ClientsTable.CreateField("tel3Number", dbtext)
myClientField(15).Size = 30
'loc-3
Set myClientField(16) = ClientsTable.CreateField("tel3Location", dblong)
'mobile
Set myClientField(17) = ClientsTable.CreateField("telMobNumber", dbtext)
myClientField(17).Size = 30
'fax
Set myClientField(18) = ClientsTable.CreateField("telFaxNumber", dbtext)
myClientField(18).Size = 30
'emailaddress
Set myClientField(19) = ClientsTable.CreateField("EmailAddress", dbtext)
myClientField(19).Size = 128
'sourced
Set myClientField(20) = ClientsTable.CreateField("Sourced", dblong)
'iscustomer
Set myClientField(21) = ClientsTable.CreateField("IsCustomer", dbVarBinary)
'isactive
Set myClientField(22) = ClientsTable.CreateField("IsActive", dbVarBinary)
'islooking
Set myClientField(23) = ClientsTable.CreateField("IsLooking", dbVarBinary)
'hasmachine
Set myClientField(24) = ClientsTable.CreateField("HasMachine", dbVarBinary)
'alarmdate
Set myClientField(25) = ClientsTable.CreateField("AlarmDate", dbDate)
'alarmtime
Set myClientField(26) = ClientsTable.CreateField("AlarmTime", dbtext)
myClientField(26).Size = 10
'alarmset
Set myClientField(27) = ClientsTable.CreateField("AlarmSet", dbVarBinary)
'startdate
Set myClientField(28) = ClientsTable.CreateField("StartDate", dbDate)
'starttime
Set myClientField(29) = ClientsTable.CreateField("StartTime", dbtext)
myClientField(29).Size = 10
'lastmoddate
Set myClientField(30) = ClientsTable.CreateField("LastModDate", dbDate)
'lastmodtime
Set myClientField(31) = ClientsTable.CreateField("LastModTime", dbtext)
myClientField(31).Size = 10
'enquirydate
Set myClientField(32) = ClientsTable.CreateField("EnquiryDate", dbDate)
'enquirytime
Set myClientField(33) = ClientsTable.CreateField("EnquiryTime", dbtext)
myClientField(33).Size = 10
'comments
Set myClientField(34) = ClientsTable.CreateField("Comments", dbmemo)
'historyfile
Set myClientField(35) = ClientsTable.CreateField("HistoryFile", dbtext)
myClientField(35).Size = 128
'--> add fields
For counta = 0 To 35
ClientsTable.Fields.Append myClientField(counta)
Next
'--> add index/key
ESmartDB.TableDefs.Refresh
' Now add an index.
Set myClientsIndex = ClientsTable.CreateIndex("Index")
myClientsIndex.Primary = True
myClientsIndex.Unique = True
'index number
Set myCliIdxField(0) = myClientsIndex.CreateField("Index")
myClientsIndex.Fields.Append myCliIdxField(0)
'company
Set myCliIdxField(1) = myClientsIndex.CreateField("Company", dbtext)
myCliIdxField(1).Size = 50
myClientsIndex.Fields.Append myCliIdxField(1)
'add index
ClientsTable.Indexes.Append myClientsIndex
' Save TableDef object by appending it to TableDefs collection

'--> ERROR HERE <-----------------------------------
ESmartDB.TableDefs.Append ClientsTable
'--> ERROR HERE <-----------------------------------


MsgBox ("Database Created")
ESmartDB.Close

End Sub


Thanks in advance
Zaf Khan
{;->

[Frans C]

It worked as soon as I replaced the dbVarBinary with another fieldtype. I don't think this is a valid datatype for a jet database. Looking at the fieldnames, I think a boolean would do. So I replaced them with dbBoolean.

[Zaf Khan]

Thanks Frans,

I will try it out,
The reson i used vbVarBinary is because it is a BIT variable (taken from access help) and the BOOLEAN variable is a BYTE.

Well i guess i wont know how it effects the data till i try it.

Also the one time it did work, when i open the DB in access the table was not present.

DocZaf
{;->