Multiple (two) back-end MDB's.
in [General]
|
Prev: test
Next: Automatic number combination
From: Bill on 13 May 2010 03:51 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
From: david on 13 May 2010 05:24 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) "Bill" <billstanton(a)psln.com> wrote in message news:%23vyjaEn8KHA.5476(a)TK2MSFTNGP06.phx.gbl... > 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 >
From: Larry Linson on 14 May 2010 23:42 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. -- Larry Linson, Microsoft Office Access MVP Co-author: "Microsoft Access Small Business Solutions", published by Wiley Access newsgroup support is alive and well in USENET comp.databases.ms-access "Bill" <billstanton(a)psln.com> wrote in message news:%23vyjaEn8KHA.5476(a)TK2MSFTNGP06.phx.gbl... > 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 >
From: Bill on 15 May 2010 00:12 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. "Larry Linson" <bouncer(a)localhost.not> wrote in message news:OdPkWC%238KHA.1888(a)TK2MSFTNGP05.phx.gbl... > 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. > > -- > Larry Linson, Microsoft Office Access MVP > Co-author: "Microsoft Access Small Business Solutions", published by > Wiley > Access newsgroup support is alive and well in USENET > comp.databases.ms-access > > > "Bill" <billstanton(a)psln.com> wrote in message > news:%23vyjaEn8KHA.5476(a)TK2MSFTNGP06.phx.gbl... >> 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 >> > >
From: John W. Vinson on 15 May 2010 02:25
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] |