Setting primary key in Vba

[meadt]

My code imports data and creates a table to store it in. I need to set up a relation and to do this i first need to set the primary key automatically in the code. I have been playing around with two different solutions but neither work.

The first:

DoCmd.RunSQL = CREATE INDEX ind ON [Data Conversion]([Primary Key]) With Primary

highlights INDEX and returns the error 'expected end of statement'



The Second:

Public Function Test()
Dim ind As DAO.Index
Dim tdf As DAO.TableDef
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

'Initialize
Set db = CurrentDb()

Set tdf = db.TableDefs("Data Conversion")
Set ind = tdf.CreateIndex("Primary Key")
With ind
.Fields.Append.CreateField ("Primary Key")
.Primary = True
End With
tdf.Indexes.Append (ind)
End Function

Highlights the .fields line with the error 'invalid arguement'

I'm pretty new still to vba so i'm guessing that i've missed something obvious but any help with either (or both!) solution would be greatly appreciated.

 

[DCrake]

Is there any reason why you need to do it in vba? Can you create the table and append the records to it? Where is the data coming from anyway?

David

 

[gemma-the-husky]

much better to do what DC says - that way you can validate the imported data before using, and not have to mess with managing keys at all

 

[meadt]

Hi DCrake,

It needs to be automated so sadly it does need to be VBA. Is it possible to do it on table creation? This is the code I use to create my table

DoCmd.RunSQL "CREATE TABLE [Data Conversion] ([Primary Key]INTEGER, [Reference Code]TEXT, [Terminal Address]TEXT, [T/R]TEXT, [Command]TEXT, [Words]TEXT, [Status Word]TEXT);"

The data comes from another table in the database, the data is manipulated and saved into the new table (you may remember that you helped me with this back in October)

 

[LPurvis]

>> CREATE INDEX ind ON [Data Conversion] ([Primary Key]) With Primary
Your first line implies that you have a field called "Primary Key". Is that right? :-s
I certainly wouldn't personally.

The syntax is
CREATE INDEX [IndexName] ON [TableName] ([FieldName]) With Primary

Of course it will fail if the data in that column isn't non-null and unique.
Your earlier code would be:
DoCmd.RunSQL "CREATE INDEX ind ON [Data Conversion] ([Primary Key]) With Primary"
(i.e. Not "=" as run from code it shouldn't have highlighted anything in the statement - merely informed you that the code failed)

However I'd agree - you should do this when you create the table.
IMO every table should have a primary key. (Unless you want generally poorer performance and the potential for non-updatability ;-).

(Let's imagine the field is named [Primary Key Field] instead).

DoCmd.RunSQL "CREATE TABLE [Data Conversion] ([Primary Key Field] INTEGER Primary Key, [Reference Code] TEXT, [Terminal Address] TEXT, [T/R] TEXT, [Command] TEXT, [Words] TEXT, [Status Word] TEXT)"

or something like

DoCmd.RunSQL "CREATE TABLE [Data Conversion] ([Primary Key Field] INTEGER CONSTRAINT PK PRIMARY KEY, [Reference Code] TEXT, [Terminal Address] TEXT, [T/R] TEXT, [Command] TEXT, [Words] TEXT, [Status Word] TEXT)"

General consensus prefers CurrentDb.Execute for running SQL statements, but there's nothing wrong with RunSQL (avoiding the prompts presumably).

And importing to a temporary location to "scrub" the data first is a good practice.
It doesn't need to affect the choice of including the PK still.


Cheers.

 

[meadt]

hi guys, thanks for the great advice, Ive done what you suggested and created the primary key when I create the table. Seems obvious now that you've pointed it out, but i'm still quite new to this and as a result i do seem to go the long way round to solve problems pretty often!

Ive also changed the names of my primary key fields as well!, Thanks LPurvis!