Action Queries vs VBA code in a multi user environment

[ancb]

Is it better or more stable to use sql code directly in a vba module than have an action query called from the vba module, particularly in a multiuser database? The database has a front and back end and each user has their own copy of the front end on their computer. It seems that I get a lot of corrupt records when action queries are executed.

 

[ancb]

Anybody?

 

[pweegar]

When you perform an action query, are you locking the table(s) for exclusive use?? Sounds like the tables need to be locked before an action query is taking place. The corruption comes from a collision being caused when two or more users are accessing the same record9s) at or about the same time.

We ran into a similiar situation with Foxpro saving records thru Samba on a Linux box. Samba didn't do any record locking and we had all kinds of collisions and data loss.

 

[MichaelRed]

In particular, if you are uising AutoNumber and adding records you will have this among other difficulties. see fsq700-184. If not adding records, see the ubiquitous {F1} a.k.a. Help re Transactions.

p.s. it (the ubiquitous thing) can also be helpful in other situations / areas.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[ancb]

Thanks I'll take a look at that.

 

[gusbrunston]

[tt]
Hi:

Don't know if it's so or not...

I did read that Access was configured so queries run faster than SQL = "SELECT ... " statements.

Don't know that I've ever noticed any difference...[/tt]

[tt]Gus Brunston - Access2000(DAO)
Skill level based on 1-10: 7, on the way to 6, and beyond!
Webmaster: www.rentdex.com[/tt]

 

[vbajock]

I am a big believer in coding ADO recordsets to do appends, deletes, etc rather that relying on either a stored query or a sql when in a multiuser environment. It takes a little more time but the amount of control you have vs using sql/query shortcuts more than make up for the lost coding time. With a recordset, you have a choice of lock types, cursors, single or batch update, and more.

I have worked in big corporate shops where the initial app is prototyped by some departmental Access query/guru type guy, and then that "app" is passed to an IT department programmer, who then replaces the macro/query stuff with real code, using VB or Access VBA.