Controlling multiple MDBs from one source

[BTaylor]

I'm sorry if the solution to this is obvious, but I am new to Access database
development. Is it possible to alter forms and reports on several database
files without having to update the MDB files themselves?

We have a requirement for upwards of thirty databases with identical table
structures, and it would be good if users could just open them in Access, but
manually updating so many MDB files would be tedious and error-prone.

We could write an executable to handle the data, but this means setting up
reporting structures which are already available using the Access software.
Some kind of template which can be altered after data creation would be ideal.

 

[Graham R Seach]

...without having to update the MDB files themselves.
No.
You could create a new database that contains all the code necessary to
update all the other databases. As long as this new database can "see" all
the others, and the user operating it has sufficient permissions on the
target computers, it could use automation to make any changes you want, but
those changes would have to be explicitly coded.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[BTaylor]

Thanks,

I think we will pursue the executable approach - the report layouts will not
be too complicated. It is a pity that Access doesn't allow external layout
files for displaying and reporting.

 

[Larry Daugherty]

If you have several separate BackEnds (tables only) with identical
table structures then they should really be in a single BackEnd and an
MDE copy of the FrontEnd (all the Forms, Queries, Reports & Modules)
placed on each user's PC. I'm sure that the separate BackEnds are to
segregate the data in some way. That will come down to no more than
one field in a table or so to maintain that segregation.

You would have to come forward with a lot more details for us to know
what your business need might be.

HTH
--
-Larry-
--

I'm sorry if the solution to this is obvious, but I am new to Access database
development. Is it possible to alter forms and reports on several database
files without having to update the MDB files themselves?

We have a requirement for upwards of thirty databases with identical table
structures, and it would be good if users could just open them in Access, but
manually updating so many MDB files would be tedious and error-prone.

We could write an executable to handle the data, but this means setting up
reporting structures which are already available using the Access software.
Some kind of template which can be altered after data creation would

be ideal.

 

[BTaylor]

We need separate files as the data belongs to different clients and also we
have unique sequential numbering for each data set which would be difficult
(impossible?) to maintain with a single table.

We do not see the need to alter the data structure, but types of query and
report layouts may have to change at a later date. We would want to apply
changes to all data simultaneously and if possible allow a double-click on
the data file to just run the appropriate software.

The databases will be updated from completely separate data-entry software
with no reporting or editing facilities. The data will also feed into other
programs, which is why we do not want users playing with the data structure.

I hope this gives a clearer impression of what we are trying to achieve.

 

[Larry Daugherty]

No, it doesn't clear things up for this simple and easily fuddled mind
but it's you who needs to understand what you're doing.

Also, I did not mean to imply that all of your data could or should be
contained in a single table. My assertion was that the combination of
the data would most likely require the addition of a single field in
one table in order to manage the data. If each back end currently has
several tables of normalized data then the combined data would require
the same number of tables with same entities.

As to the many BackEnd configuration; if you maintained the exact same
schema in each BackEnd you could manage and present the data from a
single FrontEnd - assuming that everything stays on the same LAN. It
would be unusual but conveniently achievable. However, if the
business rules differ between data sets or the Reporting varies by
data set then you could back yourself into a situation where the
thirty BackEnds require thirty specific FrontEnds. In that case, as
you've already heard, you will have to manually maintain each of those
FrontEnds.

HTH

 

[BTaylor]

Ok - a 2nd attempt!

We would like to separate the logic from the data to allow changes to the
logic alone - the data structure will almost certainly not have to change.

Orders from on high prohibit us from placing data for different clients in
one logical file, even in separate tables. In fact the data is entered
through different bespoke software for each client anyway - the data entry is
just one part of each system, the rest of which differs greatly from one
project to another.

It seems to be difficult to get Access to use forms, queries and reports
from an external file, so we will probably put the logic into an executable
and standardise the data structure as we have done in the past with Paradox
and DBase tables. I just liked the idea of using Access's own logic for
reporting, but it seems that it may be more trouble than it is worth for our
purposes.

If there is a separate reporting module that can use different MDB files
with the same set-up this would be another solution we could use.

 

[Larry Daugherty]

