Append Query vs Using Recordset vs SQL - which to use?

[StuKregor]

Two parts to me question...

One
I have a database that will potentially have 50-60 users. It will have a frontend located on each PC. Each user enters data into a form and when they hit enter it adds that data to the backend on the server.

To do this "adding" which is the quickest and safest from a locking perspective...

1. to use a simple "saved" append query to the linked table
2. Open a recordset and add
3. Use an SQL INSERT statement to the linked table

Two
Because users only ever add data to the back end I don't think I'm being too ambitious using access, but am I wrong?
Will 50-60 users possibly appending data at once bring the whole thing down around my head?

Thanks in advance.

 

[Rhonin]

Nice question. I have used all three with a front/back access application. What I have found works best (IMHO) is to have the user enter data to a local temp table then append the data from the temp table to the back end. This way any background additions/modifications can be carried out prior to posting to the back.

In reality though, any of the three ways will work fine. Use whichever you are more comfortable with.

 

[Databaseguy]

The two things you're going to have to watch out for are duplicate entries and locking.

Using DAO and the .addnew method, if the record already exsists, you'll get a runtime error that you have to trap.

There's no real difference between a SQL statement and an saved append query. The append query would contain the SQL statement.... Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[StuKregor]

Thanks for the answers guys.

I have read that a saved query will run slightly faster than an SQL statement, although in this particular case it won't make much difference I would think.