splitting a database - what am i doing wrong?

[BillyH1892]

Hi, could someone please help me as this is driving me crazy:banghead:?

I work as a stock controller for a large multi-national company. I work in a small team of 12 people and we use Access 2010 on a daily basis. The problem is that the database is set up as single user only. If you want to access the database, you must ask if it is available. Sometimes we have a queue of people waiting to gain access.

I recently announced that this was crazy, as Access allows multiple user access and that i would fix the problem. Now i'm stuck and a little embarrassed

I have read lots of articles, viewed forums and watched videos on Youtube but i still cannot get it to work.

I've set up a folder on the network F drive and called it PRACTISE1. I have created a new database also called PRACTISE1 and placed it in the folder. I've made it shared access File -> options -> client settings.
I have split the database with a _be name, saved the front end as PRACTISE1_Alan_fe.accde and linked the tables.

I think i've done everything right so far.

What i have now done is copied the PRACTISE1_Alan_fe.accde front end file and pasted a new file and saved it under PRACTISE1_Ryan_fe.accde. Is this the correct way to replicate front ends?

So the PRACTISE folder now contains the back end and two front end files.

I went onto Ryan's computer, found the Ryan_fe, opened it and asked him to run a practice macro when i was also running a macro.

Unfortunately, it kicked him out as he did not have exclusive access

What am i missing? Could someone please help as i am so close, yet so far away

Regards,

Alan

 

[pbaldy]

For starters, check the setting in File/Access Options/Advanced for default open mode. It should be shared. Record locking should either be edited record or no locks.

As to structure, the "normal" setup is the back end file in a shared central location, and a copy of the front end on each user's PC. Having individual copies like you've done can work, but there's a network performance hit as the application itself has to be sent over the wire rather than just data.

Most of us handle the distribution of new versions with some sort of utility rather than having to visit each PC. There are any number of ways to create that utility; batch files, applications, etc. I use a "version" table in the front end and a utility that checks the version on the user's PC against a master copy on the server.

 

[BillyH1892]

Hi Paul,
thanks for your quick reply. I definitely think my problem lies with the front ends.
My biggest concern now is how do i create and distribute front ends? Due to very strict access control, I am not allowed to change any settings on my PC, download any software or run any application that is not on the approved list.
How can i get around this? Could i email the original front end to my home pc and create the front ends on my own machine?
i had planned to make 12 individually name front ends, is that the right thing to do?
Sorry if i am asking stupid questions here but i just need to clarify exactly what i need to do

Any help would be most gratefully received

Alan

 

[Anakardian]

If you do as pbaldy suggested and have a version table in both the back- and front end, you can check if the two match when the front end is started.
If they match, just continue as it means you are running the latest version.
If they do not match, stop and go to some kind of update routine.
The update routine could be as simple as to contact you, a link to where they can get the new version or even something that automatically updates the front end with the latest version.
What you choose really depends on what you want to achieve.

My advice would be to start with the "call you" option until you get more comfortable with the entire process. After that you can look at the other options for updating.

I would not go for individually named front ends as they should go on the users computer. for that reason there is no need to rename them and it will keep your life much simpler.

 

[pbaldy]

I would not use individually named front ends, though it would work. There are many ways of updating. There's a batch file method here:

http://www.thatlldoit.com/Pages/howtosarticles.aspx

A member has a method here:

http://www.access-programmers.co.uk/forums/showthread.php?t=265491

Former member Bob has a method here:

http://btabdevelopment.com/free-access-tools/

 

[BillyH1892]

hello again, the replies have been very helpful but i still have a few issues.
Sorry if i am struggling with this one but seriously, i run queries, macros and reports on aa daily basis but i've done nothing like this before with Access.
You may have realised that i'm not a 'techy' sort of person. Too be honest, i still don't understand how to create additional front ends. Everything that i had previously found was about splitting the database and then there was a suggestion to simply distribute the front ends.
I come to you guys because you are the experts, you know this stuff inside out
I know this is basics for you guys but could i please clarify;

1. after creating the initial front end, how do i create another without the use of other applications? My pc at work will not allow me to download any application
2. if there are 12 people on the team who can access the database, do i make 12 front ends? I assumed everyone got their own front end and put it on their desktop?

Please don't pull your hair out and please bear with me. If i can do it correctly once then i'll be sorted

Regards,

Alan

 

[pbaldy]

The first two links I posted don't require any program download. Copy the script/code and use it locally, modifying as appropriate. Basically you put a master copy of the front end on a server. The batch file or code does the work of updating each PC.

Maybe easiest for you is to manually give each person a copy, either by going to each PC or emailing it to them. Then, as you get more comfortable and find you need to update the front end, you can learn how to make a utility to handle the updates.

 

[JeremyR23]

