How to set column-options in "create table" with ODBC Driver ?

[Guest]

I am using a normal SQL script for creating a table :

CREATE TABLE sample (
id int NOT NULL,
value1 int NOT NULL DEFAULT 0,
value2 varchar(50) NULL,
PRIMARY KEY(id)
)

This statement works for MSSQL, but it doesn't work for msaccess :
"Error: [Microsoft][ODBC Microsoft Access Driver] Syntaxerror in
Fielddefinition."

I want to set two things :
- the column "value1" should have a default value (0), so you don't need it
in the insert-statement
- the column "value2" should also accept "empty strings" (like '')
i know you can set this option in msaccess, but can you do it here ?

Does anyone know how to do this right ?

Thanks for help,
Michael

 

[Guest]

Hi, Michael.

Does anyone know how to do this right ?

Yes. "Default" is not a valid option when creating a new field using DDL in
Jet 4.0. To use this syntax, you'll need to change the database settings.
First, make a back up of the database file.

Select the Tools -> Options... menu to open the Options dialog window.
Select the Tables/Queries tab and make sure that the "This database" option
is selected in the "SQL Server Compatible Syntax (ANSI 92)" section. (If you
are in Access 2002/2003 database format, you may set the option for all new
databases, too.) Select the OK button. You'll get a warning that all queries
will use this same mode -- so they may not work, but that's why you made a
back up in case you change your mind.

Run this DDL query. It should work without any errors. By default the text
field created will allow zero-length strings, so no changes need to be made
to your SQL code.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

I am using a normal SQL script for creating a table :

CREATE TABLE sample (
id int NOT NULL,
value1 int NOT NULL DEFAULT 0,
value2 varchar(50) NULL,
PRIMARY KEY(id)
)

This statement works for MSSQL, but it doesn't work for msaccess :
"Error: [Microsoft][ODBC Microsoft Access Driver] Syntaxerror in
Fielddefinition."

I want to set two things :
- the column "value1" should have a default value (0), so you don't need it
in the insert-statement
- the column "value2" should also accept "empty strings" (like '')
i know you can set this option in msaccess, but can you do it here ?

Does anyone know how to do this right ?

Thanks for help,
Michael

 

[Guest]

Thanks for help, but i'm sorry, i forgot to tell that i'm using access2000.
So, the setting "SQL Server Compatible Syntax (ANSI 92)" is only accessible
in access2002, right ? I believe access2000 uses sql-89 standard, is there an
option to change this ?
Also i have the problem that the empty character string is not allowed by
default. Can i change this setting in access2000 ?

Thanks
Michael

Hi, Michael.

Does anyone know how to do this right ?

Yes. "Default" is not a valid option when creating a new field using DDL in
Jet 4.0. To use this syntax, you'll need to change the database settings.
First, make a back up of the database file.

Select the Tools -> Options... menu to open the Options dialog window.
Select the Tables/Queries tab and make sure that the "This database" option
is selected in the "SQL Server Compatible Syntax (ANSI 92)" section. (If you
are in Access 2002/2003 database format, you may set the option for all new
databases, too.) Select the OK button. You'll get a warning that all queries
will use this same mode -- so they may not work, but that's why you made a
back up in case you change your mind.

Run this DDL query. It should work without any errors. By default the text
field created will allow zero-length strings, so no changes need to be made
to your SQL code.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

I am using a normal SQL script for creating a table :

CREATE TABLE sample (
id int NOT NULL,
value1 int NOT NULL DEFAULT 0,
value2 varchar(50) NULL,
PRIMARY KEY(id)
)

This statement works for MSSQL, but it doesn't work for msaccess :
"Error: [Microsoft][ODBC Microsoft Access Driver] Syntaxerror in
Fielddefinition."

I want to set two things :
- the column "value1" should have a default value (0), so you don't need it
in the insert-statement
- the column "value2" should also accept "empty strings" (like '')
i know you can set this option in msaccess, but can you do it here ?

Does anyone know how to do this right ?

Thanks for help,
Michael

 

[Guest]

Hi, Michael.

I believe access2000 uses sql-89 standard, is there an
option to change this ?
No.

Also i have the problem that the empty character string is not allowed by
default. Can i change this setting in access2000 ?

No.

You cannot use this DDL query "as is" in Access 2000. Either you must
upgrade to a newer version of Access to use this query, or you will have to
remove the DEFAULT 0 parameter and then manually make the changes you need in
the table Design View. Instead of SQL, you may also use the DAO or ADOX
library in VBA code to create this table with the fields and properties you
need.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

