Multiple (two) back-end MDB's.

[Bill]

How does one go about structuring the concatenation
of back-end DB's? That is, for example, a single
application that has one back-end that is common at
each of multiple locations and a ("site") second back-end
that is peculiar to each of those locations.

The common mdb would desirably be read only while
the "site" mdb would be updateable.

Given that there's a straight forward answer to this
question, my next question would be how to create
the common mdb based on a query of a "master"
DB. (The "master" location being the point of
control for the contents of the "common" DB.)

Thanks for any thoughts or suggestions.

Bill

 

[david]

The linked table wizard will let you link to multiple
BE, so that is a place to start.

The linked table wizard is very slow if you try to
use it to refresh links to multiple BE. If you are
trying to do that, first select a group of links to one
BE, then select a group of links to the other BE.

If you plan to distribute this, perhaps you may plan
to include some of the standard code for relinking,
rather than using the wizard? (Warning, the standard
Dev Asish code needs some minor modifications to
work with current versions of Access).

You can use user permissions to make one MDB
read-only, or you can use server file permissions
to make one MDB read-only, or you can modify
the table links (removing the primary key index),
or you modify the tables (removing the primary key
index).

Warning: you make read-only every
query that use the read-only tables at all. You need
to test this to see that it does what you want.

Warning, if you put one MDB in a read-only folder,
opening it in R/W mode will lock out all of the
Read-Only users.

Or you can just give the users forms, and tell them
not to modify the read-only tables. With Access
applications, that is often the best way. If it's not
the best way, sometimes it's better to use a SQL
Server BE.

You can create an MDB by copying a master copy,
or by using the DAO CreateDatabase command, and
then a series of CreateTable commands etc, or a
series of DDL commands, or a series of Import
commands. Often it is easiest to just copy a blank
master copy to the desired location.

(david)

 

[a a r o n . k e m p f]

uh, you can't use multiple Access databases and still enforce
referential integrity.

with SQL Server, you can use triggers to do the same thing.

If you don't give a shit about referential integrity, you should go
and play with excel, kid

 

[Larry Linson]

I'm concerned that you may be talking about linking to an MDB or ACCDB back
end (the "site") back end over a WAN or across the Internet. In either
case, performance will likely be a problem... there are solutions for
accessing data across a WAN or the Internet, but they differ from just
linking a back-end MDB or ACCDB as you would on a high-speed LAN.

 

[Bill]

Oh no, I'm well aware of the problems in linking up MDB's
over a WAN. In this case, the back-end MDB's are on a
single user machine.

I didn't do a very good job of describing exactly what I wanted to
do. The two MDB's are identical in table structure. What makes
them different is that the "common" MDB contains records that
are a subset of a master MDB.

The end users, if I can call them that, simply have the "common"
MDB records combined with the "local" set when the records
are displayed......sort of like a big address book where some of
the records are common to all users.

 

[John W. Vinson]

Oh no, I'm well aware of the problems in linking up MDB's
over a WAN. In this case, the back-end MDB's are on a
single user machine.

I didn't do a very good job of describing exactly what I wanted to
do. The two MDB's are identical in table structure. What makes
them different is that the "common" MDB contains records that
are a subset of a master MDB.

The end users, if I can call them that, simply have the "common"
MDB records combined with the "local" set when the records
are displayed......sort of like a big address book where some of
the records are common to all users.

You're clearly using frontend and backend differently than most! Typically a
backend has tables; the frontend doesn't - it has links to the tables in the
backend, queries, forms, reports, and so on.

It can certainly have tables of its own, and you can create queries joining a
local table to a linked table. You cannot enforce referential integrity across
two databases though. You can also have one frontend linked to two different
backends, and again, you can create queries but not enforce RI between them.

You could even create a UNION query stringing together records from two
different backends into one longer recordset - but that "table" would not
exist in the frontend, only virtually, and performance would probably be
abysmally bad.

Finally storing a subset of one table *IN* another table would be redundant,
very hard to work with, and extremely bad design.

Could you step back a bit and describe the real-life problem you're trying to
solve? It's likely that Access can help... but probably not the way you're
doing it!

 

[Bill]

John,
You wrote:
You could even create a UNION query stringing together records from two
different back-bends into one longer recordset - but that "table" would not
exist in the frontend, only virtually, and performance would probably be
abysmally bad.

That is really what I was up to. Most of my applications are split DB's with
mde front-ends and mdb back-ends where the normalized tables reside.
Each end-user has its own MDB back-end and the application levels are
distributed as zipped mde, plus any other library files pertinent to the
application.

I have a case here where I'd like to control the content of a subset of
the data taking the form of TWO back-end mdb's where I distribute
ONE of the back-end mdb's common to ALL users. If a UNION
query of the two back-ends would be a performance nightmare,
I'll abandon the idea entirely.

Bill

 

[John W. Vinson]

I have a case here where I'd like to control the content of a subset of
the data taking the form of TWO back-end mdb's where I distribute
ONE of the back-end mdb's common to ALL users. If a UNION
query of the two back-ends would be a performance nightmare,
I'll abandon the idea entirely.

Well, it's worth a try - it will depend on the data, the network, your users'
expectations and so on. Don't abandon the idea untried.

If performance is unacceptable you might be able to import the needed data
into a local temp table, in the user's frontend or a "throwaway" backend on
their machine.

 

[Bill]

The amount of data is fairly small, so I'll try a simple UNION first
and see what happens.

