creating tables in VBA

[BSK]

I am trying to get this code to work. IT is supposed to create a
table when a button is clicked. This code is not working. When I
attempt it, access gives me the error "Runtime Error 3219: Invalid
Operation" This occurs at the line that says
'db.tabledefs.append(tbdf)' Any ideas?


Dim tbdf As DAO.TableDef
Dim db As DAO.Database
Dim fld As DAO.Field

Set db = CurrentDb
Set tbdf = db.CreateTableDef(strFilename)
tbdf.Fields.Append (tbdf.CreateField("Drawing"))

db.TableDefs.Append (tbdf)
'refresh the table collection
db.TableDefs.Refresh
'refresh the database window
RefreshDatabaseWindow
Set tbdf = Nothing

 

[Jason Lepack]

This works:

Public Sub create_table(strfilename As String)
Dim db As DAO.Database
Dim tbdf As DAO.TableDef

Set db = CurrentDb
Set tbdf = db.CreateTableDef(strfilename)

With tbdf
.Fields.Append .CreateField("drawing", dbText, 50)
End With

db.TableDefs.Append tbdf

db.TableDefs.Refresh
RefreshDatabaseWindow

Set tbdf = Nothing
Set db = Nothing
End Sub

Any questions, just post back.

Cheers,
Jason Lepack

 

[Douglas J. Steele]

Just to highlight the difference, putting parentheses around field names
when they're not required can have unexpected results.

The original code contained:

tbdf.Fields.Append (tbdf.CreateField("Drawing"))

db.TableDefs.Append (tbdf)

Both of those lines have parentheses that shouldn't be there: they should be

tbdf.Fields.Append tbdf.CreateField("Drawing")

db.TableDefs.Append tbdf

Parentheses mean several different things in VB and hence in VBA. They mean:

1) Evaluate a subexpression before the rest of the expression: Average =
(First + Last) / 2
2) Dereference the index of an array: Item = MyArray(Index)
3) Call a function or subroutine: Limit = UBound(MyArray)
4) Pass an argument which would normally be byref as byval: Result =
MyFunction(Arg1, (Arg2)) ' Arg1 is passed byref, arg2 is passed byval

In other words, db.TableDefs.Append (tbdf) no longer properly passes a
reference to the table that needs to be appended to the collection.