Access Front End Changing backend SQL Servers

RoadrunnerII

Registered User.
Local time
Today, 08:35
Joined
Jun 24, 2008
Messages
49
This seems easy but I am having a problem Have not touched this in a few months
Have the front end MS Acccess DB using an odbc link to the backend MS SQL server SQL Server has both tables and views
Production works fine
I want to take a copy of the Front End and point it at a different backend MS SQL server which contains the same tables and views (Call this my DEV Server)
So within MS Access you use the utility Linked Table Manager and repoint all the tables and views to the new server by the ODBC Connection
Everything seems to go fine but I cannot update the tables afterward in the DEV Server
What did I miss in the process?

RRII
 
Do you recieve any kind of error message when you try and update?

Is it possible that your dev tables have lost their primary keys?
 
I know when I look at the views the keys are missing
The DBA for the SQL server di the transfer of the DB from one server to the other so that is possible
I will do some checking

RRII
 
As for an error I cannot add an lines to the Tables
within access it just grays out the add new line button
 
Ok, sometimes you get a bit of text come up at the bottom right hand side of the screen saying "recordset not updateable"
 
So the primary keys are all there on the tables
Now on the views they are not showing up if that makes sense
They do show up on the Production SQL Server with the same front end
Hmm
So if I run one of the Access Queries that links to the view on the DEV box I cannot upate any records ON production I can
Now looked at the relationships and the tables all still have their interrelations in tack
Hmm
 
Finally figured it out well sorta anyway
Something must have corrupted when I copied over the new copy of the front end
Just copied it over again Ran the Access Linked Table Manager tool and repointed to the DEV ODBC Connection and it works fine
Go Figure

Thanks for the help SQL Hell

RRII
 
hmm most strange, at least you got it fixed mate :)
 

Users who are viewing this thread

Back
Top Bottom