Multiple (two) back-end MDB's.
in [General]
|
Prev: test
Next: Automatic number combination
From: Bill on 18 May 2010 19:41 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 18 May 2010 19:48 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 18 May 2010 20:01 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 23 May 2010 20:06 "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 23 May 2010 20:20
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/ |