Speaking of UNION queries, I've not had the occasion where I needed
to specify anything in the way of a DB other than the implied "current DB".
Can you point me to an example of where this is done?

Thanks,
Bill

(PS) I'll leave this post flagged and will post back with the results, but
it
might be several days, as I have other more pressing tasks at hand.

 

[John W. Vinson]

The amount of data is fairly small, so I'll try a simple UNION first
and see what happens.

Speaking of UNION queries, I've not had the occasion where I needed
to specify anything in the way of a DB other than the implied "current DB".
Can you point me to an example of where this is done?

I'd just link to the table and include the link name in the UNION, though you
could skip that by using the IN clause:

SELECT field, field, field FROM Localtable
UNION ALL
SELECT field, field, field FROM remotetable IN "C:\path\otherbackend.mdb"

 

[Bill]

The table names are the same in both back-end DB's,
so the IN clause will be perfect.

I ought to be able to get to this little task in about a week.

Thanks for your help,
Bill

 

[Bill]

John,
Here's the query I'm currently working with:

SELECT [RecordID], [LastName], [FirstName], [SpouseName], [MaidenName],
[Address], [CityState], [ZIP], [HomePhone], [CellPhone], [WorkPhone],
[Company], [EmailHome], [EmailWork], [FaxPhone], [Salutation], [Notes],
[ListingTag], [ImageID], [GAP], [Hornet]
FROM Addrlst

UNION ALL SELECT [RecordID], [LastName], [FirstName], [SpouseName],
[MaidenName], [Address], [CityState], [ZIP], [HomePhone], [CellPhone],
[WorkPhone], [Company], [EmailHome], [EmailWork], [FaxPhone], [Salutation],
[Notes], [ListingTag], [ImageID], [GAP], [Hornet]
FROM Addrlst IN "C:\Addrlst-II\HornetsNetData.mdb"
ORDER BY [LastName], [FirstName];

The query itself works fine, but the resulting recordset
is not updateable. I assume that Access simply can't
determine which mdb the record came from?

Bill

 

[Douglas J. Steele]

Union queries are never updatable.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
(no e-mails, please!)

John,
Here's the query I'm currently working with:

SELECT [RecordID], [LastName], [FirstName], [SpouseName], [MaidenName],
[Address], [CityState], [ZIP], [HomePhone], [CellPhone], [WorkPhone],
[Company], [EmailHome], [EmailWork], [FaxPhone], [Salutation], [Notes],
[ListingTag], [ImageID], [GAP], [Hornet]
FROM Addrlst

UNION ALL SELECT [RecordID], [LastName], [FirstName], [SpouseName],
[MaidenName], [Address], [CityState], [ZIP], [HomePhone], [CellPhone],
[WorkPhone], [Company], [EmailHome], [EmailWork], [FaxPhone],
[Salutation], [Notes], [ListingTag], [ImageID], [GAP], [Hornet]
FROM Addrlst IN "C:\Addrlst-II\HornetsNetData.mdb"
ORDER BY [LastName], [FirstName];

The query itself works fine, but the resulting recordset
is not updateable. I assume that Access simply can't
determine which mdb the record came from?

Bill

 

[Bill]

Indeed, that makes sense.

Bill

Union queries are never updatable.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
(no e-mails, please!)

John,
Here's the query I'm currently working with:

SELECT [RecordID], [LastName], [FirstName], [SpouseName], [MaidenName],
[Address], [CityState], [ZIP], [HomePhone], [CellPhone], [WorkPhone],
[Company], [EmailHome], [EmailWork], [FaxPhone], [Salutation], [Notes],
[ListingTag], [ImageID], [GAP], [Hornet]
FROM Addrlst

UNION ALL SELECT [RecordID], [LastName], [FirstName], [SpouseName],
[MaidenName], [Address], [CityState], [ZIP], [HomePhone], [CellPhone],
[WorkPhone], [Company], [EmailHome], [EmailWork], [FaxPhone],
[Salutation], [Notes], [ListingTag], [ImageID], [GAP], [Hornet]
FROM Addrlst IN "C:\Addrlst-II\HornetsNetData.mdb"
ORDER BY [LastName], [FirstName];

The query itself works fine, but the resulting recordset
is not updateable. I assume that Access simply can't
determine which mdb the record came from?

Bill

The amount of data is fairly small, so I'll try a simple UNION first
and see what happens.

Speaking of UNION queries, I've not had the occasion where I needed
to specify anything in the way of a DB other than the implied "current
DB".
Can you point me to an example of where this is done?

I'd just link to the table and include the link name in the UNION,
though you
could skip that by using the IN clause:

SELECT field, field, field FROM Localtable
UNION ALL
SELECT field, field, field FROM remotetable IN
"C:\path\otherbackend.mdb"

 

[Tony Toews [MVP]]

How does one go about structuring the concatenation
of back-end DB's? That is, for example, a single
application that has one back-end that is common at
each of multiple locations and a ("site") second back-end
that is peculiar to each of those locations.

Just to throw out an idea. Consider setting up a SQL Server system
where your users access all the data from anywhere they have Internet
Access.

This could solve a lot of other issues such as backup.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Bill]

Tony,
Thanks for the reply. I ended up solving the problem
not by using a UNION query, but by simply using
the IN clause for the RecordSource query that
accesses the "common" table whenever the end
user needs that information. It was really simple
because a separate form was being used anyway.

The user has no sense that the back-ends are
essentially being switched whenever access to
the "common" mdb is in play.
Bill