Thanks for help, but i'm sorry, i forgot to tell that i'm using access2000.
So, the setting "SQL Server Compatible Syntax (ANSI 92)" is only accessible
in access2002, right ? I believe access2000 uses sql-89 standard, is there an
option to change this ?
Also i have the problem that the empty character string is not allowed by
default. Can i change this setting in access2000 ?

Thanks
Michael

Hi, Michael.

Does anyone know how to do this right ?

Yes. "Default" is not a valid option when creating a new field using DDL in
Jet 4.0. To use this syntax, you'll need to change the database settings.
First, make a back up of the database file.

Select the Tools -> Options... menu to open the Options dialog window.
Select the Tables/Queries tab and make sure that the "This database" option
is selected in the "SQL Server Compatible Syntax (ANSI 92)" section. (If you
are in Access 2002/2003 database format, you may set the option for all new
databases, too.) Select the OK button. You'll get a warning that all queries
will use this same mode -- so they may not work, but that's why you made a
back up in case you change your mind.

Run this DDL query. It should work without any errors. By default the text
field created will allow zero-length strings, so no changes need to be made
to your SQL code.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

I am using a normal SQL script for creating a table :

CREATE TABLE sample (
id int NOT NULL,
value1 int NOT NULL DEFAULT 0,
value2 varchar(50) NULL,
PRIMARY KEY(id)
)

This statement works for MSSQL, but it doesn't work for msaccess :
"Error: [Microsoft][ODBC Microsoft Access Driver] Syntaxerror in
Fielddefinition."

I want to set two things :
- the column "value1" should have a default value (0), so you don't need it
in the insert-statement
- the column "value2" should also accept "empty strings" (like '')
i know you can set this option in msaccess, but can you do it here ?

Does anyone know how to do this right ?

Thanks for help,
Michael

 

[Guest]

Hi Gunny,

thanks a lot for your help. I'll do it manually.

Best Regards
Michael

Hi, Michael.

I believe access2000 uses sql-89 standard, is there an
option to change this ?
No.

Also i have the problem that the empty character string is not allowed by
default. Can i change this setting in access2000 ?

No.

You cannot use this DDL query "as is" in Access 2000. Either you must
upgrade to a newer version of Access to use this query, or you will have to
remove the DEFAULT 0 parameter and then manually make the changes you need in
the table Design View. Instead of SQL, you may also use the DAO or ADOX
library in VBA code to create this table with the fields and properties you
need.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

Thanks for help, but i'm sorry, i forgot to tell that i'm using access2000.
So, the setting "SQL Server Compatible Syntax (ANSI 92)" is only accessible
in access2002, right ? I believe access2000 uses sql-89 standard, is there an
option to change this ?
Also i have the problem that the empty character string is not allowed by
default. Can i change this setting in access2000 ?

Thanks
Michael

Hi, Michael.

Does anyone know how to do this right ?

Yes. "Default" is not a valid option when creating a new field using DDL in
Jet 4.0. To use this syntax, you'll need to change the database settings.
First, make a back up of the database file.

Select the Tools -> Options... menu to open the Options dialog window.
Select the Tables/Queries tab and make sure that the "This database" option
is selected in the "SQL Server Compatible Syntax (ANSI 92)" section. (If you
are in Access 2002/2003 database format, you may set the option for all new
databases, too.) Select the OK button. You'll get a warning that all queries
will use this same mode -- so they may not work, but that's why you made a
back up in case you change your mind.

Run this DDL query. It should work without any errors. By default the text
field created will allow zero-length strings, so no changes need to be made
to your SQL code.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.


:

I am using a normal SQL script for creating a table :

CREATE TABLE sample (
id int NOT NULL,
value1 int NOT NULL DEFAULT 0,
value2 varchar(50) NULL,
PRIMARY KEY(id)
)

This statement works for MSSQL, but it doesn't work for msaccess :
"Error: [Microsoft][ODBC Microsoft Access Driver] Syntaxerror in
Fielddefinition."

I want to set two things :
- the column "value1" should have a default value (0), so you don't need it
in the insert-statement
- the column "value2" should also accept "empty strings" (like '')
i know you can set this option in msaccess, but can you do it here ?

Does anyone know how to do this right ?

Thanks for help,
Michael