multiple back-ends

[Mark Kubicki]

is it possible to have an access file with multiple back-ends

I have one that requires
(1) be for a master data set (from which information is pulled,
and (1) be for data specific to a particular job

(the protocol established a long time ago in the office is that each job has
it's own access file; as currently designed, there is no separation between
the code and the job's data -only between the code and the master data; this
doesn't make much sense to me...)

 

[pietlinden]

is it possible to have an access file with multiple back-ends

I have one that requires
    (1) be for a master data set (from which information is pulled,
    and (1) be for data specific to a particular job

(the protocol established a long time ago in the office is that each job has
it's own access file; as currently designed, there is no separation between
the code and the job's data -only between the code and the master data; this
doesn't make much sense to me...)

sure. You could write pass-through queries to get the data from the
backend. (I've done this with SQL Server) Then you could put all your
code into a template and reuse it.

 

[Klatuu]

Not a problem, you can link to multiple back-ends. You can even mix and
match. We have one app the connects to two mdbs and SQL Server at the same
time.

But, you are right, having a different back end for each job makes
absolutely no sense. This is certainly a decision made by someone with no
technical knowledge.

 

[John W. Vinson]

is it possible to have an access file with multiple back-ends

Sure. One drawback is that you cannot enforce referential integrity between
tables in different backends.

I have one that requires
(1) be for a master data set (from which information is pulled,
and (1) be for data specific to a particular job

Gnnn.... bad design.

(the protocol established a long time ago in the office is that each job has
it's own access file; as currently designed, there is no separation between
the code and the job's data -only between the code and the master data; this
doesn't make much sense to me...)

It may be long established, but it's WRONG. I don't know the nature of the
data, but this certainly sounds like you will have many databases with
identically structured data tables; it's more than likely that you will have
redundant data stored in these tables. Unless there is a *very good* reason to
do this, you should consider instead having one unified, single backend, with
a JobID field to distinguish data pertaining to different jobs! If the field
is indexed then it's trivially easy to construct a query selecting just one
job.

 

[Tony Toews [MVP]]

Not a problem, you can link to multiple back-ends. You can even mix and
match. We have one app the connects to two mdbs and SQL Server at the same
time.

But, you are right, having a different back end for each job makes
absolutely no sense. This is certainly a decision made by someone with no
technical knowledge.

Not quite. There are indeed times when having a separate BE by job
can make sense. Maybe not a lot of times but I have seen some
largish systems controlling $10M or $50M jobs where this did make some
sense.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/