Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Posts
    4

    Question Unanswered: 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).

    Code:
    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.)
    Last edited by dwulff; 06-22-04 at 16:21.

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    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.

  3. #3
    Join Date
    Jun 2004
    Posts
    4
    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

  4. #4
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    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.....

  5. #5
    Join Date
    Jul 2004
    Posts
    1

    You are Close

    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.

  6. #6
    Join Date
    Jun 2004
    Posts
    4
    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?

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  8. #8
    Join Date
    Jun 2004
    Posts
    4
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •