CREATE TABLE...

[Ben]

Hi all,

Yesterday I posted a related question regarding join conditions. What I
would like to ask is how can I create an Access Table with a decimal
field as Michel Walsh suggested.

MGFoster suggested turning the Access database into ANSI-92 compatible
route using Tools->Optinns->Tables/Queries->SQL Server Compatible
Syntax, this database.

My question is: I can currently create a new table by clicking:
Table->New, and then select any field to be decimal, without turning on
the ANSI-92 feature on. Isn't there a way to just write a CREATE
TABLE... query to make this happen without turning ANSI-92 on? I worry
my existing queries might not work if I do. If you do, can you share
with me the exact syntax on the CREATE TABLE to create a decimal field?
I tried a few different ways and it just doesn't seem to work.

Thanks in advance,

Ben

 

[Michel Walsh]

CurrentProject.Connection.Execute "CREATE TABLE withDecimal (f1
DECIMAL)"

to create the table with the default decimal type. You can create it with a
scale, as usual, or bring modification too:


CurrentProject.Connection.Execute"ALTER TABLE withDecimal ALTER COLUMN
f1 DECIMAL(10, 2)"



You can try/test the previous lines of code quite easily by pasting them
into the Debug (Immediate) Window. No special setting required.



Note that even if that code uses ADO (CurrentProject), once the table is
created, you can still use DAO (CurrentDb) to access it further on.





Vanderghast, Access MVP

 

[Ben]

Michel,

Is there any way to just do it using query, ie, just CREATE TABLE... instead of through code?  I keep trying CREATE TABLE  and it just wouldn't work.  BTW, I am using Access 2003 sp3.

Thanks for any assistance you can provide.

Ben



On 2/18/2009 11:06 AM, Michel Walsh wrote:

CurrentProject.Connection.Execute "CREATE TABLE withDecimal (f1 DECIMAL)" to create the table with the default decimal type. You can create it with a scale, as usual, or bring modification too: CurrentProject.Connection.Execute"ALTER TABLE withDecimal ALTER COLUMN f1 DECIMAL(10, 2)" You can try/test the previous lines of code quite easily by pasting them into the Debug (Immediate) Window. No special setting required. Note that even if that code uses ADO (CurrentProject), once the table is created, you can still use DAO (CurrentDb) to access it further on. Vanderghast, Access MVP "Ben" <[email protected]> wrote in message news:[email protected]...



Hi all, Yesterday I posted a related question regarding join conditions. What I would like to ask is how can I create an Access Table with a decimal field as Michel Walsh suggested. MGFoster suggested turning the Access database into ANSI-92 compatible route using Tools->Optinns->Tables/Queries->SQL Server Compatible Syntax, this database. My question is: I can currently create a new table by clicking: Table->New, and then select any field to be decimal, without turning on the ANSI-92 feature on. Isn't there a way to just write a CREATE TABLE... query to make this happen without turning ANSI-92 on? I worry my existing queries might not work if I do. If you do, can you share with me the exact syntax on the CREATE TABLE to create a decimal field? I tried a few different ways and it just doesn't seem to work. Thanks in advance, Ben

 

[Michel Walsh]

By default (I mean, without changing setting) the Access Query designer uses
DAO instead of ADO and thus, has the same problem than DAO on some (most)
Jet 4 extensions.

As example,

CurrentDb.Execute "ALTER TABLE withDecimalOther (f1 DECIMAL)"

in the Immediate Debug Window returns an error and that is why I used
CurrentProject.Connection.Execute instead.



Vanderghast, Access MVP