Whats Wrong With This Alter Table Statement?

[Guest]

DoCmd.runMacro "FRM-CR10RW"
ALTER TABLE DPS_FR_CR10RW CASEID INT ADD CONSTRAINT
(CASE_NUM_YR,CASE_NUM) PRIMARY KEY

Getting Compile error, expecting end of statement. I need to create a
multiple field primary key for table named DPS_FR_CR10RW. Table is being
created in execution of FRM-CR10RW macro.

 

[david epsom dot com dot au]

To run SQL you can put a RunSQL statement in your "FRM_CR10RW"
macro. If you do it that way, you probably also need to use
Jet (Access) SQL rather than ODBC SQL. Something like this:

ALTER TABLE DPS_FR_CR10RW add column CASEID long
CONSTRAINT dps_fr_cr10rw (CASE_NUM_YR,CASE_NUM) PRIMARY KEY


Alternatively, execute the sql against your default project
connection:

application.CurrentProject.Connection.execute _
"ALTER TABLE DPS_FR_CR10RW CASEID INT ADD " & _
"CONSTRAINT(CASE_NUM_YR,CASE_NUM) PRIMARY KEY"

(david)

 

[Van T. Dinh]

Unless I misunderstood your description, the ALTER TABLE statement is an SQL
String which VBA won't understands the way you wrote it in VBA code. You
need to write VBA code to "wrap around" the SQL String so that VBA knows to
pass the SQL String to JET database engine for execution.

IIRC, since your SQL String has a CONSTRAINT clause, you will need to use
ADO to execute it since DAO doesn't know about the CONSTRAINT syntax.

HTH
Van T. Dinh
MVP (Access)

 

[david epsom dot com dot au]

ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT
index] |
CONSTRAINT multifieldindex} |
DROP {COLUMN field I CONSTRAINT indexname} }

Jet 3.5

(david)

Unless I misunderstood your description, the ALTER TABLE statement is an
SQL
String which VBA won't understands the way you wrote it in VBA code. You
need to write VBA code to "wrap around" the SQL String so that VBA knows
to
pass the SQL String to JET database engine for execution.

IIRC, since your SQL String has a CONSTRAINT clause, you will need to use
ADO to execute it since DAO doesn't know about the CONSTRAINT syntax.

HTH
Van T. Dinh
MVP (Access)

 

[Van T. Dinh]

OK, some Constraints cannot be executed using the SQLView of the QBE or DAO,
for examples CHECK constraint.

I did put "IIRC" so I think I am excused for having recalled incorrectly
<g>. The one I thought of when I replied was DEFAULT clause.

 

[david epsom dot com dot au]

I only wanted that to be part of the record :~)

(david)

OK, some Constraints cannot be executed using the SQLView of the QBE or
DAO,
for examples CHECK constraint.

I did put "IIRC" so I think I am excused for having recalled incorrectly
<g>. The one I thought of when I replied was DEFAULT clause.

--
HTH
Van T. Dinh
MVP (Access)

ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT
index] |
CONSTRAINT multifieldindex} |
DROP {COLUMN field I CONSTRAINT indexname} }

Jet 3.5

(david)