Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    361

    Unanswered: Issues with make table query

    I am having a problem with a make table query that adds a primary key to a field. Here is the code that I have so far.

    Option Compare Database

    Private Sub Command0_Click()
    On Error GoTo Err_Command0_Click

    Dim stDocName As String

    stDocName = "Build New Table"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    DoCmd.RunSQL ("Alter table [tbl main] add primary key (LEASE_NO")

    Exit_Command0_Click:
    Exit Sub

    Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click

    End Sub

    When I press the button on the form, the query runs and builds the table but I get an error that says "Syntax error in CONSTRAINT clause". So, i'm lost. Could anyone help. I would be greatful. Thanks so much.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    DoCmd.RunSQL ("Alter table [tbl main] add primary key (LEASE_NO)")
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    The Alter Table statement is used when the table already exists. I presume from your post you are trying to create a table. Use the create table statement.

    DoCmd.RunSQL "CREATE TABLE TableName (PrimaryKeyFieldName AUTOINCREMENT CONSTRAINT C1 Primary Key, AnyOtherNameName TEXT(255))"

  4. #4
    Join Date
    Mar 2004
    Posts
    361
    Thanks r123456. That was the ticket.

Posting Permissions

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