Urgent: Adding a Date Field to a Table using VBA

[Richard Krupa]

Hey Guys,

Im trying to add a date field to an existing table using VBA but its not
working. I have it working for boolean, currency, text etc.. just not date.

strDB = "C:\testdatabase.mdb"
Set dbs = DBEngine.Workspaces(0).OpenDatabase(strDB)
Set tdf = dbs.TableDefs(TestTbl)
Set fld = tdf.CreateField(TestDate, dbDate)
fld.DefaultValue = Now()
tdf.Fields.Append fld
Set prp = fld.CreateProperty("Format", dbText, "General Date")
fld.Properties.Append prp
Set prp = fld.CreateProperty("Description", dbText, "testing date
add")
fld.Properties.Append prp
Set dbs = Nothing

Any help would be greatly appreciated!

 

[Tony Toews]

Im trying to add a date field to an existing table using VBA but its not
working. I have it working for boolean, currency, text etc.. just not date.

Incomplete code. I assume the following are the Dims.

Dim strDB As String, dbs As Database, tdf As TableDef
Dim fld As Field, prp As Property

strDB = "C:\testdatabase.mdb"
Set dbs = DBEngine.Workspaces(0).OpenDatabase(strDB)
Set tdf = dbs.TableDefs(TestTbl)

What is the value of TestTbl at this point?

Set fld = tdf.CreateField(TestDate, dbDate)

You want quotes around the TestDate string otherwise it's looking for
a variable called TestDate in your code..

fld.DefaultValue = Now()

You want quotes around the Now().

So what are the error messages? If any?

Do you have "Option explicit" set at the top of all the modules? Is
"Require Variable Declaration" checked in VBA >> Tools >> Options?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Richard Krupa]

Hi Tony,

I have it working now, yes the variables were dims etc... thanks for your
help!

Regards,
Richard