After Upsizing, cannot add records to some tables

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

Hi,

I converting my application to use SQL Server for stroring the data. My app
is split into a Data.MDB (which has been upsized) and an App.MDB.

I used the access XP upsizing record without reported error.

When using enterprise manager, I can add records, etc. Viewing the attached
tables in access, I cannot add records to some tables (other appear to work
fine). I do not get the new record row, it seems as thought the tables
cannot have records added. I cannot update records either.

I have explicitly given full permission in both SQL Server and Access.

Any ideas? I am obviously stuck here. .

thanks

e
 
Some more info/ Clarrification:

Using enterprise manager, I can change data, add rows, etc.

Only the linked access table is a problem.

Here is my connection string:
ODBC;DSN=MQIS;Description=SQL Server;APP=Microsoft Office XP;" & _

"WSID=LAPTOP;DATABASE=PCProData;Trusted_Connection=Yes;TABLE=dbo.Calendar"

Other tables, attached the same way, work fine. Not sure what makes one
table different than another.

Thanks for the help.

e
 
Probably not all of your tables have primary keys. In this case when you
link the tables Access asks for unique keys. You can either select such key
or click Cancel, but in later case you won't be able to update the linked
table.

HTH,
Alex.
 
Yes, that is the problem.

In access, that was not really an issue. I assume SQL Server requires a
primary key?

What is the suggested solution? Is there some sort of autonumber primary
key I can create?

Thanks for the help.

e
 
SQL Server does not require it, but Access does require it from the SQL
Server because of record identification technique it uses. Yes you can
create Autonumbers in SQL Server tables.
Open a table in Enterprise Manager and create a numeric field (smallint,
int, tinyint, all will work)
and set its Identity property to Yes. Make this field a primary key and then
relink your tables.

That should work.

Alex.
 
Back
Top