Imported XLS requires PrimaryKey in visual basic macro

[IAN BURDON]

Hi:- I am having a small problem assigning a primary key to a imported XLS
file which has no key. The import was done via tranferspreadsheet in visual
basic .

Does anybody have an example of a VB macro that takes a imported file and
adds the primary key to a current named column or adds a new column /index
containing a primary key.

The microsoft example using ADOX is over complicated and as clear as mud.
There must be a better way.

 

[Brendan Reynolds]

You're right, there's a much simpler and easier way. You can do it with SQL
....

Dim strSQL As String
strSQL = "CREATE INDEX PrimaryKey ON tblTest (TestID) WITH PRIMARY"
CurrentProject.Connection.Execute strSQL

.... or if you prefer ...

CurentDb.Execute strSQL

Where 'PrimaryKey' is the name you want to give to your index, 'tblTest' is
the name of the table and 'TestID' is the name of the field (or list of
field names separated by commas).

See the 'CREATE INDEX' topic in the help files for more information.

 

[IAN BURDON]

Brendan,
Many thanks I will give it a try. I wonder why MS make thing
hard to use. A primary key should have been a flag on transferspreadsheet or
any import.

Regards,
Ian

 

[IAN BURDON]

A little more help if you would be so kind.
The table imports OK but the initial record is null and there are repeated
records in the table containing null items.

Dim strSQL As String
strSQL = "CREATE INDEX PrimaryKey ON tblTest (TestID) WITH PRIMARY"
CurrentProject.Connection.Execute strSQL

Does not like null records in the selected table when adding a primary key.

Is there a way to add an extra field to a imported table and make that the
primary key. i.e. auto fill the the new field with the primary index. I could
then strip out the null records in the imported data.

Or do you have another get out of jail card.??

Many Thanks,
Ian

 

[Douglas J. Steele]

Import the data without setting a primary key, then add an AutoNumber field
to it.

I haven't experimented with doing this through DDL. You might be able to
use:

strSQL = "ALTER TABLE tblTest " & _
"CREATE FIELD Id COUNTER CONSTRAINT " & _
"PrimaryKey PRIMARY KEY"

or you might have to do it in two separate steps.