[RESOLVED] VB6 with Access 97 for multiuser system

[blackbox]

Hi Everyone,
This is not a joke. I know we are in the year 2007 and what I am going to talk about is compeletly obsolete.

Here is my scenario.

My boss wants me to write a VB6 application that saves and retreives data from an Access 97 database for approx 6 - 7 users.

MY have managed to write it. OK....

But my problem is that I am having too much records and table locks (like error 3262 Table in use and error 3008) when I save data to a table.

This is the code I am using to insert a new record:

On Error GoTo Err_ErrorHandler
    
    Dim rs As dao.Recordset
        
    If theSerial = "" Then Exit Sub
        
    Set rs = db.OpenRecordset("Results", dbOpenDynaset, dbOptimistic)
    rs.AddNew
    rs("DateTime") = Now
    rs("Serial") = theSerial
    rs("ProductID") = prod.prodID
    rs("Passed") = passed
    rs("Comments") = comments
    rs("OperatorID") = MainWindow.GetUserId
    rs("Firmware") = BaseName(prod.firmware)
    rs.Update
    rs.Close
 
    
Exit_ErrorHandler:
    ' de-initialise our object variables
    Set rs = Nothing
    Exit Sub
    
Err_ErrorHandler:
    ' display error message and error number
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_ErrorHandler

Am I using the correct connection mothods (i.e DAO) for a multiuser system? Is there a better option?

Can please someone give me some hints and I am completely new to this topic?

Thanks in advance

[Hack]

Welcome to the forums.

First, DAO should not be used as that technology falls well within the definition of ancient. To connect to your database, you should be using ADO.

Second, MS Access has been known to have issues in a multi-user environment, but, for 6 or 7 people, it shouldn't be too much of a problem providing all of them aren't doing the same thing at the same time.

[blackbox]

May I ask you one last thing please?
Have you got a link to a code snippet where I can see how to implement ADO?

I know that this may seem funny, but I am learning to the fly with this project.

Thanks

[Hack]

How about an ADO Tutorial?

[RobDog888]

With Access 97 you may want to stay with DAO since it native to that version. The table lock is unnecessary because you are locking the entire table just to add a record. Dont do that. Either use a SQL statement that will return a blank recordset or .Execute the SQL statement of an INSERT INTO action type.

[lewmur]

In order to have record locking work right you have to add these two lines right after the recordset in opened

rs.movelast
rs.movefirst

Don't ask me why, but if you add those two lines your problem will be solved.

[RobDog888]

No, you still dont need to open a recordset to Add a new record. Recordsets are for retrieving data and not inserting it.

[lewmur]

No, you still dont need to open a recordset to Add a new record. Recordsets are for retrieving data and not inserting it.

Reread his original post. He is getting record lock errors. That occurs with '97 databases when you don't have the two lines I suggested because DAO locks a bunch of records instead of just the one a user is actually using. The code he posted is only the error handler and not the entire application.

[RobDog888]

Yes, and reread as the code is opening the entire table to add a record. You dont need to have a recordset to add a record like I previously pointed out.

[si_the_geek]

The locking is caused by using a recordset. As there are no records being returned, why open a recordset.

Instead of "fixing" something that is wrong anyway, why not simply use the correct method instead?

[lewmur]

The locking is caused by using a recordset. As there are no records being returned, why open a recordset.

Instead of "fixing" something that is wrong anyway, why not simply use the correct method instead?

Because he IS using a recordset and needs to to perform OTHER tasks. Not just the task in the code posted. Are you saying that there is NEVER a need to open a recordset?

[si_the_geek]

Of course I'm not... you need a recordset if you want to get data from the database.

In the code posted the recordset is opened, not read (only added to), and then closed.

By definition, the same recordset cannot be used anywhere else - so it is pointless to use it in this case.

[lewmur]

Of course I'm not... you need a recordset if you want to get data from the database.

In the code posted the recordset is opened, not read (only added to), and then closed.

By definition, the same recordset cannot be used anywhere else - so it is pointless to use it in this case.

The CODE POSTED is NOT what is causing his problem. It obviously isn't solving it either but the two lines I suggested, used in the right place, will solve it.

[GaryMazzone]

Yes the code posted is causing the error. If you are only going to add records to the table then there is no need to cause all the network traffic in bringing back all the records from the table just to say never mind all the data here I'm just going to add a new record to the table. The better solution is to create an Insert statement for the table and excute that on the connection object. Cleaner and quicker. If you look at the code that was posted the recordset was opened only to add the new record that is a waste of resources.

[lewmur]

Yes the code posted is causing the error. If you are only going to add records to the table then there is no need to cause all the network traffic in bringing back all the records from the table just to say never mind all the data here I'm just going to add a new record to the table. The better solution is to create an Insert statement for the table and excute that on the connection object. Cleaner and quicker. If you look at the code that was posted the recordset was opened only to add the new record that is a waste of resources.

Where in his post does it say that he is ONLY adding records? If he was only adding records he wouldn't be getting record locks. You can't lock a record that doesn't already exist. In order to UPDATE records, you want the record being updated locked but not a whole bunch of other records. And that is what happens if you don't use the two lines.

[RobDog888]

Because he IS using a recordset and needs to to perform OTHER tasks. Not just the task in the code posted. Are you saying that there is NEVER a need to open a recordset?

Ok, things need to calm down and no yelling in caps in your posts.

The procedure is self contained so there is no need to use a recordset since the procedure is only for adding a record, not retrieving or searching or anything else. Just executing an "insert into" sql statement is all that is needed here.

[lewmur]

Ok, things need to calm down and no yelling in caps in your posts.

The procedure is self contained so there is no need to use a recordset since the procedure is only for adding a record, not retrieving or searching or anything else. Just executing an "insert into" sql statement is all that is needed here.

If the procedure is the entire application, you would be correct. If, OTOH, it isn't, which I fully believe to be the case, then he needs the two lines. He stated in his post that there was also a need to "retrieve" records. There is no provision for that in the code posted.

[RobDog888]

This is the code I am using to insert a new record:

He doesnt need those two lines in this procedure which is what we all have been stating. Now if he has other issues in his app somewhere then that code should be posted too but not until after the changes are made to help reduce any conflicting errors during debugging.

If he needs to retrieve records in some other procedure then it more then likely looks the same as this procedure. So the openrecordset arguments wil need to be changed so they are optimized for just retrieving records and not opening the rs with as editable. This alone will prevent the lock on the table too. Reading records will not lock a table if opened correctly.

The rs.MoveLast and rs.MoveFirst are to initialize and fully populate the rs because of the DAO bug issue where it wont return a correct recordset count until that is done.

[randem]

If you want to use correct Multi-User code and solve some of your other issues look here

[blackbox]

Oh my God!!!!

Ok.. ok... I was the culprit. I was the one who started it.

Thanks a lot for your replies. I really appreciate it.

Basically, I think I managed to solve the problem by using a db.execute command and inserting the sql. Obviously, I could not have made it without the replies I got.

I am only having a slight problem. Now that I am unsing sql statement, I am getting errors when a user enters a single quote(') or double-quote(") in the comments field. The sql statement gets messed up big time.

Is that a smart way to fix such quotations so the sql statement does not get messed up.

Please do not fight!! OK!! keke..

[randem]

The best way is to eliminate it from the string before inserting

[RobDog888]

Also, doubling up on single quotes wil work too.

[si_the_geek]

See this FAQ article for how to do that 'automatically' in VB.