Multiple (two) back-end MDB's.
in [General]
|
Prev: test
Next: Automatic number combination
From: Bill on 15 May 2010 06:20 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" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message news:k9fsu5hh2afcbf80pmb3m03upe1uvom60o(a)4ax.com... > On Fri, 14 May 2010 21:12:32 -0700, "Bill" <billstanton(a)psln.com> wrote: > >>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! > -- > > John W. Vinson [MVP]
From: John W. Vinson on 15 May 2010 13:22 On Sat, 15 May 2010 03:20:48 -0700, "Bill" <billstanton(a)psln.com> wrote: >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. -- John W. Vinson [MVP]
From: Bill on 15 May 2010 13:59 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" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message news:dvltu59tahub9jijiei9inot1qaam34jk2(a)4ax.com... > On Sat, 15 May 2010 03:20:48 -0700, "Bill" <billstanton(a)psln.com> wrote: > >>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. > -- > > John W. Vinson [MVP]
From: John W. Vinson on 15 May 2010 16:54 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 15 May 2010 20:31
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 "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] |