Splitting Database and Distributing to Users

[RECrerar]

Hello,

I am getting there with completing my database and am now thinking of splitting it into a front end and a back end before distributing to users. I've never done this before so I have a few questions.

1. As I said, am almost there with the database, the table structure for example is finalised however I do still need to make some modifications mainly with the VBA code and maybe create another couple of simple reports and so forth but I'm moving department in a few weeks and would like to get v1 out before I do. Is it harder to make modifications to a split database? Should I ideally have the whole database in it's final form before splitting it?

2. I understand that you distribute a seperate front end to each user and that ideally they should store this on their C drive while the back end is stored on a sever that they all have access to. I don't work in the IT department so am a bit confused here. I can save the back end to a network drive but this may not always be mapped to the same letter so the absolute address of the back end would be variable. How would I deal with this? Am I mis-sunderstanding where I should save the back end?

3. How can I make it that the user can not see any of the queries/forms/reports except though a switchboard? (I have created my own switchboard as I didn't like the format of the built in one)

4. Following on from the above point. Is a reasonable way of restricting users access to certain parts of the database to distribute restricted switchboards (i.e that the front end some people recieve has less functionality than others)?

I think that will do for starters. I hope that makes sence, basically I don't want to split that database and then realise I've done something horrible wrong and have to start again

Any advice even if not directly related to the questions would be appreciated (I have access 2003)

PS. Sorry not directly related to the thread but how rohbust is Access to Upgarde, for example of my company decides to ever upgrade from 2003 how likely is my database to still work? - Are there specific commands/bits of code I should avoid, i think I read somewhere that DAO recordsets are becoming obsolete, I have used these a lot - could this be a problem?

Regards
Robyn

 

[fkegley]

1. Once you split the database, it becomes much harder to update all the front end copies with new reports, forms, etc. My advice would be to go ahead and complete as much as you can BEFORE the split.

2. You should link the front end databases using a UNC rather than a mapped drive:
\\servername\sharename\database.mdb.

3. From the database window, click Tools--->Startup... You will see a great many things you can do to restrict what the users can do when the database is open. One of them is to set the startup form. You can also turn off toolbars, shortcut keys, other stuff.

4. Yes, you can develop different startup forms for different groups of users. A custom login form can be used to determine to which group a user belongs. That can then be used to present the appropriate startup form.

DAO recordsets, AFIK, are not going anywhere. Microsoft is still using them in the Access 2007. I believe I read somewhere that ADO is going away, not DAO.

Microsoft changed the security model with Access 2007. That has been my biggest challenge. You will have to tell Access which locations to trust. Otherwise, it won't run code that is contained in a database that is contained in a particular folder.

 

[RECrerar]

Hi,

Thanks for the advice. I will get as much completed as possible then before splitting.

2. You should link the front end databases using a UNC rather than a mapped drive:
\\servername\sharename\database.mdb.

Is it possible to elaborate a bit on point 2 as I'm really not very hot on this area yet. Is it something I should get on the phone to the IT guys about?

4. Yes, you can develop different startup forms for different groups of users. A custom login form can be used to determine to which group a user belongs. That can then be used to present the appropriate startup form.

I like the your idea of a custom login form so I may try that. my other idea was to avoid the login form but directly looking at the Users Login Name and automatically presenting the correct for that way. A bit more complex maybe but I think it would be a ncie functionality

Thanks again. I will get on with completing all the niggly remaining bits and get back to you with more specific questions when it comes to spitting time no doubt

 

[boblarson]

Is it possible to elaborate a bit on point 2 as I'm really not very hot on this area yet. Is it something I should get on the phone to the IT guys about?

See this for a visual on how to do it:
http://www.btabdevelopment.com/main...linktoyourbackendviaUNC/tabid/69/Default.aspx

 

[Rabbie]

Also see the information on Bob's site about his utility which will keep all the FE databases up to date easily. I use it and can reccommend it.

 

[fkegley]

While Bob's tutorial link is good, it can be improved upon. The simplest way is to develop a Network Place, then at the point in the tutorial where you type in the UNC, instead navigate to the Network Places and select the correct one. Access will convert the Network Place name to its equivalent path. Voila!

 

[RECrerar]

While Bob's tutorial link is good, it can be improved upon. The simplest way is to develop a Network Place, then at the point in the tutorial where you type in the UNC, instead navigate to the Network Places and select the correct one. Access will convert the Network Place name to its equivalent path. Voila!

huh?

Sorry to be slow but firstly what exactly is a network place?
Next how would I create one and why would I want to?

 

[KenHigg]

Seems like figuring out the unc path and typing it in is simple enough - ?

 

[RECrerar]

Seems like figuring out the unc path and typing it in is simple enough - ?

Yep, I think I should be able to manage that (hopefully)

 

[fkegley]

Click Start, then My Network Places. There is an Add a Network Place choice in the left list. Click this and a wizard will start that will allow you to navigate to the server and share name on that server. It will build the path for you, you can give it a name. In the future to get to that place, you just double-click the icon.

I like them for databases because I don't have to use a mapped drive. It gives me the heebie-jeebies thinking about users with read/write access to a folder having an easy way to get to that folder. By using the UNC path as I posted above, the users don't actually see the drive that contains the back-end database, though the database sees it just fine. I can then store a generic front end database on the server and drag and drop that to the user's computer without worrying about then having to map a drive to make it work.

 

[Screaminxpert]

I use a Front End - Back End scenario on my database so I would like to throw in what I have learnt. I dont know if what I am doing is the correct way but it works.

Firstly I have the front end and back end on a server, every morning or when users log in to their PC a batch script runs which copies the front end to their C drive then the script tells the front end to run.

When running the front end I only allow it to run in 'Runtime' mode this is done by adding the /runtime switch to the batch script. This basically strips out all of the navigation and access menu's so users cannot change forms, queries or reports.

If I need to make changes to the front end I make the changes to the server copy and then get the users to log off and log on again this causes the batch script to run and so they always have an up to date copy of the front end.

Hope this helps a little

 

[KenHigg]

Hum... I haven't tried putting the runtime switch in the command line. Thanks for the tip -