Your 1st paragraph describes a split database configuration; FrontEnd
with everything except the data and BackEnd with just the Tables and
Relationships. All professional Access developers roll out their
applications in this configuration. It is because if facilitates
changes to the business rules and the user interface without affecting
the data.

Your 2nd paragraph says that each set of data is entered by different
software and manipulated by different software which you have to use.
You go on to say: " the data entry is

just one part of each system, the rest of which differs greatly from one
project to another."

The data structure is the same but there are huge differences between
systems??

I don't understand the lament about Access not being remotely
controllable. What would you have control it? Access can be a server
to another VBA platform. A whole bunch of functionality can be put
into a library and referenced from an Access application. That
library can also be instilled as an AddIn. As a referenced library or
as an installed AddIn it could be available to any running Access
application.

The Access Report functionality requires Access to be running.

But, you go back around the circle and say you'll "standardize the
setup". If by setup you mean schema then you're right back to the
situation in which a single FrontEnd can service all of the BackEnds
for Reporting or anything else. The BackEnds and FrontEnd must all be
on the same LAN.

HTH

 

[Larry Daugherty]

You expressed another issue that went unaddressed: Double-Click on a
data file to open the program. That can be done but it violates the
idea of having no code in the back end.

It's possible to put in a chunk of functionality that, when awakened,
would call the real program to come alive with the calling file in the
cross hairs.

HTH

 

[BTaylor]

Thanks Larry,

We have 2 choices, split the functionality and data within Access, or just
use Access files for data storage and handle the logic ourselves.

I have commented below - it's the splitting of data and logic within Access
that I haven't worked out how to do.

 

[Larry Daugherty]

Ha! The splitting is a piece of cake. There is even a Split function
provided within Access if you don't trust the elder children to guide
you.

At its simplest you start by copying your application. Name one copy
Front and name the other Back. Open Back. Delete every object in the
databse window except the tables. Close Back. Open Font. Delete all
of the tables. On the File menu select Get External Data. Link.
Navigate to Back. Select all tables. Go back to the Tables view in
the database window and you'll see all of your tables - linked this
time. You are done.

Note that you'll only need to do this once. Do it after you're pretty
sure of your data design. Then you can copy Back 29 times, giving it
a unique name each time. The back end can maintain referential
integrity.

What is the logic to which you refer?

All files on the server is a very good thing.

Now I understand. The data portion across the systems is/will be a
standardized schema but comprises only a part of the whole of each
customer's data package. If you're passing the data on to Accounting
what massaging must you do? Or is it that you're gathering the
resources for Reports, etc. to pass on to Accounting as well?

Access supports SQL but its own dialect. SQL Server Express might
fall nearer the norm.

Any contemplated data massaging and presentation would be from the
Front End which will be separate from the data. I wouldn't know how
to qualify the querying capabilities of Access. It can do more than I
know how to ask. That's not much of a measure. Its also ODBC
compliant.

As mentioned before: if your schemas are identical then you can
manage and Report on all of your customer data sets from a single
Access Front End.

HTH
--
-Larry-
--

Thanks Larry,

We have 2 choices, split the functionality and data within Access, or just
use Access files for data storage and handle the logic ourselves.

I have commented below - it's the splitting of data and logic within Access
that I haven't worked out how to do.

out how to do this in Access. We are not Access experts and as this
part of our system is quite small, it doesn't seem worthwhile going
too deep into it. If the front and back ends can be split simply and
easily, we would do it. The files are all kept on the main server on
our LAN, so data paths are constant, if this helps.what each program handles - the rest of the data is in text and binary
files which do not directly impinge on the databases. The Access data
then goes on to our accounts department for processing, which is why
we are standardising the database format for each project.here!) - we want to control the look and feel of the data from outside
the actual data files. Our applications are not written in VBA, so we
they not be as tightly integrated as a VBA program, but we only need
flexible querying, which is quite manageable if we take this route.Yes, but it would be nice for each MDB to be linked to the same logic
somewhere else - the whole structure of Access may not allow this - I
haven't done much in Access before, so I was asking the question.

 

[BTaylor]

That's exactly what we need - thanks for your patience, Larry.

Now I'm off to play with lots of data files