Follow-up on creating DB in Code

[Monguse]

Well I thought I had it figured out, the code does create the DB and it creates the first table, however when I try to creat a second table within the same DB I ger an error - 3240 (DB Exist) and it does not create the second table.
Below is the code I am using - (there are several more fields in each table) Can someone pleasepoint out my error?

Many Thanks



Sub Create_DB()
On Error GoTo Err

sDataBaseName = App.Path & "\Printing.mdb"
Set wrkDefault = DBEngine.Workspaces(0)
Set dbsPrint = wrkDefault.CreateDatabase(sDataBaseName, _
dbLangGeneral, dbEncrypt) 'Create New Database
Set tdfWOPrint = dbsPrint.CreateTableDef("PrintWO&quot 'Create Table
Set tdfPSPrint = dbsPrint.CreateTableDef("PrintPS&quot

With tdfWOPrint
.Fields.Append .CreateField("Date", dbText, 12)
.Fields.Append .CreateField("PONum", dbText, 10)
.Fields.Append .CreateField("TOTAL", dbText, 50)
.Fields.Append .CreateField("LotNo", dbText, 15)
.Fields.Append .CreateField("ToDaysDate", dbText, 10)
End With

With tdfPSPrint
.Fields.Append .CreateField("PONum", dbText, 10)
.Fields.Append .CreateField("WONum", dbText, 10)
.Fields.Append .CreateField("DUEDATE", dbText, 12)
.Fields.Append .CreateField("CustomerName", dbText, 50)
.Fields.Append .CreateField("TEL", dbText, 36)
End With

dbsPrint.TableDefs.Append tdfWOPrint
dbsPrint.TableDefs.Append tdfPSPrint
dbsPrint.Close

Exit Sub
Err:
If Err.Number = 3204 Then 'DB Exists
Kill sDataBaseName
Resume
End If

End Sub
"The beauty of the second amendment is, that it will not be needed until
they try to take it." - Thomas Jefferson

 

[HermanvLimbeek]

Place the line
dbsPrint.TableDefs.Append tdfWOPrint
directly after the append fields block (tdfWOPrint).
That might help.

Herman :-Q

 

[bjd4jc]

You can use SQL to create tables as well. If you write a CREATE TABLE statement. It is much more efficient.

In order to execute the statment you would do something similar to this:

MyDb.Execute strSQL

Here is the prototype of the CREATE TABLE SQL statement:

CREATE TABLE tablename (field1name type [(size)] [NOT NULL] [index1] 
[, field2name type [(size)] [NOT NULL] [index2] [, ...]] 
[, CONSTRAINT multifieldindex [, ...]])

The code for your first table would like like:

strSQL = "CREATE TABLE PrintWO (PODate varchar(12), PONum varchar(10), TOTAL varchar(50), LotNo varchar(15), ToDaysDate varchar(10))"
dbsPrint.Execute strSQL