CREATE TABLE with a DEFAULT for Microsoft Access

I'm missing SQL Server already. :(

This doesn't work with a Microsoft Access database. The DEFAULT is causing a syntax exception. Trying to find any help with Google has prooved very frustrating and given me no leads, so do any of you know how it is done in a CREATE TABLE statement? (i.e. not seperately).

CREATE TABLE [MyTable] (
[MyField] VARCHAR(50) DEFAULT ""
)

Thanks for reading,

- David

(btw, I posted this in the general SQL forum as there didn't seem to be one for non-application-type MS Access questions. Hope that was right.)When you use the Table Design within Access there is Default Value property for a column.
By the way there is a Microsoft Access section with dbforums.|||I know about that, I want to set it using an SQL statement though. I am creating the database tables through script not using Access itself.

I mentioned why I didn't use the Microsoft Access forum in my edit. I looked at the messages that were on the first few pages and they seemed to all be application-orientated.

Thanks for your reply,

- David|||Ok, but it is just a suggestion to maybe have your question duplicated in the MS Access (you never know who might be popping in there to view stuff).

Also, have you looked at the Access documentation there is a section about Jet SQL Reference (not sure if that is what you need to reference)...although it looks as though there isn't a mention of DEFAULT. I agree with you when you 'downgrade' from a DB engine that has everything to something that lacks, it is frustrating.

Good luck....|||CREATE TABLE [MyTable] (
MyField Text(50) DEFAULT Hello World,
MyID Integer NOT NULL DEFAULT 1
)

Sorry for pulling a Hello world stuff on ya but that should work.|||I tried running that SQL in MS Access itself and got the same error I have been seeing with other attempts:

---------------
Microsoft Access
---------------
Syntax error in CREATE TABLE statement.
---------------
OK Help
---------------

It then selects the CREATE keyword in the SQL window.

I am using Access 2002/XP for this, should I be using something else?|||Just an observation, but MS-Access is a client side program. By default, it ships with the Microsoft-Jet database engine. If you have MS-Access 2002, you have MSDE on the CD, which is a slightly scaled down version of MS-SQL.

It might be worthwhile for you to install MSDE and use that as your database engine. It would put you on much more familiar ground!

-PatP|||Thanks Pat, but it's not for that sort of use. The product gets installed on web servers that don't have SQL Server or MSDE available to them. (if it does, it would use them anyway). :(

Create Schema

I want to create a schema within the database, and then tables within the schema.
I am able to create the database and the tables but am missing the schema creation part.
Please help.
Thanks in anticipation.I think what you are talking about is a diagram. If you use Enterprise Manager, just drill down on the database, and you will see "Diagrams" listed. Right click on it, and choose 'New Diagram'. The wizard will help you with the rest.