From: Bill on
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" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message
news:ct1uu5182oorgl0651lr8fq32kng5a468a(a)4ax.com...
> On Sat, 15 May 2010 10:59:45 -0700, "Bill" <billstanton(a)psln.com> 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]


From: Douglas J. Steele on
Union queries are never updatable.

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



"Bill" <billstanton(a)psln.com> wrote in message
news:OB05WOu9KHA.4768(a)TK2MSFTNGP04.phx.gbl...
> 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" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message
> news:ct1uu5182oorgl0651lr8fq32kng5a468a(a)4ax.com...
>> On Sat, 15 May 2010 10:59:45 -0700, "Bill" <billstanton(a)psln.com> 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]
>
>

From: Bill on
Indeed, that makes sense.

Bill


"Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote in message
news:%23bw2jSu9KHA.5808(a)TK2MSFTNGP02.phx.gbl...
> Union queries are never updatable.
>
> --
> Doug Steele, Microsoft Access MVP
> http://www.AccessMVP.com/djsteele
> (no e-mails, please!)
>
>
>
> "Bill" <billstanton(a)psln.com> wrote in message
> news:OB05WOu9KHA.4768(a)TK2MSFTNGP04.phx.gbl...
>> 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" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message
>> news:ct1uu5182oorgl0651lr8fq32kng5a468a(a)4ax.com...
>>> On Sat, 15 May 2010 10:59:45 -0700, "Bill" <billstanton(a)psln.com> 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]
>>
>>
>


From: Tony Toews [MVP] on
"Bill" <billstanton(a)psln.com> 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/
From: Bill on
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]" <ttoews(a)telusplanet.net> wrote in message
news:olgjv5ped9mjpb3mdgashegbgfh9tg7b35(a)4ax.com...
> "Bill" <billstanton(a)psln.com> 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/


First  |  Prev  | 
Pages: 1 2 3
Prev: test
Next: Automatic number combination