We have moved an SQL2000 database to a new server. We recreated the ODBC to point to the new location. The database has an MS Access front end that allows updating the database and uses a DBO account. Since moving the database to the new server the access database can update some tables but not others. We ran an update query through enterprise manager using the dbo account. It updated the database successfully. I have relinked the tables to the database at its new location. The problem is not in the Access forms, because I can't change the data by directly opening the tables. Any ideas?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Can't update to an SQL 2000 db that has been moved.
- Thread starter MrBillSC
- Start date
Do your SQL tables have primary keys that Access can identify?
Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
- Thread starter
- #3
When I open the linked tables in design mode, the keys are marked in the left column. This application worked fine until the database was moved to the new server. I don't know if the problem is with Access or security on the database . Our Db Administrator says security etc on the db is the same as on the old server. I couldn't say as that is foreign territory for me.
Any suggestions are appreciated.
MrBill
Any suggestions are appreciated.
MrBill
I expect it might be a security issue. When I migrate databases, I generally prefer to document then remove all security. After moving the database, I manually recreate the security. There are some issues with SIDs not matching on the new servers.
You should try run a pass-through query like:
UPDATE tblEmployees
SET FirstName = FirstName
WHERE EmployeeID = 111
Substitute your sql above to see if you get an error message.
Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
You should try run a pass-through query like:
UPDATE tblEmployees
SET FirstName = FirstName
WHERE EmployeeID = 111
Substitute your sql above to see if you get an error message.
Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
- Thread starter
- #5
Thank you Duane for your responses.
If anyone is interested in the solution, here is what we did.
We had to delete the tables from the access front end and re-add them (as linked tables). When we readded them, Access asked us to identify the primary key for 2 of the tables. Deleting and readding the tables solved the problem.
MrBill
If anyone is interested in the solution, here is what we did.
We had to delete the tables from the access front end and re-add them (as linked tables). When we readded them, Access asked us to identify the primary key for 2 of the tables. Deleting and readding the tables solved the problem.
MrBill
- Status
Similar threads
- Locked
- Question
- Locked
- Question