creating tables in VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BSK - 20 Jun 2007 14:29 GMT

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

Reply to this Message

Jason Lepack - 20 Jun 2007 14:43 GMT

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

> 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
[quoted text clipped - 16 lines]
> RefreshDatabaseWindow
> Set tbdf = Nothing

Reply to this Message

Douglas J. Steele - 20 Jun 2007 16:09 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> This works:
>
[quoted text clipped - 43 lines]
>> RefreshDatabaseWindow
>> Set tbdf = Nothing

Reply to this Message