Link access to sql server

Question

Hi... I did a project by access and worked properly. After that I transfered and redesign my main tables in sql server with primary keys and linked my access to sql server propely. But now the delete and append queries dont work. when I see the linked table in access file the primary keys are of type number not auto number and i think this is the problem. How can I solve this. Thank you

Answer 1

I would take a quick look at the data type you used in the SQL server table(s) that are having difficulty. In most cases, you want to use type "int" for the primary key column. So, while in Access, we can set a autonumber column as primary key, in SQL server, as George above pointed out?

You want

The column type = int

The column to be set as primary key

The column to have identity set = yes.

So, for the most part, all 3 of the above are to be set if you want Access to "correctly" see the PK column and have things work much the same if you were not using SQL server, but local Access table(s).

So, take a re-look at the example screen(s) posted above - they should help resolve this.

Answer 2

If I fail to solve this problem, my project will be restricted on 2Gb access db linked tables and a lot of new issue :)

Hi ROHAM,

As a fast in-between possibility, you could use several/many different BE-databases.

Imb.

Answer 3

"...it didn't work..."? What DID happen?

I should have added, though, that you would have to relink the tables after designating the Primary Keys' Identity property in SQL Server.

Answer 4

Thanks a lot, I tryed this but didnt work.

I test everything on a smaller project and everything properly worked, so I think it must be related to volume of data is appending.

If I fail to solve this problem, my project will be restricted on 2Gb access db linked tables and a lot of new issue :)

Answer 5

That is possibly because you did not designate the Primary Key fields in the SQL Server tables as being identity fields. That is a separate property in SQL Server.