ODBC Connection using the SQL Server Driver

[Guest]

I make a connection to a SQL Database (this particular one is on a MSDE
desktop version) using the ODBC Driver in Acess 2003 (11.6566.6568) SP2

On a particular table the Primary Key on the Item table is not defined
correctly on the Linked Item table. In design view In Access it shows the
primary key being the DBTIMESTAMP. This is incorret. The primary is suppose
to be the ID Autonumber Column.
The primary key is defined correctly on the table in the SQL database as
the ID field

This prevents any Update query from running. No error is displayed. The
Query just eventyallty times out.

Not all tables have the problem.

It is a large table with ~ 80 fields.

Does anyone know what the problem is or a way to set the primary on a
lionked table other than making the table a local table?

 

[Duane Hookom]

If the "primary key is defined correctly on the table in the SQL database"
then all you should have to do is re-link the table to update Access. You
can use a datadefinition query in Access to set primary or unique indexes.

 

[Guest]

The table in Question is a Table called "Item" in the SQL database. In my
access data base this table will only reside as a Linked Table. I can not
change the primary key or assign a primary key on a linked table.

When I create the data source as a odbc some tables allow me to set an
identifier while other tables "Item " for example do not allow the option or
the option is not displayed in the process.

Can you run a datadefinition query on a linked table?

Why does the primary key on some tables show up on the linked table and not
show uo on the linked "item" table.

Thanks


My goal is to update the SQL database table using Access as a Frontend with
data from an external source imported into access.

 

[Duane Hookom]

You can create an Access query as a datadefinition query with SQL like:

CREATE UNIQUE INDEX PrimaryKey
ON ITEM (ID)

Then go back to the design view of the linked table to see if it worked.