Will upsizing access db to SQL server stop errors?

[jondow]

Hi All,

I have a web based system running on an Access database. The system is a questionnaire which collects data and adds it to the db using while loops, as such the recordset is open for a period as the updates are performed. I'm getting the following errors:
Update tblAnswer set ans_yn_check=Y where emp_id=415 and qu_id=236
for multiple qu_id's

Then it says:

microsoft oledbprovider for odbc drivers error 80004005
Microsoft driver could not update currently locked by user admin on machine ServerName

What I'd like to know is - If I upsize the Access db to SQL Server will this overcome this problem of the db being locked out while updates are performed on its data?

I'd welcome any thoughts or suggestions.

Thanks

 

[Sheco]

It is possible to hit a lock with any database but, in general, a database designed to be used by multiple users like SQL Server is going to have many fewer locks than a database system originally designed for a single user like MS Access.

 

[Abraxus]

I have heard of problems with multiple users trying to update Access dbs simultaneously and having them come back as locked. If you have a SQL Server available I would say absolutely - converting is easy and SQL Server performs faster and more reliably, including on instances where the table could be receiving data from more than one user at a time.

Are you leaving recordsets open while updating? I find it's more efficient when you're going to perform an update to use an ADODB.Command object. It works similarly only you get to construct it before it runs... I set an activeconnection, set a commandtext, and the it's just object.Execute to perform the update statement. Good luck, I'm a huge fan of the portability of Access dbs but whenever I can put something on a SQL Server that's usually a better option.

 

[jondow]

Hi Guys,

Thanks for your comments. ecwee, Im currently using u number of loops and keeping the recordsets open, in the form:

while not rs1.eof
update
while not rs2.eof
select
update
select
update
rs2.movenext
wend
rs1.movenext
wend

I was also thinking of assigning the rs to a array and then looping through the array and closing the rs, do you think your method would be more efficient? If so could you give me an example of how to set it up?

Many Thanks

 

[jondow]

Scratch that last question, I misunderstood your comment.

 

[Tarwn]

You should be able to bypass the issue by modifying how your doing your updates, without having to use MS Access. If you move the code as is, it should work just fine on SQL Server, but it will still be using a great deal more resources then it needs to be.

Provided your operating completely off of one database, you should (almost) never need to have embedded loops in order to read data. Any nested loop like that should be replaceable with a single SQL statement. If your outer level is opening table A and your inner level is selecting from table B where B.someField = A.someField, then you should be able to replace the whole mess with a SQL statement that says: SELECT fieldnames FROM A INNER JOIN B ON A.someField = B.someField
This results in a single recordset, instead of COUNT(A) + 1 recordsets. This reduces how much resourcs your using and can additionally give you other options to release the recordset, such as using an array to loop through the data (per ecwcee's easy to follow FAQ): faq333-6473

Similar to ecqcee, I do not use Recordset objects for updating, I use the Connection.Execute method. In fact, i have a feeling that the select's your doing in your loop are simply to get the correct record you want to update. you could replace the temporary recordset, select, and update commands with a single Connection.Execute "update SQL string"

An UPDATE string looks something like:
UPDATE MyTable SET myField1 = 'somevalue', MyField2 = 'Some Value' WHERE somefield='a' AND somefield2='b'

I can't get any more specific and may in fact have given you advice that doesn't fit your situation. if you can show us the loop we could probably help to simplify it a great deal, speeding up your page, reducing resource usage, and clearing up the db locks your having issues with.

-T