Is splitting the DB the correct choice?

[tc197]

What I have created is a database that has a few forms needed to be filled out by just a few people at any given time at my company. Right now, the database is saved on a drive shared by everyone in the company but I am the only one working on it and it isnt being used yet. Would splitting the DB to a front end and back end be the best option to allow 4 or 5 people to have access to the forms without giving them access to all of the data? All the forms do is record data and then the data will be used by other people here on the back end.

Also, if I split the DB will I still be able to make changes as I am right now and have the changes automatically update all of the front ends? So say I add a new field to one of my forms, hit save, and then walk over to the computer with the front end access, will it be updated?

Finally would it matter where I stored the backend of the DB? I don't necessarily know where it will be kept yet because I'm not the one who needs the data but would there be any issue if I kept both the front end and the back end on the same shared drive and just told the front end people where the front end is?

Security isn't much of a concern and them editing data isn't really a concern the goal is just simplicity. Rather than have the workers who enter data have to go through Access and try to find the forms on their own I'd just like them to be able to open up the front end and see the 5 or whatever forms and that's all. Thanks for the help again!

[JoeM]

Would splitting the DB to a front end and back end be the best option to allow 4 or 5 people to have access to the forms without giving them access to all of the data?

Yes.

Also, if I split the DB will I still be able to make changes as I am right now and have the changes automatically update all of the front ends?

The way that I do it is I have them run the front-end from a batch file. Here is what my batch file does:
- Goes out to the network and pulls down my latest working front-end copy, and copies it to their local (C) drive
- Opens the copy from their local drive

