jc31415
asked on
Problem with CREATE TABLE
In a form I create a table. The code lines follow:
dbs.Execute "CREATE TABLE " & TableName & " (TCName TEXT CONSTRAINT c PRIMARY KEY,vendor TEXT);"
dbs.TableDefs.Refresh
The table is created correctly and I fill it with data. Then I try to access a record with the following code:
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(TableNam
rst.Index = "PrimaryKey"
I get an error: "Primary Key isn't an index in this table.
When I open the table and look at the definition for TCName it IS a primary key. If I turn the 'Key' off and then back on and rerun the form, everything is ok.
What is going on?
dbs.Execute "CREATE TABLE " & TableName & " (TCName TEXT CONSTRAINT c PRIMARY KEY,vendor TEXT);"
dbs.TableDefs.Refresh
The table is created correctly and I fill it with data. Then I try to access a record with the following code:
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(TableNam
rst.Index = "PrimaryKey"
I get an error: "Primary Key isn't an index in this table.
When I open the table and look at the definition for TCName it IS a primary key. If I turn the 'Key' off and then back on and rerun the form, everything is ok.
What is going on?
ASKER
Edited text of question.
You're using CONSTRAINT c PRIMARY KEY which is creating your primary key, but naming it 'c'.
Try:
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(TableNam
rst.Index = "c"
Or try changing your create table to this:
dbs.Execute "CREATE TABLE " & TableName & " (TCName TEXT CONSTRAINT [Primary Key] PRIMARY KEY,vendor TEXT);"
Try:
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(TableNam
rst.Index = "c"
Or try changing your create table to this:
dbs.Execute "CREATE TABLE " & TableName & " (TCName TEXT CONSTRAINT [Primary Key] PRIMARY KEY,vendor TEXT);"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It is my understanding that if you don't create a primary key, the first field of the table defaults as the primary key.
ASKER
Thanks! In the description of the CONSTRAINT clause I didn't see anything about the meaning or use of the name field. It seemed to be completely arbitrary.
ASKER