How to improve a complex project split between three access files?

[aireq]

I'm currently working on my first relational database project in Access 2007.
Unfortunately it is far from a "typical" database project, so it is rather
challenging.

We are using a software application, Autodesk Revit, that can export project
information to a database through ODBC. We then want to process this
information to parse out various codes, as well as filter and union various
tables. Finally we want to take this "processed" information and display it
on reports using different filters and grouping. This is not a typical
beginning database project because I have no control over the tables that are
being exported through ODBC. So everything that I am doing is through a ton
of nested queries.

In addition, we want to be able to use this tool on more than one project so
I do not want to have any file paths or project names hardcoded into the
database. Plus we want to make it easy to point this tool at an entirely
different project file to generate new reports. I'm trying to make it as
generic as possible so it can easily be applied to another project.

The biggest problem I've having is the logic to process the tables exported
through ODBC is just too complex for Access and if I try to put everything in
one ACCDB file I start getting errors saying I can't connect to anymore
databases, or access has run out of resources.

So to fix this I've split the project up into three separate databases.
First I have an "export" database that contains only the tables exported
through ODBC. Then I have a "processing" database that links to a few tables
in the "export" database, and processes these into four "master" queries
through a bunch of nested queries. These "master" queries are then exported
to tables in a "reporting" database through a VBA script that essentially
runs a series of Make Table queries. The reporting database then groups and
filters the content of these tables through more queries which eventually
generate reports.
I have this whole thing working now, but it is a very convoluted process to
go from updating something in our project to viewing an updated report. To
view an updated report here's what needs to be done

1) Make change to the project file.
2) Export to "export" database through ODBC
3) Open the processing database
4) Update the tables linked to the "export" database through the linked
table manager
5) Export the master queries to the reporting database through a form where
the user can enter the path to the "reporting “database
6) Open the reporting database.
7) Open the report to be viewed.

I would like any suggestions on how I could possibly consolidate this
project into one or two MDB files, or automate the current update process
described above. Any other suggestions on how I can improve any of this would
be greatly appreciated.

Eric

 

[Klatuu]

Eric,
What you are describing is complex. I don't think any of us could provide a
really specific answer to your question. That is because we can't see the
data, the relationships, and we don't know the detail of the design.
What I can tell you is that the user of make table queries will contribute
to database bloat. It is more efficient for a couple of reasons to use
predefined tables and delete the old data out of those table before running
the append queries that load them.

As to combining the 3, that should not be a problem. It could be as simple
as importing the objects from the other two into one.

There is no problem connectiong to multiple back end databases from one
front end. It is also okay to mix types, for example, you can link to your
ODBC and to mdb back ends from the same front end. I actually did one
application where we linked to SQL Sever via ODBC for some of our data, to
an mdb back end for other, and linked to some FoxPro tables to transfer to a
state agency.

 

[david]

There does seem to be anything wrong with your use of 3 files: two temp
files and one control file. It does look like you could put all of your temp
tables into one database if you wanted to, but I'm not sure there would be
any advantage.

This surprised me a bit:

The biggest problem I've having is the logic to process the tables
exported
through ODBC is just too complex for Access and if I try to put everything
one ACCDB file I start getting errors saying I can't connect to anymore
databases, or access has run out of resources.

That's a miss-statement: nothing you have described is too complex
for Access. In fact it actually sounds very simple. "The fault, dear
Brutus, is not in our stars" (JC I,ii,140-141)

into one or two MDB files, or automate the current update process

I use VBA to automate the process. For a start there are many versions
of this on the Web: there is no reason to use the linked table manager:

4) Update the tables linked to the "export" database through the
linked table manager

(david)

 

[Larry Linson]

I'm currently working on my first relational database
project in Access 2007. Unfortunately it is far from a
"typical" database project, so it is rather challenging.

Not "slamming" you, but, I'd agree with Klatuu that what you describe does
not sound so exceptionally complex as to require multiple databases. I
suspect it may well be just over-complex for someone either working on their
first RDB project in any development environment or, particularly Access
2007 (it appears that Microsoft's decision to adhere to a common Service
Pack for all Office has left Access with known bugs "unfixed" for far longer
than in earlier versions).

It is not clear to me why you believe that using the tool on more than one
project would require different file paths (implying a different set of
tables, or different back-end database for each project) or project names to
be _hard-coded_ into the database. Surely, you can include a Project Table,
and allow your user to select the project on which the user is working, and
identify the data (somewhere in your processing, you should be able to add
Project ID) by project.

If, for some reason, it _is_ necessary that each project be in a separate
back end, you'll find an example of re-linking tables at start-up in a
sample database called "Developer Solutions" that is downloadable from
Microsoft, using links and information you'll find in Knowledge Base article
# 248674, http://support.microsoft.com/kb/248674/en-us. You also will find
an example of using the Windows Common Dialog to allow a user to scan for
and select a filename, at http://www.mvps.org/access/api/api0001.htm.
Neither of these were specifically designed for Access 2007, but both of
them still work with Access 2007. And, if you must use temporary tables, you
can reduce or avoid the need for frequent Compact and Repair, by using a
technique of creating a temporary database, creating and using the temporary
tables in the temporary database, unlinking and then deleting the temporary
database when you are done with it. There's an example at MVP Tony Toews'
site, http://www.granite.ab.ca/accsmstr.htm.

If you are attempting to implement the project you describe without a
more-than-elementary competency with Access Visual Basic for Applications
(VBA) and without an excellent understanding of using Access as an end user,
then it is, very likely, an "over-ambitious beginning". When I first began
using Access, I had over thirty-five years of broad experience in data
processing, including various database products and with a very good
knowledge of Visual Basic from using the separate classic VB product, and
would have been reluctant to just "jump feet-first into" a project of this
nature. The first "caveat" is because you will need to use VBA (and DAO),
and you really shouldn't be burdened with a learning curve for VBA as well
as learning how to do some admittedly-advanced tasks in Access. The second
is because much of what we do in creating Access applications is automating
things that an end user should not be forced to do manually.

You gave us a good "flying-at-thirty-thousand-feet" overview with few
specifics. Unfortunately, just about the only kind of solutions we can give
in a newsgroup response are to very specific problems where we have been
provided the "eye-level" details.

Larry Linson
Microsoft Office Access MVP