Update ODBC sql table with a query (1 Viewer)

[rosajen]

Hi everyone,

I have an access database that basically checks supplies in use and checks them out after they have expired. I have a table called All Supplies that holds the item's name, the date it started being used, its expiration date, and the date it actually stopped being used and how many times it was used. I have a query called NowInUse based on All Supplies that shows only the supplies that are still being used. I did this by setting the criteria in the query for the field Date Use Stopped to is Null. My problem is that I need this list of supplies in the NowInUse query to be inserted into a ODBC sql table called dbo_InUse that is already in the database. I've tried using an append query but I get the message:

ODBC--insert on a linked table 'dbo_InUse' failed.
[Microsoft[ODBC SQL Server Driver][SQL Server] The INSERT permission was denied on the object 'InUse', Database 'SupplyDatabase',schema 'dbo'.(#229)
The database tables and the sql table are not set to read only as far as I can tell , all of them have primary keys, and when I linked the table into the database I provided the password. I'm not sure what is going wrong. Can anyone help? I'd really appreciate it.

Thanks!!

 

[mdlueck]

Are you certain that InUse is actually a Table and not merely a View? A View can look like a table, but has a SELECT statement contained under the covers which prevent an INSERT. I am not sure what exact error message SQL Server returns when one attempts an INSERT into a View.

 

[Lightwave]

Another thing to check. On the primary key of the table ensure that identity is set to yes and make the increment something like 1.

This ensures that a new unique number is inserted into primary key value with value one more than previous when a new record is created.

 

[rosajen]

Hi!

Thanks for posting. I checked on both the primary key and the table/view part. It is actually a table and the primary key already had the increment set to one and had identity set to yes.

Thank you for responding I really appreciate it

 

[rosajen]

I also cross posted this on utteraccess it under the title Odbc Database Linked Table Cannot Update sorry I can't post the link

 

[rosajen]

I figured out the issue. I had to set the OBCD table to my machine data source and not my file data source. thanks for your help though!!

 

[Lightwave]

Rosajen just for future reference if you are trying to enter new information and the primary key doesn't have identity set to 1 you will get a similar message to your original message. Just something that is useful to know about.