For some reason I cant figure out how to create a post so info on how to do that would be good to, but back to splitting databases. I just split a database and would like more info on where I need to store the back end. Do I need to ask my supervisor for contact info to our network administrator in order to create a shared LAN folder or what do I need to do.

 

[pbaldy]

It needs to be accessible to all users, so a shared folder on a server is typical. They need read/write permission.

 

[JeremyR23]

I should gain access to shared LAN folders soon. I am under the impression I just save my back end there, email the front end to the dozen users that will be using it. Have them download it and it will all work fine. I know it cant be that easy. What problems will I face.

 

[pbaldy]

That should work, as long as they have access to the back end folder. Make sure you use a UNC path when linking, rather than a mapped drive, unless you can be sure everybody has the same mapping.

\\ServerName\Foldername\FileName

Most of us use some sort of utility to handle new front end versions.

 

[JeremyR23]

What are the limitations of a front end database. Can tables be directly edited from the FE?

 

[Simon_MT]

Of course.

All you are doing is separating the JET (mdb) or Ace (accdb) database engine from the Access application aspect of the application. The only downside is version control mentioned above but that is far outweighted the benefits of splitting FE/BE. Essentially all you are doing is making a call to the data and not running the application on the server and down the LAN.

Simon

 

[gemma-the-husky]

in this respect access is no different to any other program.

take excel. you all have MS office. A spreadsheet will reside in some common location. Any user can open the spreadsheet using their own copy of excel.

access is just the same, except that you can all open the database together without getting the "readonly" message you get with excel.


So when you open the database, is there an "open exclusive" check box somewhere (they move stuff like this in each access version, so I am not sure where it will be). Make sure no user is opening the dbs in exclusive mode.

 

[gemma-the-husky]

depending where you are, it might be wroth getting someone in for half a day to guide you. Once you get started, I am sure you will have no problems - but Access is nothing like as useable as Excel out of the box.

eg Most people can get a useful spreadsheet without writing code. You cannot possibly achieve anything useful in access without code. Indeed at least half of the code is there to limit what users can do (including the developer!), as access is just so powerful.

 

[The_Doc_Man]

Due to very strict access control, I am not allowed to change any settings on my PC, download any software or run any application that is not on the approved list.

Depending on just how strict this is set, you might have some issues, but with a little bit of reasoning (and maybe pointing people to this forum for explanations of configuration issues), you can live in this kind of environment. I with the U.S. Navy and we have some very stringent restrictions, yet I have a database that I can update at need.

I note that Jeremy has joined the thread as a questioner rather than a contributor. Jeremy, we are loose about this, but in general you should make your own thread to assure that YOUR question is being answered and not someone else's. On the other hand, your question IS close the Billy's.

Here is how you do this in a way that keeps your network security people off your butt.

1. Let them know that everyone will be using MS ACCESS and that they all will need their own local copies thereof. Further, you will need to set up a domain-level rights group OR a server-based rights group using domain-user IDs as group members. (The choice there is a matter of site preference). You might also want a second group ID for the allowed developers, depending on how you set things up.

2. On the designated server, build a SHARE and a couple of folders under it. In my case, we have \\servername\sharename\PROD and three other folders (TEST, DEV, ARCHIVE) at the same level.

3. Give the user group MODIFY rights to the \PROD folder and its contents. (If you have a separate group ID for developers, give that group MODIFY rights on all of your folders.

4. Make users members of the appropriate groups.

5. Now, in the PROD folder or whatever name you needed it to be, store the following files. (a) The Back-End (BE) file. (b) The Front-End (FE) file. (c) A link to your PROD folder. The FE file's "Current Database" options page must NOT have "Exclusive Open" checked.

6. Now tell each user to create a folder on their individual machines, one that will be used exclusively for your FE file.

7. Give them the \\server\sharename\production-folder-name\ name and tell them to OPEN that path. (If they need to map a drive letter to do this, it is OK but you don't need to keep that drive letter mapped permanently.)

8. Tell them to copy the link file and the FE file but NEVER EVER can they open that file directly. On the other hand, they can open their copy on their local machine any time.

9. From that time forward, they open the local copy. If you have a new version, they can open the link file and do a COPY/PASTE operation to get the file from the shared location to their private system location.

This has been repeated before (and probably will be repeated again). There are many ways to skin this cat <MEEEOOOOWWWRRR!> but the way I just described works great for me with a 30-person department and maybe 5 or so users simultaneously tapping on the tables.

 

[JeremyR23]

I split the database, have it on shared mode, made the shared folder a trusted spot, and both me and my co-worker have read/write permissions. Why when my co-worker tries to open it,while I have it open, she gets a "already in user" error. If I don't have it open then she can get in, but then when I also open it at the same time it doesn't allow either of us to edit data. Help please!

 

[JeremyR23]

PS: Sorry Doc Man, as I posted earlier I do not know how to create new posts/threads