?
Solved

Problem with CREATE TABLE

Posted on 2000-01-14
6
Medium Priority
?
186 Views
Last Modified: 2008-02-26
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(TableName)
 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?
0
Comment
Question by:jc31415
  • 3
  • 2
6 Comments
 

Author Comment

by:jc31415
ID: 2354988
Edited text of question.
0
 

Author Comment

by:jc31415
ID: 2354989
Edited text of question.
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 2355189
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(TableName)
 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);"


0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 25

Accepted Solution

by:
clockwatcher earned 400 total points
ID: 2355194
Whoops!  I didn't notice you didn't have a space in there.  Without the space:

 dbs.Execute "CREATE TABLE " & TableName & " (TCName TEXT CONSTRAINT PrimaryKey PRIMARY KEY,vendor TEXT);"
0
 
LVL 7

Expert Comment

by:JimMorgan
ID: 2355341
It is my understanding that if you don't create a primary key, the first field of the table defaults as the primary key.
0
 

Author Comment

by:jc31415
ID: 2355544
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.
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question