Synchronous & Asynchronous ACCESS Database Access

Hello All

What's the deal with Synchronous and Asynchronous ACCESS database access.

I prefer synchronous but am not sure if I'm getting it with my ASP pages hosted here. The updates APPEAR to be synchronous but it mightjust be updating so fast that it appears that it's synchronous while I test and will slow down when the database gets more full.

What do you have to do to ensure that the database updates are always going to happen before the code moves on, i.e., synchronously?

Thanks for any help

PeteB

 

Hi Bruce





In VB I believe there were ways to force synchronous or asynchronous updates.





I see that most people on the forum are anti ACCESS; your informative reply leads me to believe that you are one of them.





In timeI will probably port all of my DB apps to SQL but for now I'm sticking to ACCESS as the size and speed doesn't warrant it.





My question still stands.





Thanks





PeteB

 

Hello


Apparently there's a registry key as shown below which is supposed to indicate which mode to use.


DisableAsync=0 means Async updates; DisableAsync=1 means Sync updates. 1 is the default.


I have emailed support asking how it's set on the DiscountASP servers and will post the information from their reply here for those interested.


Thanks


PeteB


**********************************


The \HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\ODBC folder contains initialization settings for the Microsoft Jet database engine.

 

Support advises that the DisableAsync key is set to the default of 1.


This means that ACCESS database updates should be SYNCHRONOUS


yay!

 

Further to the previous entry on this thread, it appears that if you are using DNSless connections you can specify additional parameters in your connection string to affect the Synchronous/Asynchronous behaviour of your Jet updates.


See URLs:


http://doc.ddart.net/mssql/sql2000/html/mdacxml/htm/wpmigratingappendixb.htm


http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdrefjetprovspec.asp


as a starting point, and search for keywords like "Jet OLEDB:Implicit Commit Sync" to drill deeper.


Although I couldn't find a anything that said "To make a synchronous connection do this ...", it appears that all the defaults would make for Synchronous updates.


That coupled with what I'm seeing gives me hope that that is what I've got.


I'd love to hear from anyone who could help confirm or deny what has been said here.


Thanks


PeteB

 

Hi Macronencer

In the end I upsized to SQL Server. Not because of the asynchronous issues but because of SERVICE UNAVAILABLE errors that I thought were coming from ACCESS's use of connections when multiple users were hitting the database.

As it transpires I'm still getting the SERVICE UNAVAILABLE errors when 8 or more people are hitting my pages - but the folks at DiscountASP are helping me look into that.

I would imagine thatthe nature of the ODBC layer would be such that all of your application level synchronisation stuff (mutex et al.)may notcount for much. Ifthe app is happily passing data to ODBC and ODBC is saying "Yep got it, off you go", but it's really buffered somewherethen we're in trouble. i.e., the ODBC layer is reporting back to the application that it's written - but maybe it aint.

I think this is where setting the FLUSH values etc at an OS/Provider level in such a way that the driver/ODBC etc layers aren't hanging on to the data but writing it straight away is really where the Synchronous/Asynchronous behaviour is going to come from.

Everything I saw in the behaviour of Classic ASP (VBScript), ADO and ACCESS on the DiscountASP platform looked synchronous to me - and I had some bits of code that relied on it being so.

Without much more science than is in this thread, I think I'd try doing a test using an ADO connection string with all of the defaults to the database and see what happens.

PeteB

 

Thanks for that, Pete. I'm actually now trying a different approach based on locking the record with a transaction/UPDATE combination in order to ensure an exclusive lock before continuing (my code is actually providing a generic locking service across various tables).

Meanwhile, you may find this interesting:
support.microsoft.com/default.aspx?scid=kb;en-us;200300

It says that I need to close and re-open the ODBC connections in order to ensure that the data values are correct - which seems to tie in with your theory about caches /emoticons/smile.gif

However - I've tried it, and it doesn't seem to work. I'm beginning to suspect that my analysis tool is flawed, as it shows the same errors whatever I try!!

 

mmmm...I love the way it's "by design". It's always got in the way for me rather than helped!

If the connections are on different pages, or you have to use different connections within the same page sounds like you might have to put some redundant reading loops in to wait for the new data to show up - maybe write a flag into a text file to let you know that a write has happened! Kind of defeats the purpose though!

I was only ever using a single connection string at a time with multiple recordsets open.

Good luck

 

Hi Pete,

I'm not so sure this works. I am using C# ASP.Net and ODBC to connect to a local Access database. I am having a concurrency problem, wherein one thread writes to the database with an UPDATE (with or without a transaction), and then another thread does a SELECT and reads the OLD data that existed before. The threads are safely interlocked with a mutex, and I have double-checked - this is really happening!

So far I have investigated the 'Implicit Commit Sync' as a possible solution, but unfortunately it only seems to be available at the Jet (ADO) level, and I'm using ODBC connections, so I can't see these properties from my code. I have checked my registry key as above, and the value is set to 1, so that isn't the issue.

I'm stuck at the moment, so if your investigations have brought anything else to light, I'd be grateful to hear about it!