dsoderstrom
asked on
Microsoft Access Multi-User Application questions
I have been considering writing an application for collecting labor from our plant floor.
I would like to write this application using MS Access 2010. It would be deployed to about 25 workstations as an .accdr file along with MS Access 2010 runtime.
I would like to store the data tables in an SQL Server database, linking to them using ODBC.
Basically it would collect 4 types of transactions:
1) Clock IN time for the day.
2) Clock ON time for jobs.
3) Clock OFF time for jobs
4) Clock OUT time for the day
Each worker has a unique badge ID and would be entering their own data. They will all be appending to and updating the same tables but no two workers will ever be updating the same record in these tables because of the unique badge ID.
My concern is at the end of the day when everyone clocks OFF of jobs and OUT for the day on these multiple workstations simultaneously. Is there going to be problems caused by multiple users appending and updating these tables at the same time?
Any thoughts or suggestions would be appreciated.
I would like to write this application using MS Access 2010. It would be deployed to about 25 workstations as an .accdr file along with MS Access 2010 runtime.
I would like to store the data tables in an SQL Server database, linking to them using ODBC.
Basically it would collect 4 types of transactions:
1) Clock IN time for the day.
2) Clock ON time for jobs.
3) Clock OFF time for jobs
4) Clock OUT time for the day
Each worker has a unique badge ID and would be entering their own data. They will all be appending to and updating the same tables but no two workers will ever be updating the same record in these tables because of the unique badge ID.
My concern is at the end of the day when everyone clocks OFF of jobs and OUT for the day on these multiple workstations simultaneously. Is there going to be problems caused by multiple users appending and updating these tables at the same time?
Any thoughts or suggestions would be appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
""Docmd.Runsql" method in vba."
Let me suggest you use this:
CurrentDB.Execute "YourActionQueryNameOrSQL"
With, you can trap error you would want to know about, and no DoCmd.SetWarnings True/False is necessary.
Example:
Function mCurrentDbExecuteSeveralQu
Dim X1 As Long, X2 As Long, X3 As Long
On Error GoTo mCurrentDbExecuteSeveralQu
With CurrentDb
.Execute "YourActionQuery1", dbFailOnError
X1 = .RecordsAffected ' tells you how many records were processed - This is optional
.Execute "YourActionQuery2", dbFailOnError
X2 = .RecordsAffected ' tells you how many records were processed
.Execute "YourActionQuery3", dbFailOnError
X3 = .RecordsAffected ' tells you how many records were processed
' and so on ....
End With
mCurrentDbExecuteSeveralQu
Exit Function
mCurrentDbExecuteSeveralQu
' Your error handling code here
Resume mCurrentDbExecuteSeveralQu
End Function
Let me suggest you use this:
CurrentDB.Execute "YourActionQueryNameOrSQL"
With, you can trap error you would want to know about, and no DoCmd.SetWarnings True/False is necessary.
Example:
Function mCurrentDbExecuteSeveralQu
Dim X1 As Long, X2 As Long, X3 As Long
On Error GoTo mCurrentDbExecuteSeveralQu
With CurrentDb
.Execute "YourActionQuery1", dbFailOnError
X1 = .RecordsAffected ' tells you how many records were processed - This is optional
.Execute "YourActionQuery2", dbFailOnError
X2 = .RecordsAffected ' tells you how many records were processed
.Execute "YourActionQuery3", dbFailOnError
X3 = .RecordsAffected ' tells you how many records were processed
' and so on ....
End With
mCurrentDbExecuteSeveralQu
Exit Function
mCurrentDbExecuteSeveralQu
' Your error handling code here
Resume mCurrentDbExecuteSeveralQu
End Function
Please follow Joe's advice on the CurrentDB().execute.
Also, as with any BE other than JET, you are better to push as much as possible server side no matter how many users.
Now with ODBC and linked tables, JET will look at the SQL being executed and if possible, push it server side for you. But it can't do that if you join to local tables, use VBA expressions, or JET specific SQL.
So keep that in mind when working with your queries. Also, if you can make a query pass through, then do so. Also don't forget you can use views in SQL as "tables" in your app. Again, that leaves a lot of work server side.
With 20-25 users, you can use Access "as is" without issue really, but all I'm saying is "you can do better than that" and if at all possible, you should.
Jim.
Also, as with any BE other than JET, you are better to push as much as possible server side no matter how many users.
Now with ODBC and linked tables, JET will look at the SQL being executed and if possible, push it server side for you. But it can't do that if you join to local tables, use VBA expressions, or JET specific SQL.
So keep that in mind when working with your queries. Also, if you can make a query pass through, then do so. Also don't forget you can use views in SQL as "tables" in your app. Again, that leaves a lot of work server side.
With 20-25 users, you can use Access "as is" without issue really, but all I'm saying is "you can do better than that" and if at all possible, you should.
Jim.
ASKER
Great advice which I will follow. Thank you both.
One thing caught my eye though. Jim, you referred to JET. I was under the impression that MS Access no longer used the JET engine starting with version 2007. Apparently I was misinformed.
One thing caught my eye though. Jim, you referred to JET. I was under the impression that MS Access no longer used the JET engine starting with version 2007. Apparently I was misinformed.
JET became ACE (Access Connectivity Engine) which became ... humm, not sure now (some similar name). But ... at the end of the day ... it's still JET .
ASKER
Actually I will not be binding any forms to these linked tables either. All of the updates and appends will be done using the "Docmd.Runsql" method in vba.
Thanks for your input Jim.