Creating tables using VBA

[tim]

I'll start by saying that I've only been working with Access for the last several weeks, so I may be overlooking something simple

My problem is that I can't seem to create a table using VBA (even though I followed an example program

Can Anyone help me figure out what's going on?

Thanks in advance,Ti

I tried creating a table/ and even deleting a table using VBA, but I can't seem to get the example code to work. I encountered the issue that required that I enable the DAO library, so that's no longer the issue, but I'm still getting an error '3265' every time I run the code. Since I couldn't get the table to create, I thought that I would try to delete a table to test whether the table was even being seen. Apparently, the code doesn't get that far, before it craps out

The example code that I tried for the delete was

Dim db As DAO.Databas
Set db = CurrentD
db.TableDefs.Delete "testtable" 'The error occurs her

I tried this for the create table: (the code ran, but nothing happened) I tried this both with and without the DAO.
Dim db As DAO.Database
Dim td As DAO.TableDe
Dim fld As DAO.Fiel
Dim prp As DAO.Propert
Dim idx As DAO.Inde

Set db = CurrentD
' Create a new TableDef object
Set td = db.CreateTableDef("temptable"

Set fld = td.CreateField("test1", DB_INTEGER, 5
td.Fields.Append fl

Set fld = td.CreateField("test2", DB_TEXT, 5
td.Fields.Append fl

'set primary ke
Set idx = td.createindex("PrimaryKey"
Set fld = idx.CreateField("indexfield"
idx.primary = Tru
idx.unique = Tru
idx.Fields.Append fl
'add index to the indexes collectio
td.indexes.Append id

 

[Chris]

Comments Inline:

I tried creating a table/ and even deleting a table using

VBA, but I can't seem to get the example code to work. I
encountered the issue that required that I enable the DAO
library, so that's no longer the issue, but I'm still
getting an error '3265' every time I run the code. Since
I couldn't get the table to create, I thought that I would
try to delete a table to test whether the table was even
being seen. Apparently, the code doesn't get that far,
before it craps out.

The example code that I tried for the delete was:

Dim db As DAO.Database
Set db = CurrentDb
db.TableDefs.Delete "testtable" 'The error

occurs here

Yeah, if the table doesn't exist, you can't delete it.
There's a couple things you could try, the easiest being
just ignore any errors

On Error Resume Next
db.TableDefs.Delete "testtable"
On Error Goto 0 ' Or other error handler

I tried this for the create table: (the code ran, but

nothing happened) I tried this both with and without the
DAO.)

Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Dim idx As DAO.Index

Set db = CurrentDb
' Create a new TableDef object.
Set td = db.CreateTableDef("temptable")

Set fld = td.CreateField("test1", DB_INTEGER, 5)
td.Fields.Append fld

Set fld = td.CreateField("test2", DB_TEXT, 5)
td.Fields.Append fld

'set primary key
Set idx = td.createindex("PrimaryKey")
Set fld = idx.CreateField("indexfield")

You have to reference an existing field to add an index.
Set fld = idx.CreateField("test1")

idx.primary = True
idx.unique = True
idx.Fields.Append fld
'add index to the indexes collection
td.indexes.Append idx

One last line. You created the table, but didn't add it
to the database tabledefs collection. It seems redudent,
but you still have to add:

db.TableDefs.Append td



As with most programming tasks, there's more than one way:

'Drop the table:
CurrentDB.Execute "Drop Table TempTable"
'Create it:
Currentdb.Execute "Create Table TempTable (Test1 Counter
Primary Key, Test2 Char(25), test3 integer)"


And off you run!


Chris

 

[Tim Ferguson]

My problem is that I can't seem to create a table using VBA (even
though I followed an example program)

Best advice is, just don't do that. Once upon a time, DAO methods for
creating tables were all we had, but since the SQL DDL has been available I
would not touch it for the world. It is not only easier, it will port to
any other db engine you would like to use in the future too.

Try this:

strSQL = "CREATE TABLE TestTable " & _
"( Test1 INTEGER CONSTRAINT pk PRIMARY KEY, " & _
" Test2 VARCHAR(5) NULL " & _
")"

db.Execute strSQL, dbFailOnError

which does everything you wanted in two lines of code.

Hope that helps

Tim F