multiuser issues in msaccess project subforms

Question

multiuser issues in msaccess project subforms

suha hammad

SSC Enthusiast

Points: 121
More actions
June 27, 2005 at 12:43 am

#66712

Hi,
i would like to develop a form-subform for entering and viewing sales transactions. i use a subform bound to a temporary table and on the click event of the save button i execute a stored procedure in sql server to save the data and validate it. This way does not seem to work in a multi user environment where each user needs to link to his own temporary table.
I would like to ask what is the optimum way to handle forms - subforms in ms access project with sql server environment.
Regards,
suha

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

The answer is to use either Local Access Tables or Unbound forms with vb Classes.

You are goin to have probles if you try bound forms with Linked SQL Tables this Topic is way too lengthy to give you a road map I would suggest you pick a copy of MS ACCESS DEVELOPER HANDBOOK and read it cover to cover, you will be glad you did

* Noel

suha hammad SSC Enthusiast Points: 121 More actions · Answer 2

Thanks Noel,

i have already ordered Microsoft access developers guide to sql Server by Mary Chipman and Andy Baron. Since i am a beginner.

The problem is that i have already developed the project for a single-user use. And i don't want to recode it again; i just want to make slight modifications to use it in a multi-user environment.

i tried to link to local tables but the subform becomes unupdatable when linking to a table in a local mdb file... any suggestions !!??

Regards,

suha

Dick Schroth Right there with Babe Points: 725 More actions · Answer 3

one workaround for you might be to add the username to the temporary table. then create a view or stored procedure using the SUSER_SNAME() function, which essentially gives each user their own virtual table.

Hope this helps!

noeld SSC Guru Points: 96590 More actions · Answer 4

The problem with remote (linked) tables and Access BOUND forms is the they perform TABLE LOCKS therefore multiuser enviroments are really hampered. The reason I believe your Local Table is read only is because of the absence of a PK.

hth

* Noel

Robert Stewart Hall of Fame Points: 3399 More actions · Answer 5

The other thing you need to look at is running the Access application on each local machine and NOT, repeat, NOT, from the server. If you have everyone using the same GUI, you are asking for problems with corruption.

You need to join one of the Access lists. Access-L or Access-D (or both). You will get the best Access help from people that know Access and not from people that are into SQL Server.

suha hammad SSC Enthusiast Points: 121 More actions · Answer 6

Thanx Dick,

your workaround works very fine and saved a lot of time.. thanx again.

Regards,

Suha