472,146 Members | 1,389 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,146 software developers and data experts.

Access 2000 & MSSQL 2000 Problems

Hi everybody,

I have recently been doing a conversion for my boss for a access 2000 database (both front and backend) to a MSSQL backend.

I finished the conversion today (as quite a lot of the code / queries ran slow due to access running the queries locally rather than on the server). And tested it on my and my boss's machine with no problems so he gave the go ahead to update everybody to our new mssql 2000 backend with the modified frontend.

This is when the problems started; We had two different sets of forms for accessing one of our databases systems - the log system, one is the original dynaset based form, and the other is a newer set which uses snapshot views and preforms updates via queries. Nobody uses the old dyanset system apart from my boss and one of the administrative team as they have things on that window which they need to see. About 30 minutes into the release of the new database the system frooze up on my bosses computer and nobody could create a new log (the server was timing out). I assumed this had something to do with the old dynaset's creating locks on the table.

I offlined the database and kicked everybody out of the front end, turned it on again and tied again, this time banning everybody from the dynaset system. Within 10 minutes another computer frooze up, again with a timeout on the insert query. I discovered that after you had added a new log to the database it would timeout on all subquent additions (something it hadn't done during testing) . Further investigatiion showed it was the snapshot list window causing the error, so coded the add log window to close the list before preforming the insert query and then reopen it afterwards. This allowed my machine to make multible additions without flaw. So I released a new client to everybody. 15 minutes later it was timing out again, but this time there you could not even make one new record.

I checked for locks on the table though the server management table and couldn't find any for the Log table. I have restarted the SQL server box and with no avil. So I reverted our backend to the access mdb file and told people to use the old client.

I am at a complete lose to why this is happening, if anybody has had any expericences like this or knows the cause please tell me.

Some information on the database in question.

It was made as an access 2 database all intergrated into one file, then it was seperated into two files (frontend and backend). Upgraded to Access 97, then to 2000 before this final update to MSSQL 2000.

The log system has two main tables. The first is the log title / info table which links (one to many) to a log entry table. This problem only occurs on the main log table and does not appear to be reoccurring anywhere else within the database. The main log table has just under 18,000 rows in it.

Thank you in advance for any help,

Aug 20 '07 #1
4 2159
14,534 Expert Mod 8TB
Hi Dom

I'm not sure I'm following you.

You have moved all tables to MSSQL and added full UPDATE, DELETE, etc. privilages on the tables. You are happy with the constraints etc. on these tables.

Are you still using Access as a frontend? You speak of snapshot forms but I'm not sure what you are talking about.

Aug 21 '07 #2
By snapshot forms I mean a bound form which's data is grabed via a snapshot query instead of a dynaset
Aug 22 '07 #3
14,534 Expert Mod 8TB
By snapshot forms I mean a bound form which's data is grabed via a snapshot query instead of a dynaset
OK now I understand. Your problem is that snapshot queries copy the whole recordset into memory and hold it there whereas dynaset queries only copy the current record into memory.

This is not a problem with small data sets but with multiple users it will create a heavy memory load. Look at switching to dynaset queries. You can lock the forms to editing instead.
Aug 22 '07 #4
Sorry for the bump but I've been off work for a while.

Does a list form which displays all the logs within the system (which are not marked as finished) be good to place on dynaset then?

The way the form works is it has a query which retrieves all the information required and then uses a filter to only display what is desired at that point.
Sep 17 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Gary | last post: by
4 posts views Thread by Fabian von Romberg | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.