Create table DDL statement

[joyaccess1]

Hi,
I am writing ddl statement for creating tables in access, i would need to provide a default date for a date column, how can i do this in a ddl statement ?

In the table design window in the gui, i can do it by providing the default as "=format(now())" but it seems not to work in the ddl statement.

also, is it possible to generate the ddl sql statement for an existing table in the database, i would need to know how can this be done too.

Thanks for the help
Joy

 

[DJkarl]

Hi,
I am writing ddl statement for creating tables in access, i would need to provide a default date for a date column, how can i do this in a ddl statement ?

In the table design window in the gui, i can do it by providing the default as "=format(now())" but it seems not to work in the ddl statement.

also, is it possible to generate the ddl sql statement for an existing table in the database, i would need to know how can this be done too.

Thanks for the help
Joy

You could use this SQL to add a default value for your date column.

ALTER TABLE [YourTable] ALTER COLUMN [DateColumn] DATE DEFAULT Now()

However if you try to run this from the query editor it will generate an error on the DEFAULT statement, place this query into code and call it using

CurrentProject.Connection.Execute

 

[Moniker]

CurrentProject.Connection.Execute

is easier said as:

CurrentDb.Execute

As for DDL, it looks like you've figured out that the Access conventions will not work in DDL, as it's a standardized language, not Access-specific. It's the same reason you can't write a pass-through query using Access conventions. You have to conform to the language you want as it will not conform to you.

 

[DJkarl]

CurrentProject.Connection.Execute

is easier said as:

CurrentDb.Execute

As for DDL, it looks like you've figured out that the Access conventions will not work in DDL, as it's a standardized language, not Access-specific. It's the same reason you can't write a pass-through query using Access conventions. You have to conform to the language you want as it will not conform to you.

Actually those are two different things. If you try and run the above alter table statement using Currentdb.execute it will generate an error, if you use the CurrentProject.Connection.Execute method it will not generate an error and will successfully add the default value.

 

[Moniker]

I couldn't recreate the issue you described. Both currentDb.Execute and CurrentProject.Connection.Execute performed the exact same thing in a quick test from the immediate window.

We're getting off topic here, but I'm curious as to what the difference would be? CurrentDb refers to the entirety of the current DB (seems logical) while CurrentProject refers to all the objects collections in a DB (AllForms, All Modules, etc.). In this case, they should be identical.

 

[DJkarl]

I couldn't recreate the issue you described. Both currentDb.Execute and CurrentProject.Connection.Execute performed the exact same thing in a quick test from the immediate window.

We're getting off topic here, but I'm curious as to what the difference would be? CurrentDb refers to the entirety of the current DB (seems logical) while CurrentProject refers to all the objects collections in a DB (AllForms, All Modules, etc.). In this case, they should be identical.

A little off topic perhaps, but I've been led to believe that Currentdb uses DAO and CurrentPrject uses ADO, the ALTER TABLE statement in my database works using either method except when using the DEFAULT qualifier. When I execute an ALTER TABLE statement with the DEFAULT value specified using Currentdb I get a Run-time error 3293.

 

[Moniker]

Hmm, SP3 installed with the hotfix?

 

[DJkarl]

That could be the difference, only have SP2 installed at work.

 

[winshent]

Hi Guys

I'm trying to use DDL to add a DATE field as above with NOW() as the default value..

I am using the same syntax pretty much (Altering instead of Creating a table), but its not working..

The error I get is:

err.number = 3293
err.description = Syntax error in ALTER TABLE statement.

Here is the code.. Any ideas ?

CurrentDb.Execute "ALTER TABLE _tbl ADD COLUMN RecordCreatedDate DATE DEFAULT NOW()"