Cetralised Database with various sections

[Richie27]

Hello Everybody,



I have an access database which will basically be 4 databases in one access project - main menu will have menu like this:

STAFF
HR
FINANCE
CLIENTS

They will all have different schemas tables, forms etc etc

I am currently thinking the best thing to do would be to put a letter at the start of the table names to distinguish one from the other.

Like tblCLTsDetails for clients and tblFEDetails for finance tables and forms etc.

Does anyone know of a better way to separate the databases without having to put specific letters inside in the table and form names?

Cheers.

Any inspiration would be super.

I'm bad, I always wonder if there is a better way to do things!!!

[jzwp11]

Will the tables in the 4 databases be structured the same? In other words will tblAClients, tblBClients, tblCClients and tblDClients all have the same field names?

[Richie27]

Will the tables in the 4 databases be structured the same? In other words will tblAClients, tblBClients, tblCClients and tblDClients all have the same field names?

Ya, they will be pretty similar in terms of their field names and table names, though there will be a certain amount of bespoke stuff as well.

I just thought it would be handy if there was someway to physically separate all things relating to each individual database in terms of being able to view things in the navigation pane.

My first inclination was a lettering system as described above but then I thought maybe there might be some access pro who might know a better way.

Hello again jzwp! I guess this forum is your version of Sudoko or Crosswords!

I might try this as a pastime too - if I ever get good at it! ;-)

10/10 Mate

[jzwp11]

Ya, they will be pretty similar in terms of their field names and table names, though there will be a certain amount of bespoke stuff as well.

If the structures are essentially the same, have four sets of tables, queries etc. violates normalization rules and creates quadruple the amount of work for you. I would try to commonize and use a field to differentiate the four types whether they are four client types or whatever.

Can you provide the structures of the tables you intended to use? Then we can figure out how to do this within 1 set of tables, queries etc.

[Richie27]

If the structures are essentially the same, have four sets of tables, queries etc. violates normalization rules and creates quadruple the amount of work for you. I would try to commonize and use a field to differentiate the four types whether they are four client types or whatever.

Can you provide the structures of the tables you intended to use? Then we can figure out how to do this within 1 set of tables, queries etc.

Not really four client types.

One database will relate to client files, another to finance files and another to human resource files and there will be slight differences in the underlying structure.

It will really be four separate databases, each of which will be accessed via a single front end interface.

I fully appreciate your logic and I had thought of doing it that way at first - it scares me a bit though!!

I am but a novice at this game.

Not 100% sure of the table structure as yet as same has yet to be finalised with client but will keep you posted.

Kind of exciting/challenging doing what you suggested though I must say!

[jzwp11]

If they are strictly separate databases, you would create the 4 separate database files. These files would only have the tables. These four files would go on your server.

You would then create a fifth database file that will hold all of your forms, queries, reports, macros & modules/code (no tables only links to the tables in the other 4 files). This would be your front end. Each user would get a copy of this fifth database & would place it on their local hard drive.

[Richie27]

If they are strictly separate databases, you would create the 4 separate database files. These files would only have the tables. These four files would go on your server.

You would then create a fifth database file that will hold all of your forms, queries, reports, macros & modules/code (no tables only links to the tables in the other 4 files). This would be your front end. Each user would get a copy of this fifth database & would place it on their local hard drive.

Crickey - who needs college, courses and lecturers when there's guys like you around!

I didn't even know you could do that!!

[jzwp11]

Give it a try and let us know how it works out for you.

[Richie27]

Give it a try and let us know how it works out for you.

Hmmm....I still think it would be better to have four separate databases myself but they want them all under one roof so to speak. Methinks it makes for a lot of clutter though.

They also want the database(s) ported into sql server so I wonder how that will work......with having four separate database files.....

Would it be totally evil to go with my original idea of flagging the tables with letters depending on which database they represent?

Hmmm.....to be continued!!

[jzwp11]

I'm not too familiar with SQL Server but I know that you can use an Access front end and have the tables linked in from SQL Server just as if the back end were in Access

[Richie27]

I'm not too familiar with SQL Server but I know that you can use an Access front end and have the tables linked in from SQL Server just as if the back end were in Access

I've done it a couple of times. Apparently it's more secure.