Inserting/deleting data in table in multiuser environment

[Alex]

Hi Guys,

I’m creating mdb file (not a project) and I’m running a stored procedure on
SQL server that deletes and inserts data into a table. Then I’m making a
query and a report based on that table.

Everything is fine except I’m concerned about multiuser environment when
users could run the report hence a stored procedure simultaneously whether it
could create some conflicts.

Please, advise.

Thanks

 

[Brian]

I think you are saying that you populate a table with temporary data upon
which a report is based. In this case, you do need to isolate the data by the
user so that when two users run the report at the same time, their data is
not intermixed.

I can suggest two things:

1. Ensure that your table in the SQL database has a field for the user who
is populating the table (or a session ID or timestamp or some other unique
ID). Secondly, make sure the stored proc populates this field when it runs.
Finally, filter to the current user's entry in this field when running the
report. This way, it will look only at the data inserted by the current user
or session.
2. Alternatively, give each user his own copy of the front end and put the
temporary table in the Access front end instead of in the SQL back end. Then,
instead of hte stored proc, run a query to populate that table, imitating the
action of the stored proc. This way, the temporary data is in the front end
that exists on the user's computer and will not intermix with other users'
data for that process.

 

[Alex]

Thanks, Brian.

I was thinking the same as you’re suggesting in your second option.

Please, notice that store procedure on SQL server deletes all data from a
table and then insering new data after.

I did create a table in a front-end to just copy data from that table on SQL
server where all data is deleted (table emptied) and then inserted. So, yes,
the users would run local query and report on that local front-end table.

However, then I realized that the SQL table where stored procedure is
deleting all data and then doing inserting still could create a conflict as
the stored procedure will be running each time with report generation anyway.

So, the only way would be to bring a code from the SQL store procedure to a
front-end and populate that local front-end table using SQL tables using code
from the front-end. I thought I could avoid it somehow. Please, comment.

Thanks

 

[Alex]

Thanks, Chris.

It sounds great!
Please, advise how I could create randomly assigned names for a temp table
in sp.
But, the most importantly, I don’t know how I could use those temp tables on
SQL server from my front-end to generate a local front-end query/report.
Please, advise.

Thanks

 

[Brian]

Do you have the ability to:

1. Add a field to the table in the SQL DB?
2. Modify the stored proc?

If Yes to both, you could add a field (e.g. BatchID) to the SQL table for a
string representing the current timestamp (e.g. 20081003071008 for 2008-10-03
07:10:08). The chance of two users creating a BatchID within one second of
each other, while possible, is statistically impossible. Modify the stored
proc to accept this as an argument and including it when inserting into the
table. Capture the value to a variable before you send it to the stored proc,
then filter your query to records where that field matches the variable. Also
modify the stored proc to not delete the temporary records from the table. Do
the deletion after you run the query/report via a delete query or SQL
statement in VBA, and filter the DELETE statement to just those records whose
BatchID matches the timestamp-related string above.

If the answer is No to both questions 1 & 2 above, then perhaps you can find
out what the stored proc does and see if you can emulate that using SQL in
VBA code and or queries of your own, but delete/insert the records from/into
a table in the front end, where there will be no conflict.

 

[Alex]

Chris and Brian, thank you very much. The both suggestions are brilliant and
I believe will resolve all conflicts.