 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Multiuser / Networking / November 2003MSysObjects column CONNECT - reconnects despite DSN
In a production environment we encountered the following with Access 2000... Typically when an Access application is developed that uses linked tables (SQL Server 2000) we connect to a development server via a DSN. When the MDB is ready fro prod, the DSN is repointed to the production server. What happend yesterday is the Dev server webt down while i was looking at a production MDB. It returned an error: ODBC_Call failed. ODBC SQL Server Driver] Communication link failure (#0). Subsequent attempts to open a linked table resulted in this error: CONNECTION FAILED: SQL SATE '08001' SQL SERVER ERROR 17 ... SQL SERVER DOES NOT EXIST OR ACCESS DENIED. Attempting to use the Database utilities\link manager returned the second error, but we were able to use Get Expternal Data\Link tables to reimport the links. This worked for a while (until the dev server went down again??). Relinking a single table seemed to allow all tables to work again. What we found was that the MSysObjects has a column called CONNECT which saves the connection information when the link was originally established. Even though the linked data appeared to be proberly coming from the production SQL db via the DSN, it seems that something stilltracked the connection to the original dev DB. As soon as dev went down, we'd get those errors. The scary part was, once the dev environment came back up, the connections appeared to now be established with the original SQL db and we were retrieving test data even though the DSN was pointing to production. The solution was to delete all the linked tables and reimport them all when the DSN was pointing to prod thereby updating the MsysObjects table with the correct conection info. I am not sure what my question is, but maybe i am looking for conformation more than anything else... Rob What is it that caused the access db to detect that the development environment crashed? In other words, what event triggered this error messge? ODBC_Call failed. ODBC SQL Server Driver] Communication link failure (#0). I am confused as to what mechanism was running, even when the db was correctly pulling data from the prod SQL Server, that detected the "non-existant" connection to the original development db was broken? What we had thought was production data - wasn't. What i find is that we had linked tables in two different ways. One set of view were brought in via a DSN that specified the development server (used the full name IMSQLDV01\SYSTEST). When the linked tables are brought in using a DSN configured like this, Access 2000 does not save ADDRESS (servername) or the port number. The data linked to in this manner was what the clients used daily. The second method used to create the DSN was specifying the ALIAS to the SQL Server (SYSTEST). When links were established in this way, Access 2000 wrote to the "CONNECT" field in MSysObjects specifying the server and port: Address=IMSQLDV01,4704 When created in this manner, Access seems to ignore the DSN when it was later changed to point to production, and these links continued to point to dev. Luckily, we were refreshing our dev DB daily so they were actually seeing prod data. Sound plausible?? Rob > In a production environment we > encountered the following with [quoted text clipped - 7 lines] > When the MDB is ready fro prod, the > DSN is repointed to the production server. On client-server projects I have worked, there was a DSN defined for the development database and another DSN for the production database. When a development version was to be promoted to production, we deleted the TableDefs linked to the development DSN and recreated TableDefs linked to the production DSN. There was always a next release in development running against the development database, in parallel to a production version running against the production database. From your description, I take that you develop, convert to production, and no longer have a development database for testing, fixing bugs, or adding enhancements? In any case, I never experienced any strangeness such as you report here. Larry Linson Microsoft Access MVP Create a DSN pointing to a dev database. Use an alias for the server name. (In our environment, unit test and system test are both on the same server, so the true server name is SQLDEV01\UTEST or SQLDEV01\STEST, and we have aliases set up as UTEST or STEST) Link a table using that new DSN. Change the DSN to point to prod and import a table. The table imported while the DSN pointed to dev shows dev data even with the DSN Now pointed to prod. For a more interesting twist, Import the same table in prod as you did in dev so you have tblA and tblA1. If you open that table first, it will return prod data. Close the prod table and open the "dev" table. When You should see dev data. Now, close the dev table and open the prod table.... What i saw was dev data in both tables! It appears that once the dev table is opened, it forces both tables to ignore the DSN and use the MSysObjects entry. Again, if you use the actual server name, the problem does not occur as the MSysObjects table does not save the server/port info. Have fun! Rob My point was that we never changed the DSN to point to a different server database; we had separate DSNs for the dev and prod databases, and we had no similar problems. I think that might avoid the problem you have encountered. Larry Linson Microsoft Access MVP > Create a DSN pointing to a dev database. > [quoted text clipped - 27 lines] > Have fun! > Rob > prod table and open the "dev" table. When You should see > dev data. Now, close the dev table and open the prod > table.... What i saw was dev data in both tables! It > appears that once the dev table is opened, it forces both > tables to ignore the DSN and use the MSysObjects entry. That part is more or less as expected. Access caches connections. If it decides that two connect strings refer to the same connection, it will use the cached connection. It also stores connect information. If the stored connect information indicates that a cached connection can be used, it will be. The part about 'stores connect information' is not well understood, and I don't think that the differentiation between 'server alias' and 'true server name' has been reported previously. What has been reported previously is a differentiation between file DSN's and System DSN's. Are you using a file DSN? (david) 1) Access caches connections, and changing a DSN will NOT change a live connection (you would have to shut down and restart). 2) Access sometimes saves connection information as you describe. 3) I have never seen what you describe about using the full name of the server: that is new information. 4) Expect the behaviour to be different when using a file DSN or a system DSN. In fact, I predict that you were using a file DSN? 5) Avoid the problem by using a DSN'less connection (with all the DSN information stored in the database as you describe, and no external DSN at all). Repoint the database by updating the links, rather than by changing the DSN. Access is a database, and you can save all the connection information in the database. C++/VB/Java/Whatever developers only have an executable, so they have no where to store dynamic connection information: so the DSN was invented. We have a database, so no external data store is required. (david) > In a production environment we encountered the following > with Access 2000... [quoted text clipped - 48 lines] > > Rob |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.