organizational ideas

[SaviorSix]

Hello all, glad to be here

I wanted to bounce this idea off the members in the forum, its more along the lines of managing multiple databases rather than individual design.

I am a recent incumbent at a position where there are many individual access database files for the department. (Each DB is unique as are the users, usually) I am trying to flesh out ideas on how to consolidate these access files, so there is one central 'hub', where users could log on, and then select the study or project associated with that particular database, and then the DB be opened for them to begin working, rather than the current system, in which there is a rabble of access mdb files in multiple folders

any ideas are appreciated

 

[Mike375]

What about shortcuts on the Desktop. They will work across a network and can open the db from the starting form or a start up macro etc.

If you are not familiar with making shortcuts......just right click on a form name or whatever and in the menu you will see Create Shortcut.

 

[SaviorSix]

What about shortcuts on the Desktop. They will work across a network and can open the db from the starting form or a start up macro etc.

If you are not familiar with making shortcuts......just right click on a form name or whatever and in the menu you will see Create Shortcut.

Thank you for the response,

what I am looking for though, is more along the lines of:
Is it feasable to design one database which opens other databases. In other words, this DB would act as a sort of 'switchboard', which will allow certain users to access other mdb files.
I didn't know if this is a far fetched idea

 

[Mike375]

Yes.

I will tell you how I do it but others will have a nicer way.

Are you familiar with DOS batch files. If not they are very simple for this sort of task. A batch file will open the data base and the batch file can be run by a macro from another data base. A batch file will open the data base to the point where the db window opens. To go further than that it is done by Tools/Startup or by an autoexec macro in the data base that is opened

Another way is to make a little data base with a form. In form design go to Insert of the tool bar and HyperLinks. That lets you put a link on the form to open various files.

That might be closer for what you want than batch files. I use the batch file because I am getting it do other things at the same time.

So the answer is a "definite yes" and there are plenty of ways of going about it.

 

[SaviorSix]

Yes.

I will tell you how I do it but others will have a nicer way.

Are you familiar with DOS batch files. If not they are very simple for this sort of task. A batch file will open the data base and the batch file can be run by a macro from another data base. A batch file will open the data base to the point where the db window opens. To go further than that it is done by Tools/Startup or by an autoexec macro in the data base that is opened

Another way is to make a little data base with a form. In form design go to Insert of the tool bar and HyperLinks. That lets you put a link on the form to open various files.

That might be closer for what you want than batch files. I use the batch file because I am getting it do other things at the same time.

So the answer is a "definite yes" and there are plenty of ways of going about it.

Ahha! Yes I was thinking of using the hyperlink method, which would link to a seperate mdb file, I just wasn't sure if this was used often. Ideally id want the operation to be:
view form with links to dbs -> click link -> current db closes -> linked db opens

 

[HHUK]

You could import all those databases into a big, new database. Tidy it all up, give all the forms a common theme making it all look professional.

Split it to a front-end/back-end system and work that way.

Run the user level security wizard and make usernames/passwords for everybody. Then you can limit what people can do, also limit which areas of the database people can work in.

If you have a huge number of users (20+) working on it then maybe you would be better off "hyperlinking", setting up a form with buttons to open up the other database for the sake of keeping the data safe from corruption.

 

[Mike375]

What you do is:

1) Make a batch file that opens one of the data bases

2) Make a little macro (run from Command Button or behind label etc) that has 2 action lines

RunApp............this runs the .bat file that opens the other data base
Quit....it then closes the current data base

You will just see them kind of swap on the screen.

You could make a mini data base to contain the running of the .bat file and quit for each of these data bases.

If you are not familiar with making a .bat file then the following will do it for you. Also, best if they are loose on the c:\ drive

Go to the DOS prompt which will probaly display something like

c:\Documents and Settings\Something Else

At the end of whatever didplays type CD\ and then the return key. That will take the display to the roor C: drive. It will display as c:\>

Next to c:\> type

Copy con abc.bat.....then hit return key and on next line type
DIR........then hit return key and press
Ctrl and Z and then return key

That will create a file called abc.bat

Find the file in Exlporer and right click on it and then click Edit. That will open Notepad and in the above the exampe it will just have DIR in there. We don't need this as we only wanted to create a .bat file.

Remove the DIR and type on the first two lines
C:
CD\

You then type in the path for the data base. I will give you a couple of examples that apply to my system, both local and across network

Viper.mdb....That will open that db if it is on the C:\

