Access 2000 & MSSQL 2000 Multiuser Problems

[ThePhenix]

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,

Dom

[Guus2005]

Howmany users do you have?
Do you work stateless?
Do you still have an Access FE?

Try using unbound forms and work stateless.

[ThePhenix]

We have around 14 users.

All the code I have written is unbound forms, however my boss wrote 99% of the database, and almost all of his forms are bound. The new version of this part of the database is unbound and the old version of the this system was all bound forms.

Both have to be included with the front end. The rest of our database is also made up of bound forms.

And yes it is still a access front end and it is no an option to rewrite the whole front end into another language (no matter how much I would like to do that!).

[Rabbie]

Since it seems that everything works OK with your old Access back end and the problems only seem to occur with MSSQL backend I am drawn to the conclusion that the problem must be with the setup on MSSQL backend so I would concentrate my efforts on how that locks records etc.

The symptoms point to locks not being released properly. Can there also be a problem with the network linjk to the back-end is it able to cope with the volume of traffic.

[ThePhenix]

Some of the front end had to be modified slightly to get it to work with the new back end (complicated bits of code where converted into stored procedures) others just converted to pass through queries.

I believe you must be right with the locking issue, but I had checked the servers sql console and it didn't show any locks on the tables in question.

The code which creates a new log was orignally DAO executed SQL, I changed that to ADO and finnaly when that didn't work tried a stored procedure to create the record, the ADO SQL and Stored procedure both timeout and the DAO just completely frooze the client.

[Guus2005]

Locking is obvious the problem and working stateless should solve that problem. Hoewever this means that you have to unbound all your bound forms or at least the ones that give you problems. Using Access as the front end is no problem.

[ThePhenix]

I agree, but as I said the server is not reporting any locks and we have already tried disabling the bound forms and forcing everybody to use the unbound forms, the problem still occours.

Is there some server setting I am unawaire of which will disable locking or something?

Server is MSSQL Server 2000 Enterprise on Server 2003

[SQL_Hell]

Hi there

Firstly when you check for locking are you refreshing Enterprise Mangler? its completely crap at refreshing itself, right click on current activity and select refresh. you can also use 'exec sp_lock' in QA

Can you possibly give me a detailed explanation of the upsizing process?

Also can you post the SQL for your logging table? I want to check for primary keys and indexes

I think indexes are the problem here, but more on that later

[ThePhenix]

Hi,

Upsizing was done by placing the backend mdb file on the new sql server and then using the enterprise manager to import said mdb file. (As using the inbuilt access up sizer was giving errors). Then I spent around 2 months (on and off) changing default values / code within the frontend mdb to allow the two to work.

I also had to add a new column to the end of everytable called upsize_ts which was a timestamp to allow any records to be updated (as well as a primary key)

Then I truncated all the tables and uploaded the latest backend mdb to the sql server, imported the data (not structure) to get the latest data on the sql server, then I swapped all clients to the MSSQL client.

As requested here is the log table structure.

CREATE TABLE [dbo].[LOG FAULT] (
  [LOG ID] int IDENTITY(19472, 1) NOT NULL,
  [Client Id] int CONSTRAINT [DF_LOG FAULT_Client Id] DEFAULT 0 NULL,
  [CONTACT ID] int CONSTRAINT [DF_LOG FAULT_CONTACT ID] DEFAULT 0 NULL,
  [LOG DATE TIME] datetime CONSTRAINT [DF_LOG FAULT_LOG DATE TIME] DEFAULT getdate() NULL,
  [RECEIVED BY] nvarchar(25) COLLATE Latin1_General_CI_AS NULL,
  [ORDER/CONTRACT No] nvarchar(20) COLLATE Latin1_General_CI_AS NULL,
  [FAULT REPORTED] ntext COLLATE Latin1_General_CI_AS NULL,
  [RESOLVED] bit NULL,
  [INVOICED] bit NULL,
  [ON CONTRACT] bit NULL,
  [SHOP WORK] bit NULL,
  [SiteWork] bit NULL,
  [Due Date] datetime NULL,
  [Priority] int CONSTRAINT [DF_LOG FAULT_Priority] DEFAULT 4 NULL,
  [ResolvedDate] datetime NULL,
  [InvoicedDate] datetime NULL,
  [InvNo] int CONSTRAINT [DF_LOG FAULT_InvNo] DEFAULT 0 NULL,
  [EngAssesment] nvarchar(100) COLLATE Latin1_General_CI_AS NULL,
  [EngWho] int CONSTRAINT [DF_LOG FAULT_EngWho] DEFAULT 0 NULL,
  [CustomerInformedD] datetime NULL,
  [CustomerInformedBy] int CONSTRAINT [DF_LOG FAULT_CustomerInformedBy] DEFAULT 0 NULL,
  [LogFirst] int CONSTRAINT [DF_LOG FAULT_LogFirst] DEFAULT 0 NULL,
  [LogLast] int CONSTRAINT [DF_LOG FAULT_LogLast] DEFAULT 0 NULL,
  [SysDate] datetime CONSTRAINT [DF_LOG FAULT_SysDate] DEFAULT getdate() NULL,
  [WhereWork] nvarchar(1) COLLATE Latin1_General_CI_AS NULL,
  [TypeOfContract] int CONSTRAINT [DF_LOG FAULT_TypeOfContract] DEFAULT 0 NULL,
  [Status] int CONSTRAINT [DF_LOG FAULT_Status] DEFAULT 0 NULL,
  [DefaultLabourRate] nvarchar(20) COLLATE Latin1_General_CI_AS NULL,
  [ContractId] int CONSTRAINT [DF_LOG FAULT_ContractId] DEFAULT 0 NULL,
  [SalesOrdNo] int CONSTRAINT [DF_LOG FAULT_SalesOrdNo] DEFAULT 0 NULL,
  [upsize_ts] timestamp NULL
)
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO

[SQL_Hell]

Hi there

Is the Identity column [log id] the primary key? when you script tables there are options to select scripting of indexes and primary /forreign keys.

Are there any indexes on the table? could you either describe them or script them?

Can you run the following for me in query analyzer

DBCC SHOWCONTIG ([LOG FAULT])

[Pat Hartman]

You have done a lot of unnecessary work. Access works fine with bound forms against server-side databases. The area where you would need to make changes is in the forms. If the forms are bound to queries with no selection criteria or even worse, bound directly to tables, you would need to change the recordsources to be queries with criteria that limits the number of rows returned. Depending on the application, you may prefer to do this by creating "search" forms which provide the where argument for the OpenForm method or add search fields directly to the form. In this case you would remove the RecordSource once the form is completed and replace it after the criteria is supplied.

At my current client's site I had a similar problem with timeouts - they actually mascaraded as other errors but the bottom line was the database was somehow disconnecting from the server. However, this was when the BE was Jet and I converted it to SQL server in a single hour (my forms were already designed as client/server) and the disconnection problems dropped dramatically although we still see them.

Check with your network people and see if they can determine if the clients are dropping their connections to the server.