Multiple (two) back-end MDB's.

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


"John W. Vinson" wrote in message
...
On Sat, 15 May 2010 10:59:45 -0700, "Bill" wrote:

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"

--

John W. Vinson [MVP]

Union queries are never updatable.

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



"Bill" wrote in message
...
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


"John W. Vinson" wrote in message
...
On Sat, 15 May 2010 10:59:45 -0700, "Bill" wrote:

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"

--

John W. Vinson [MVP]

Indeed, that makes sense.

Bill


"Douglas J. Steele" wrote in message
...
Union queries are never updatable.

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



"Bill" wrote in message
...
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


"John W. Vinson" wrote in message
...
On Sat, 15 May 2010 10:59:45 -0700, "Bill" wrote:

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"

--

John W. Vinson [MVP]

"Bill" wrote:

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/

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



"Tony Toews [MVP]" wrote in message
news
"Bill" wrote:

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/