\ViperDaily\Viper13334-26032008.mdb.....That opens that db on the local computer when the db is in the folder ViperDaily

\\378Wby\Mydocuments\ViperNetwork.mdb.....This opens the db in mydocuments on a computer on the network and where the computer name is 378Wby and mydocuments is the share name

If you have folders or db names that are not one word such as My Documents or Johns Data Base they need to be "My Documents" or the file won't run.

Basically .bat files run all DOS commands and as such they are great for this sort of thing, copying files, renaming etc and etc.

 

[Mike375]

You could import all those databases into a big, new database. Tidy it all up, give all the forms a common theme making it all look professional.

My own data base does that, lost of them in one. However, one problem he may have is if he has a lot of queries, macros and forms with the same names in the different data bases. That will be deinitely the case if they have been made using the default names.

 

[SaviorSix]

You could import all those databases into a big, new database. Tidy it all up, give all the forms a common theme making it all look professional.

Split it to a front-end/back-end system and work that way.

Run the user level security wizard and make usernames/passwords for everybody. Then you can limit what people can do, also limit which areas of the database people can work in.

If you have a huge number of users (20+) working on it then maybe you would be better off "hyperlinking", setting up a form with buttons to open up the other database for the sake of keeping the data safe from corruption.

HHUK, in this case there would literally be hundreds of tables and forms, and at least a dozen people accessing them, with more to be added in the future, so ill most likely check out the hyperlinking

What you do is:

1) Make a batch file that opens one of the data bases

2) Make a little macro (run from Command Button or behind label etc) that has 2 action lines

RunApp............this runs the .bat file that opens the other data base
Quit....it then closes the current data base

You will just see them kind of swap on the screen.

You could make a mini data base to contain the running of the .bat file and quit for each of these data bases.

If you are not familiar with making a .bat file then the following will do it for you. Also, best if they are loose on the c:\ drive

Go to the DOS prompt which will probaly display something like

c:\Documents and Settings\Something Else

At the end of whatever didplays type CD\ and then the return key. That will take the display to the roor C: drive. It will display as c:\>

Next to c:\> type

Copy con abc.bat.....then hit return key and on next line type
DIR........then hit return key and press
Ctrl and Z and then return key

That will create a file called abc.bat

Find the file in Exlporer and right click on it and then click Edit. That will open Notepad and in the above the exampe it will just have DIR in there. We don't need this as we only wanted to create a .bat file.

Remove the DIR and type on the first two lines
C:
CD\

You then type in the path for the data base. I will give you a couple of examples that apply to my system, both local and across network

Viper.mdb....That will open that db if it is on the C:\

\ViperDaily\Viper13334-26032008.mdb.....That opens that db on the local computer when the db is in the folder ViperDaily

\\378Wby\Mydocuments\ViperNetwork.mdb.....This opens the db in mydocuments on a computer on the network and where the computer name is 378Wby and mydocuments is the share name

If you have folders or db names that are not one word such as My Documents or Johns Data Base they need to be "My Documents" or the file won't run.

Basically .bat files run all DOS commands and as such they are great for this sort of thing, copying files, renaming etc and etc.

Thats a pretty sweet idea,
right before I read this, I was checking out the VB command "application.followhyperlink", it seems to do a similar function that this batch file would accomplish (?)

 

[Mike375]

Thats a pretty sweet idea,
right before I read this, I was checking out the VB command "application.followhyperlink", it seems to do a similar function that this batch file would accomplish (?)

As I mentioned at the start someone else might have a better way.

Basically the .bat file will do the lot. However, its disadvantage is it can only do DOS commands.

The .bat file with lots of action lines is like a macro or code in that it completes each one before going to the next line. However, things such as db opening is independent of it.

If you are mucking about with all these dbs here is hand set of .bat lines I use for back up purposes. The idea is to copy the db to a folder for tansfer purposes and rename the db in that folder and then copy to whereever. the .bat file will do all the copying. You rename the db with a timedate in the name that way you never rub one db out with another

The db in my previous post......Viper13334-26032008.mdb..is a result of the following. The date is for Australia (UK the same?)

set Day=%Date:~4,2%
set Mth=%Date:~7,2%
set Yr=%Date:~10,4%
set Hr=%Time:~0,2%
set Min=%Time:~3,2%
set Sec=%Time:~6,2%

ren *.mdb Viper%Hr%%Min%%Sec%-%Day%%Mth%%Yr%.mdb