split database question ... will have multiple back end databases

[SuzyQ]

I currently have multiple databases that I need to split to consolidate

Currently I have the following database
Roads
TimeCards
AssetsAndMaterials
Inventory

Roads has tables such as tbldistrict, tblRoad, tblSurfaceType etc
TimeCards has tables such as tblEmployee, tblTimeSheet, tblJobCodes,
tblAdminCodes etc
Assets has tables such as tblBuildings, tblBridges, tblCulverts,
tblMaterials, tblMaterialsInventory etc
Inventory as tables for equipment and equipment type etc.

Each one of these databases has it's own tables, forms, queries, modules,
reports etc and each one also has links to multiple others

I would like to keep the data in the specific databases that they are
currently in because the data in logically grouped into the required tables,
however because of the multiple cross linkages across the different databases
to run reports, forms etc I would like to split out all the queries, reports,
forms, and modules from each of the databases into a single front end
database. I have split databases before, and generally design my databases
split to begin with however these databases were inherited as is. I have
never split databases before that involved multiple backend databases. My
question is how should I do this? I tried exporting into a blank database as
a test with one of the databases, but that didn't work. So I guess my usual
splitting method of making a copy and then deleting unwanted objects is the
way to go, but that only takes care of one of the splits. Do I then export
forms etc from the other databases into the new front end from the copy/split
and go from there or is there a better way?

 

[golfinray]

I would import all the tables into a new Access database and put it on your
LAN, or wherever users will access it. Then build one front end with all
tables linked. You will probaby have to create different permissions with
Access security to give each user what they need. An alternative to that is
to have a separate frontend for each user with only the tables they need
liked to the backend. Access will do multiple backends, no problem, but
keeping up with who has what and who has links to what and maintaining your
links might be a problem. You should also get Tony Toews (www.granite.com)
automated FE updater so that any time you unpdate an FE you don't have to go
load it on every computer.

 

[Armen Stein]

I tried exporting into a blank database as
a test with one of the databases, but that didn't work. So I guess my usual
splitting method of making a copy and then deleting unwanted objects is the
way to go, but that only takes care of one of the splits. Do I then export
forms etc from the other databases into the new front end from the copy/split
and go from there or is there a better way?

Take a copy of the most complicated database and remove the tables.
Rename it something generic. This will be your new front-end.

Link to the tables in all your back-ends.

Import all the forms, reports, etc. from the *other* front-ends.
You'll need to watch for duplicate object names, code, etc. You'll
need to have a single startup routine that handles everything. You'll
need a consolidated navigation scheme. Etc.

To manage all those linked tables, you're welcome to use our free J
Street Access Relinker at:
http://www.jstreettech.com/downloads

It handles multiple Access back-end databases, ignores ODBC linked
tables, and can automatically and silently relink to back-end
databases in the same folder as the application (handy for work
databases or single-user scenarios). There's a ReadMe table with
instructions.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[SuzyQ]

security is not an issue because the server security handles that, anyone who
has access to the folder has access to all the data, all of these databases
are within subfolders of the same root folder in which everyone will have
access and that is ok.

As far as updating the front end automaticall, I already have a routine that
I use, probably downloaded from the same source as you mention many years
ago.

Thanks

 

[SuzyQ]

Thank you, your suggestion is kind of the way I was headed. You confirmed my
thoughts. Thanks again.

 

[SuzyQ]

OK, I've created a new FE - haven't deleted anything out of the BE's yet.

Problem with FE - error - network connection may have been lost
I found this kb article

http://support.microsoft.com/default.aspx?scid=kb;[LN];304548

and because I am running Access 2000 and I found the dll mentioned in the
article I downloaded service pack 3. I tried to install, but got an error
message stating that it couldn't find the Access 2000 software installed on
my system. So what is going on?

 

[SuzyQ]

The reason why it gave the the software not found message could be because
the article applies to MS Office 2000 Standard and it is MS Office 2000
Professional that is installed on this system. Which also may mean that this
KB article does not apply to my situation, but if that is the case, what is
the problem and how can I resolve it. Thanks

 

[Tony Toews [MVP]]

I would like to keep the data in the specific databases that they are
currently in because the data in logically grouped into the required tables,

I disagree. Put all the tables in one database. Some of my clients
databases have over 100 tables each and up to 800K records in a few of
the tables.

Also you will then have relational integrity between the tables.

Tony

 

[SuzyQ]

My thinking of leaving the data in multiple databases is that it would be
easier to manage the over 100 tables if they are grouped with tables of
similar themes. Database integrity is being handled within each database...
and some of my databases are nearing the access file limit size of 2G.
Combining them would go over the limit. The reason for the single front end
is that my users want a single user interface rather than having close and
open multiple applications throughout the day.

I have the database split now. I'm working on the creating the interface
that ties all the forms/reports together now.

 

[John W. Vinson]

My thinking of leaving the data in multiple databases is that it would be
easier to manage the over 100 tables if they are grouped with tables of
similar themes. Database integrity is being handled within each database...
and some of my databases are nearing the access file limit size of 2G.
Combining them would go over the limit. The reason for the single front end
is that my users want a single user interface rather than having close and
open multiple applications throughout the day.

Your application is crying out for migration to SQL/Server or some other
client/server database. This will give you the scale you evidently need, and
get around the 2Gbyte limit.

SQL/Express is free but limited to 2G also, but bigger versions of SQL are
becoming very affordable.

 

[SuzyQ]

County is in a spending freeze at the moment so I have to make do. I agree
however. FYI our server size is only 450G and it is also maxed out!!!!

 

[John W. Vinson]

County is in a spending freeze at the moment so I have to make do. I agree
however. FYI our server size is only 450G and it is also maxed out!!!!

SERVER size!!!? I haven't seen a LAPTOP that small in a long while...!

Ah, the joys of working for local government. Good luck.

 

[Armen Stein]

SQL/Express is free but limited to 2G also, but bigger versions of SQL are
becoming very affordable.

Actually, SQL Express 2005 has a 4GB limit:
http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx

Being free, it would seem to be a good fit for this scenario. But
making the application perform well might take some extra work.

I've written a slideshow on techniques for using Access as a
client-server front-end to SQL Server databases. It's called "Best of
Both Worlds" at www.JStreetTech.com/Downloads. It includes some
thoughts on when to use SQL Server, performance and security
considerations, concurrency approaches, and techniques to help
everything run smoothly.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[SuzyQ]

I'll keep that in mind. Thanks. You have made some good points on the
presentation in favor of migrating. My problem is that I work for this place
only four months a year - I set everything up just right - then while I'm not
there my employer decides he wants a report that's not there or a table or
whatever and just adds it - or worse yet - creates a new database for just
one table - he doesn't follow the naming conventions that I've set up, he
edits directly in the tables. I've talked to him about it, but I can't take
design rights away from him because he does need them when I'm not there - I
just wish he would try a little more logic in what he does.

 

[elmer jackson]

keep what in mind Suzy q

I'll keep that in mind. Thanks. You have made some good points on the
presentation in favor of migrating. My problem is that I work for this
place
only four months a year - I set everything up just right - then while I'm
not
there my employer decides he wants a report that's not there or a table or
whatever and just adds it - or worse yet - creates a new database for just
one table - he doesn't follow the naming conventions that I've set up, he
edits directly in the tables. I've talked to him about it, but I can't
take
design rights away from him because he does need them when I'm not there -
I
just wish he would try a little more logic in what he does.

 

[SuzyQ]

Suggestions to migrate to SQLserver

keep what in mind Suzy q