General discussion


Problem: Accses MDB/DAO on new SQLServer

By Marc in StPete ·
Hopefully this won't be too long or too confusing... I can provide more info if needed.
I'm looking for any hints as to where the problems might be...

1.0 Moved all databases to a new server. Everything works fine except for a few queries in two different MDBs using 2 difference SQL databases
1.a To test, I made copies of the MDBs and created new DSNs pointing to the new server
1.b Changed all MDBs to use the new DSNs
1.c Data connectivity is using DAO 3.6
1.d Front ends are MS Access 2000 MDBS
1.e Database is SQL Server 7.0 Standard

2.0 Notes:
2.a The queries are select queries
2.b Both queries are similar in that they linked to SQL views, selecting personnel data from a 3rd SQL database
2.c Neither of the original linked views are updateable
2.d sysdatabases dbid's are not the same
2.e UserIDs are the same, SUserIDs are not
2.f sp_configure shows db settings are the same
2.g The databases are identical to original ones
2.h Test: modified the query to work on SQL Server directly - it worked fine
2.i Test: rewrote the query in Access (closer to what I think it should have been anyway) - it worked fine
2.j Test: deleted the linked view and when I relinked it into Access, I selected a key field. After doing this the original queries worked. This was only a test - I don't want to do this, as the views should not be updateable.

3.0 Questions
3.a Should I worry about and try to solve this?
3.b Or should I simply redo the queriesand continue, knowing there were some problems with the move?

3.c As a side note - Eventually I will rewrite all the queries and move them to SQL Server.
But should this influence my decision as to whether the move was successful?

Thanks for any help/hints


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Related Discussions

Related Forums