Multiple User Database 2003 - Best way to deploy (nationally)

[noaccessguru]

Hi All,

I have been developing an Information database over the past couple of months and now I am ready for users to be able to use it.

But before I do so I just need some help with a few things in regards to multiple users.

I need to find out the easiest and best way to deploy this to Multiple Users, at multiple sites Nationally (and possibly eventually Internationally) so that I can also still edit and make changes to the database. (possibly up to 100 users?)

I DO NOT want to set up Users and Groups.
I will be doing this at the folder level (via an ACL) where this database will be located and at this stage only I will have Admin rights to it.


The questions I need help with are:

· If I split the database into FE & BE, and make the FE available to users does it matter where the BE is located? Does it need to be in the same location (drive) as the FE? (obviously not in the same folder).

· How can I set it up so that multiple users can be running queries at the same time, and also allow me to make changes to the database without it locking up? (can I make changes in the BE if multiple people have the FE open and some running queries etc)?

· Is the ONLY way to make changes to the design in the FE to have everyone out of the FE?

· The database will be saved in a folder on a Shared Network Drive, since this will be used by multiple users nationally, will it be ok for them to all just access this one FE file from the Shared Folder? ( I have read about doing shortcuts etc to the database on each individuals desktop but then have to update it when changes are made to the FE? Is this correct?)

· Pretty much the same question as above, but does each user need to have their own copy of the FE file? Is this the only way? Or can multiple users access the one FE file??
Then if I need to make changes to the FE (which wont happen too often), I would need to supply them all with a new FE?…not sure how feasible this will be when there may be 100’s of users using the database


· We have an internal portal/intranet, I can set up a link on here to open the database (FE) from the Shared Folder, will this be ok as it will mean everyone is going to be using the same FE?

· Just confirming I can make just the FE an MDE file? Can I do that? Is this required if I make my ACL to the folder ‘Read Only’?

I will disabled the access for users to get into the Design View etc, this isn’t really my main issue, I ideally just need to know the best EASIEST way to make it accessible to many users so that I can still make changes.


Sorry for the million questions, but as you can tell from my Username I am certainly ‘No Access Guru’ and no IT person, so ‘idiot proof’ help would be greatly appreciated!

Thanking you all in advance

P.S - Sorry if this has all already been covered in previous threads etc....I dont have time at the moment to try find it all haha, but will go through when I can.....thanks again!

[June7]

This thread discusses how I administer a multi-user split database on a local network
http://forums.aspfree.com/microsoft-...ue-323364.html

I believe multiple users can run the same frontend but I have not used that approach. Could be performance is improved if each user has their own copy installed on workstation, like any executable app. My project has code that assures users have most recent version of FE. That is also discussed in the referenced link.

Your deployment situation is beyond my experience but I have seen discussions about use of Sharepoint for web-based database. Also, many comments that SQL would be better application in these circumstances. Access has a 2gb file size limit. Is it possible that you will exceed that?

[orange]

see http://www.kallal.ca/Articles/split/index.htm

[noaccessguru]

Hi June7….

Thanks for this Information. I have just read a few more things and by the sounds of it if I have multiple users using the same FE it will cause the database to run pretty slowly.
I cant imagine the database will exceed 2GB in a hurry, will at least give me plenty of time to work on improving this database and hopefully the business should be getting a better system to manage this information before this one hits 2GB!

I have very little knowledge of SQL so right now that isn’t going to happen. Guess my only real option is to place a copy of the FE onto each users PC.



Orange:

Thanks heaps for that link. I actually remember reading that page before and totally forgot that I had saved that link in my Favourites! Very useful.
So I guess I just have to deal with the fact that the FE application needs to be loaded onto each individuals PC….I will just have to advise everyone that changes to the FE design etc will only happen once or twice a year or something. SO best they decide of any changes now before it is given to everyone haha.

- Just 2 quick questions with loading the FE it onto everyone’s PC…I know Rookie question here, but is that just as simple as Copying the FE .mde file from the Network folder and pasting somewhere onto their PC (C: drive)?
AND if/when I make changes to the FE (which I will do in another copy) Can I just save it over the file in the Shared folder and then just advise people to go to that folder, take a copy of the database and paste it on their C: drive location OVER the current copy they have?

One other little thing, Does everyone need to have full access to the folder that the BE is located for their copy of the database to work?

Thanks in advance

[June7]

Yes, I think. As long as UNC pathing is used. I don't use mde but that is what I do with mdb. BTW, my link discusses how I automatically distribute revised FE. Does require users to close and reopen project to initiate update procedure.

[noaccessguru]

Orange

Thanks heaps for this, I did see the link to the Auto FE Updater, but unfortunately I can’t access the files here at work, so will have to check that out at home and look into that!
Is that pretty easy for a ‘non access/programmer’ person to use?

And also, is it something I will be able to use without needing the business’ IT help (we have recently been locked out of being able to download or install anything now….everything has to be done through IT
(I wasn’t even able to install the Wizard to Split my Database grrrrrr!!

[June7]

I have to install the FE on each user workstation at the C: root because IT permission settings won't allow the VBScript to copy into folders.

[noaccessguru]

Thanks June7.

Im just thinking though, If I am the only one that will ever be updating or changing data in my database, and all the users will be doing is running the queries/reports etc that I have set up and provided in the Front End Switchboard, will this cause record locking issues etc if all users use the same FE MDE file?

This is literally just an information database, and all the users will be doing is looking up data to see its information. Noone will be adding or changing records but me?

[June7]

So users will open only reports and/or forms that are locked against data editing? I doubt record locking will be an issue with running multiple instances of the same FE any more than running multiple copies. All are accessing same dataset. It is code execution and object rendering performance that might be impacted.

[noaccessguru]

Yeah thats right.
Everyone will just be accessing the reports, forms and queries (cascading combo boxes etc) that are all set up in the front end and thats it, and that will all be locked to editing.
I will be the only one editing records and that will only be maybe once a week, sometimes a couple more, somtimes less.
All I will be doing is adding new records and changing the status etc of existing records.

[June7]

Careful, access to queries could allow edit of data through the query.

[noaccessguru]

Yeah that was one concern, that they can still access the design view etc of Queries, even in an MDE file.
Im banking on the fact that pretty much everyone that will be using the database has no idea how to use Access, so wouldnt even know to do that.

But is there an easy anyway to password protect or disable this at all?
Other than just removing the buttons?

[June7]

I haven't used 2003 in a long time but I think it has built-in securities (removed from 2007/2010 versions). Search Access Help or Google.

I have never converted to mde.

Access 2007/2010 offer project setting option to hide the ribbon, QAT, shortcut menu, and Navigation pane. If I remember right, 2003 also allowed to hide equivalents. However, holding the shift key when opening MDB/ACCDB allows override of these settings. I don't think this applies to MDE/ACCDE.

[noaccessguru]

Thanks for that.

I will look into these other security options.

There really isnt too many Access 'savvy' people that will be using this database, so I will lock it down as much as a I can, removing toolbars etc and will see what happens. I will have a copy of the database plus my own copy so I will at least have back ups if anyone manages to find there way into the back end!

Just one other question, when you split your database, do all users have to have a certain level of access to the folder where the BE sits aswell?
or can I have this in my own folder anywhere (on the network) and noone really needs to know where it is or have acess to it?

Cheers

[June7]

Permissions on the folder holding the backend will have to be read/write for anyone needing to enter/edit data. Talk to IT manager.