So, whenever I have an update, all I have to do is drop a new copy of the front-end to this network drive that the batch file references.
So every time the user accesses the database, it is pulling down a fresh copy of the front-end.
This makes pushing out updates very easy (they don't even know it is happening).

Finally would it matter where I stored the backend of the DB? I don't necessarily know where it will be kept yet because I'm not the one who needs the data but would there be any issue if I kept both the front end and the back end on the same shared drive and just told the front end people where the front end is?

Shouldn't really matter as long as they can access it (that is, the front-end they are using can see the back-end). I often save them on the same drive, though typically not in the same folder as the front-end (to avoid any accidental deletion or confusion). I put it in a place where they wouldn't have reason to normally go to.

Hope that helps!

[tc197]

Thank you! That helps a lot, I like the batch file idea- not sure how to do it but at least I have something to start with. The rest sounds good as well and I feel better about what I'm thinking of doing. Thanks again!

[JoeM]

Here is a batch file script you can use to do that. Just change the directory and database names appropriately. You may also need to change the line that opens the database, depending on which version of Office you have (may be Office 12 instead of Office 14, etc). You can just browse on your computer to see where the ACCESS.EXE files resides, and that is the path you want.

:: ***** CHECK FOR EXISTENCE OF C:\DATABASES DIR ON LOCAL MACHINE *****
IF EXIST C:\Databases GOTO SKIPMDC
MD C:\Databases

:SKIPMDC
:: ***** DELETE CURRENT VERSION
del "C:\Databases\MyAccessDBName_FE.accdb" /q
:: ***** COPY NEWEST VERSION FROM NETWORK
copy "F:\Databases\Front-End\MyAccessDBName_FE.accdb" "C:\Databases\MyAccessDBName_FE.accdb"

:: ***** OPEN DATABASE
"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE" "C:\Databases\MyAccessDBName_FE.accdb"

EXIT

Just name the batch file with a ".bat" extension. I usually store it on the Network, and put a short-cut to it on people's desktops. Then all they need to do is click the short-cut to run the database.

[tc197]

Here is a batch file script you can use to do that. Just change the directory and database names appropriately. You may also need to change the line that opens the database, depending on which version of Office you have (may be Office 12 instead of Office 14, etc). You can just browse on your computer to see where the ACCESS.EXE files resides, and that is the path you want.

:: ***** CHECK FOR EXISTENCE OF C:\DATABASES DIR ON LOCAL MACHINE *****
IF EXIST C:\Databases GOTO SKIPMDC
MD C:\Databases

:SKIPMDC
:: ***** DELETE CURRENT VERSION
del "C:\Databases\MyAccessDBName_FE.accdb" /q
:: ***** COPY NEWEST VERSION FROM NETWORK
copy "F:\Databases\Front-End\MyAccessDBName_FE.accdb" "C:\Databases\MyAccessDBName_FE.accdb"

:: ***** OPEN DATABASE
"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE" "C:\Databases\MyAccessDBName_FE.accdb"

EXIT

Just name the batch file with a ".bat" extension. I usually store it on the Network, and put a short-cut to it on people's desktops. Then all they need to do is click the short-cut to run the database.

Thanks for giving me the code, I don't know much of anything about it though and what do you mean by the ACCESS.EXE path? We have Office 2003 which I believe is Office 11. So....

:: ***** CHECK FOR EXISTENCE OF C:\DATABASES DIR ON LOCAL MACHINE *****
IF EXIST C:\Databases GOTO SKIPMDC
MD C:\Databases

:SKIPMDC
:: ***** DELETE CURRENT VERSION
del "C:\Databases\MyAccessDBName_FE.accdb" /q
:: ***** COPY NEWEST VERSION FROM NETWORK
copy "F:\Databases\Front-End\MyAccessDBName_FE.accdb" "C:\Databases\MyAccessDBName_FE.accdb"

:: ***** OPEN DATABASE
"C:\Program Files\Microsoft Office\Office11\MSACCESS.EXE" "C:\Databases\MyAccessDBName_FE.accdb"

EXIT


Do I change what is highlighted?

[JoeM]

See if you can browse out to: C:\Program Files\Microsoft Office\Office11\MSACCESS.EXE
If you can, and that is where the MSACCESS.EXE file exists, then you have it right.

[tc197]

I think mine is C:\Program Files (x86)\Microsoft Office\OFFICE11\MSACCESS.EXE

yours didn't work right but I found the .exe under the (x86) one.

Using this do I then have

:: ***** CHECK FOR EXISTENCE OF C:\DATABASES DIR ON LOCAL MACHINE *****
IF EXIST C:\Databases GOTO SKIPMDC
MDC:\Databases

:SKIPMDC
:: ***** DELETE CURRENT VERSION
del "C:\Databases\MyAccessDBName_FE.accdb" /q
:: ***** COPY NEWEST VERSION FROM NETWORK
copy "F:\Databases\Front-End\MyAccessDBName_FE.accdb" "C:\Databases\MyAccessDBName_FE.accdb"

:: ***** OPEN DATABASE
"C:\Program Files (x86)\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\Databases\MyAccessDBName_FE.accdb"

EXIT


and is the C:\Databases\MyAccessDBName_FE.accdb ​the folder where the database itself is saved?

EDIT

Sorry for the very basic questions but can I just make sure I understand the big picture?

So the database is saved on my local computer. When I click the shortcut to open the database it's going to check if it is up to date. If it is up to date it opens. If it is not up to date it is going to delete the old one from my local computer and find the latest one from the network. It will then download the latest one from the network back to my local computer, and then open it on my local computer. Is that the basic idea behind this?

And on my end (the person making changes) anytime I want to make a change I replace the old copy of the front end database that is on the network, with the new copy I made changes to?

Therefore the code is going something like this:

:: ***** CHECK FOR EXISTENCE OF C:\DATABASES DIR ON LOCAL MACHINE *****
IF EXIST C:\(this is the folder that contains the database) GOTO SKIPMDC
MDC:\(also the folder that contains the database)

:SKIPMDC
:: ***** DELETE CURRENT VERSION
del "C:\(this is the folder and the database itself)" /q
:: ***** COPY NEWEST VERSION FROM NETWORK
copy "F:\(this is the network folder and database itself)" "C:\(this is the local folder and database itself)"

:: ***** OPEN DATABASE
"C:\Program Files (x86)\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\Databases\MyAccessDBName_FE.accdb"

EXIT

Am I on track here?

[JoeM]

It is actually not checking to see if the database is up-to-date, it is just checking to see if you already have a C:\Databases directory, and if not, it will create one. This is needed for the first time a person uses the database (saves you the step of manually having to create the directory on their computer).

It is copying down a fresh copy of the front-end database every time.

[tc197]

It is actually not checking to see if the database is up-to-date, it is just checking to see if you already have a C:\Databases directory, and if not, it will create one. This is needed for the first time a person uses the database (saves you the step of manually having to create the directory on their computer).

It is copying down a fresh copy of the front-end database every time.

Awesome that's even better than I thought. I'm still waiting to find an answer on my other thread https://www.accessforums.net/access/...tml#post234176 before I split the database because that is the last formatting/change I want to make to the forms and such. As soon as I figure out a solution to that I'll split the DB and report back on how successful this code is.

One last dumb question I have is where do I put this code? What sort of program do I save it in that will let me create a shortcut from it and run it? Thank you so much for the help!

[JoeM]

You just paste the code in a text file (can be created using NotePad or WordPad). The key is to save with an extension of ".bat". That is what tells it it is a batch file and should run automatically when clicked.

Once you have created and saved the file, if you browse to it in Windows Explorer, you can right-click on it and select "Create Shortcut". You can then either put a copy of the shortcut on people's Desktops manually, or email them the shortcut and tell them to save it to their Desktops.

[tc197]

Thank you so much JoeM, that's the exact solution I needed. I'll let you know if anything comes up!

[tc197]

So I split the database, changed the wording for the program to match my setup but the program isn't creating the directory and from there can't find the database to open. Also you were using .accdb but my database is a .mdb file. Not sure if that impacts anything? Here's what I tried.

:: ***** CHECK FOR EXISTENCE OF C:\DATABASES DIR ON LOCAL MACHINE *****
IF EXIST C:\Databases GOTO SKIPMDC
MDC:\Databases


:SKIPMDC
:: ***** DELETE CURRENT VERSION
del "C:\Databases\Cell-Tracking-Database-Front-End.mdb" /q
:: ***** COPY NEWEST VERSION FROM NETWORK
copy "T:\Cell-Tracking-Database\Database\Cell-Tracking-Database-Front-End.mdb" "C:\Databases\Cell-Tracking-Database-Front-End.mdb"


:: ***** OPEN DATABASE
"C:\Program Files (x86)\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\Databases\Cell-Tracking-Database-Front-End.mdb"


EXIT

[tc197]

C:\Users\tom\Desktop>IF EXIST C:\Databases GOTO SKIPMDC


C:\Users\tom\Desktop>MDC:\Databases
The filename, directory name, or volume label syntax is incorrect.


C:\Users\tom\Desktop>del "C:\Databases\Cell-Tracking-Database-Front-En
d.mdb" /q
The system cannot find the file specified.


C:\Users\tom\Desktop>copy "T:\Cell-Tracking-Database\Dat
abase\Cell-Tracking-Database-Front-End.mdb" "C:\Databases\Cell-Tracking-Database
-Front-End.mdb"
The system cannot find the path specified.
0 file(s) copied.


C:\Users\Desktop>"C:\Program Files (x86)\Microsoft Office\OFFICE11
\MSACCESS.EXE" "C:\Databases\Cell-Tracking-Database-Front-End.mdb"




Here's the other thing that pops up when I run the code.

[JoeM]

Looks like you have a typo, namely, you dropped a space in one of your lines.

MDC:\Databases

should be

MD C:\Databases

[tc197]

Wow. It works perfect now. One little space. Thank you so much and sorry for that!