By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,336 Members | 2,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,336 IT Pros & Developers. It's quick & easy.

Multiple users at the same time

P: 56
Hi, just wondering if there is any way to have more than one user in an Access 2003 database that links to a SQL Server Database?
Jul 24 '07 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi, just wondering if there is any way to have more than one user in an Access 2003 database that links to a SQL Server Database?
Yes, you can either create multiple copies of the the frontend database or go to Tools - Options and under the Advanced tab change the Default open mode to Shared.
Jul 30 '07 #2

P: 56
Yes, you can either create multiple copies of the the frontend database or go to Tools - Options and under the Advanced tab change the Default open mode to Shared.
Thanks for your reply - I checked and we are set to share. I am thinking that we are in an unfixable bind.

We have one database - that often, more than one person would like to use - at the same time - to share the workload (this is why we want to share among other reasons)

One main query which accesses the linked tables is used by any who are using it - and then non-linked tables are updated by other querys.

There are reports that are generated - which we must print - but we do not print untill we have completed all aspects of all assignments as to prevent confusion on the print end (multiples of same report w/different names - so we save all the reports and leave them open - and if multiple users are in - we cannot save)


The biggest kicker is that we also use Crystal Reports(which requires the mai query to be saved and not in use during generation) in conjunction with this database - so duplicating it would require seting up duplicate files based on which database is being used....(trying to avoid that due to the shear # of crystal reports) Also - do not want to have to update 2 different database application forms everytime something is added - I am the only one who knows how to do any type of development in access for my company - and the limitations are causing questiontionablity to the worth of the access platform, which in essence questions thier need of me.

In the advanced Options screens you pointed me to - we have Shared selected, no locks sleected and open databases using record-level locking selected, also enable dde refresh is checked - and ignore dde requests is un-checked.

So I guess what I was wondering - is there a way to have a sort of shell of the database that is unique to the user's computer - but allows for multiple users on different computers to work at the same time with the same stuff w/out the sole user access issues? I thought it was a mde file - but for some reason I am not able to create one - and I don't know how to set up the security for it either.
Aug 4 '07 #3

JConsulting
Expert 100+
P: 603
Thanks for your reply - I checked and we are set to share. I am thinking that we are in an unfixable bind.

We have one database - that often, more than one person would like to use - at the same time - to share the workload (this is why we want to share among other reasons)

One main query which accesses the linked tables is used by any who are using it - and then non-linked tables are updated by other querys.

There are reports that are generated - which we must print - but we do not print untill we have completed all aspects of all assignments as to prevent confusion on the print end (multiples of same report w/different names - so we save all the reports and leave them open - and if multiple users are in - we cannot save)


The biggest kicker is that we also use Crystal Reports(which requires the mai query to be saved and not in use during generation) in conjunction with this database - so duplicating it would require seting up duplicate files based on which database is being used....(trying to avoid that due to the shear # of crystal reports) Also - do not want to have to update 2 different database application forms everytime something is added - I am the only one who knows how to do any type of development in access for my company - and the limitations are causing questiontionablity to the worth of the access platform, which in essence questions thier need of me.

In the advanced Options screens you pointed me to - we have Shared selected, no locks sleected and open databases using record-level locking selected, also enable dde refresh is checked - and ignore dde requests is un-checked.

So I guess what I was wondering - is there a way to have a sort of shell of the database that is unique to the user's computer - but allows for multiple users on different computers to work at the same time with the same stuff w/out the sole user access issues? I thought it was a mde file - but for some reason I am not able to create one - and I don't know how to set up the security for it either.
The problem you're facing isn't unique. And to answer your question directly, yes, as long as you have an application front end, with your tables, be they ODBC or Access linked as the back end.

There are a few ways to do what you're wanting to do. One way IS to create the .mde front end and simply distribute it to each user's machine. The linking mechanism that you use should use the relative path to the back end(s). ie. //server/folder/backend.mdb, or your ODBC should be set up to point to a server as well, and not just a drive letter.

When the user opens the front end in this configuration, his copy will be unique to him.

Another option is to create either a bat file, or a small database that quite literally creates a copy of the front end for the user to open vs the MAIN copy. This is generally acceptable practice when an application is deployed to a Citrix or Terminal Server configuration. A shell script can be called from this bat or mdb to open the "copy" front end.

This is not a difficult task to accomplish, and feel free to take a look at a sample of the method above on my website. Its called the Launch DB and although it's not catered to your specific environment, may assist you in completing your task.

If you choose to use the launch method, then all your users would need is a shortcut on their desktops that point to it, and when it opens, it does the rest.

Let us know what you decide to do.
J
Aug 5 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.