VBA Schema Changes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andy - 13 Mar 2007 13:42 GMT

Just need some help please with the syntax to make a Schema change in Access
from VBA.

For instance (one that works) :-

Set tbd = dbs.TableDefs("tblAL")
tbd.Fields("AssM").AllowZeroLength = True

This sets property of field AssM to allow zero length.

I need something like :-

Set tbd = dbs.TableDefs("tblAL")
tbd.Fields("Booked").Format = ShortDate

basically, field Booked is a DATETIME field and I would like to set it to a
short date(dd/mm/yy - 97) or (dd/mm/yyyy - 2k onwards).

Can you help with syntax?

Thanks.

Reply to this Message

strive4peace - 13 Mar 2007 14:19 GMT

Hi Andy,

I think you are just missing the text delimiter

try

tbd.Fields("Booked").Format = "ShortDate"

when you dimension tdb, make sure to include the DAO library as part of
the reference

dim tbd as DAO.tabledef

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

> Just need some help please with the syntax to make a Schema change in Access
> from VBA.
[quoted text clipped - 17 lines]
>
> Thanks.

Reply to this Message

Andy - 13 Mar 2007 17:03 GMT

Thanks for this - unfortunately, not as easy as we would have wished.

The thing is, there is no Format attribute to tbd.fields so it will not
compile. There are Properties or Type attributes but these also don't seem to
work.

Any further ideas?

Thanks.
------------

> Hi Andy,
>
[quoted text clipped - 40 lines]
> >
> > Thanks.

Reply to this Message

strive4peace - 15 Mar 2007 02:36 GMT

Hi Andy,

did you do this at the top of your program?

dim tbd as DAO.tabledef

also, do you have a reference to a Microsoft DAO library?

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

> Thanks for this - unfortunately, not as easy as we would have wished.
>
[quoted text clipped - 51 lines]
>>>
>>> Thanks.

Reply to this Message