vb DAO Createdatabase problem

CivilServant

I can't this simple piece of code to work. Anyone know why? Or some code that creates a DAO database in code?

'Create Orders Database
Dim newtbldef As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index

Set db2 = DBEngine(0).OpenDatabase("Orders")
Set newtbldef = db2.CreateTableDef("TodaysOrders")

Set fld = newtbldef.CreateField("OrderNumber", dbInteger)
fld.OrdinalPosition = 1
fld.Attributes = dbAutoIncrField

newtbldef.Fields.Append fld

db2.TableDefs.Append newtbldef


The book says it should be simple but I can't get it working! Damn it, it's holding everything up else I have to do.

cgarvey

What error are you getting?

Have you "referenced" the right libraries (ADO, etc.) in VB?

.cg

amen

Hi,

1: If you want to use DAO in VB then you need to open your vb project go to Project->References and select Microsoft DAO 3.XX Object Libray

where XX is whatever version you have installed on your machine.

2: Secondly what is DBEngine defined as and where is it initialised

3: Where exactly is the code blowing up ?

e

CivilServant

No I didn't reference the library. I've tried it just now and it still isn't working.

I didn't include the database declaration with the orginal snippet. It's declared just above the tabledef.

The problem is when I compile the code. I get a runtime error 3001, invalid argument on the last line. Seems like it doesn't want to append the table onto the database.

I'm using Microsoft DAO 3.51 Object Library so that would be Access 97 databases.

I define db as :

Set db2 = DBEngine(0).OpenDatabase("Orders")

then after I run the program it manages to create the db and I use:

Set db2 = DBEngine(0).CreateDatabase("Orders", dbLangGeneral)

amen

the problem is with the
fld.Attributes = dbAutoIncrField

you can only use dbAutoIncrField on Long and not integer

I've rewritten your function below and added some error handling

e

Private Function AddDBTable()
On Error GoTo ErrorHandler

Dim db2 As Database
Dim newtbldef As DAO.TableDef

'open the database
Set db2 = DBEngine.OpenDatabase("c:\temp\test1")
'create the new table
Set newtbldef = db2.CreateTableDef("TodaysOrders")

With newtbldef
.Fields.Append .CreateField("OrderNumber", dbLong)
.Fields("OrderNumber").Attributes = dbAutoIncrField
.Fields("OrderNumber").OrdinalPosition = 1
End With
db2.TableDefs.Append newtbldef

Exit Function
ErrorHandler:
Dim sErrText
Dim errDB As DAO.Error

For Each errDB In DBEngine.Errors
sErrText = sErrText + vbNewLine + errDB.Description + "Number " + CStr(Err.Number) + " Jet Number " + CStr(errDB.Number)
Next
MsgBox (sErrText)
End Function

CivilServant

Genius amen!!

That's exactly it. I can even remember typing it in from the book I have and changing the variable to the int cos I though Long was too much.

Thanks, for ur attention to detail!