Multi mdb Design Considerations

[Klatuu]

I am looking for input on modifying a design to improve performance.
Version 2003 - All property split and linked. Mde on all front ends. Using
DAO.

Each user has MainDB front end. Because of the volume of data, all back
ends are Client/Region Specific. That makes about 300 back ends.

We have two other apps. A Global database that contains specific info on
Clients and Properties. All MainDB apps link to this database only to pull
reference data.
Also, a PrintMail app that collects invoicing data from all Client/Region
databases to create and export XML files that are sent to the firm that
prints and mails about 200,000 invoice.

The Client/Region data is passed to the PrintMail mdb by linking to it and
appending data to tables in in. The PrintMai app then creates the invoice
data from the consolidation of all Client/Region data.

Now here is the issue. Because there are multiple MainDb users trying to
upload their data via append queries, the process becomes very slow when 20
or more are trying to do it at the same time.

We are currently trying to decide the best way to uncouple the uploads so
there is not the conflict and slow down when (of course at the last possible
minute) the users are trying to upload.

We have two conflicting ideas and would like opinions on both or other
suggestions.

Idea 1. Export csv files from the MainDB. Then import them into the
PrintMail app.
Idea 2. Use regional PrintMail mdbs to allow the users to upload. Then
import them into the central PrintMail mdb. Note that multiple users working
with different clients would still be sharing a regional PrintMail mdb. (One
regions contains one or more clients).

I prefer idea 1. My boss prefers idea 2.
My problem with 2 is that it does not completely decouple the process.
Since multiple users could be export to a regional PrintMail mdb, the
possilbilty of slow down still exists, though not as sever. Another point of
contention is that if the user of the PrintMail app is trying to consolidate
the data by importing from multiple mdbs, then if a MainDB user is trying to
export to the same mdb that the PrintMail user is trying to import, a
conflict exists.

Pardon the verbosity, but in this instance, I think it necessary.

 

[Jeff Boyce]

Dave

Any chance the back-end can get upgraded to something more robust (some
variant of a SQL-Server-type db)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Klatuu]

Jeff,
Thanks for the response.
Upgrading to SQL Server is the long term goal. This is an application that
started with a small company. That company was bought by the current company
and the growth has been dramatic.

The plan is to keep this going for another year. We will begin rewriting in
C#.NET.

I suggested migrating to SQL, but the PTB don't want to expend the effort.
It probably would be significant, becase it is all mdb using DAO.

So, the short answer is no.

 

[Jeff Boyce]

Dave

Not that I'd ever suggest subversive activity, ...

.... oh by the way, did you hear that the "lite" version of MS SQL-Server is
available for free, download?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Klatuu]

Access to any version of SQL is not an issue. Much of our company's software
runs on it. Since this is a large organization, I don't have the automony to
accomplish that, besides, my boss is fairly compentent in Access and would
notice.

But thanks for the input.

My boss prefers the multi mdb approach and I think the csv approach would
better decouple the processes. I was looking for ammunition.

 

[Larry Daugherty]

Hi Dave,

For the near term solution I'd go with the delimited files. They
provide the quickest path to a reasonable solution.

A caveat that you've probably already considered is the need for code
to close the loop for what happens at the eventual processing of the
delimited files. Back at the point of origin you need to know that
the file was processed and what the outcome was.

HTH

 

[Pat Hartman]

I also prefer the delimited file approach. To expand on what Larry said, I
would create a table that contains a list of expected files with their names
and full paths and I would create a table that contains a list of processed
files for the "period". You can move processed files from the input folder
to an archive folder to make it easier for the app to determine what files
it needs to process. You can create a query that compares the files
processed in the current period with the list of expected files to determine
what is missing. You can get really fancy with this and send email
reminders.

As to the final solution - what is wrong with Access as a front end? If the
application works well for you now, upgrading to SQL server may be all you
need to do. When accessing ODBC table ADO is supposed to be more efficient
than DAO but I don't see much difference in my apps. All of my applications
use bound forms and the form's recordsource is DAO so I have never really
felt a compulsion to convert (if it's good enough for Access, it's good
enough for me). If your application is currently using forms bound to
tables or to queries without selection criteria, you will need to make
application modifications to experience any benefits from switching to SQL
tables.

I build all my applications with the idea in mind that at some point, they
may need to upsize and so for me, conversion time is measured in minutes or
hours at most rather than days or months which is typical when converting to
an .adp.

 

[Klatuu]

Thanks for your input, Larry and Pat.

Yes, everything you suggested has been considered.

As to the verification, that process is in place in the current environment.
The post app appends to 6 tables one of which is the statistics on what was
sent. The receiving app compares that to what it recieved.

My plan would be to have the posting app export the data into 6 csv files to
a specific folder. The receiving app would then import all the files found
in the folder. That way, there would be no contention. The receiving app
will know client/regions exist and would have to determine if any are missing.

As to converting to SQL, the whole point of this is that the company has
grown so dramatically the backend mdbs are approaching capacity. We have
already split it out so there are about 300 back ends.

The company wants to standardize all the existing apps to C#.net. All our
other apps are already in that platform, but we need to keep the Access
version alive for another year.

I suggested we convert all the back ends to SQL Server. That way we could
redeploy front end functionality as it is build rather than having to build a
new database, but I don't get to make the decisions.