Results 1 to 14 of 14
  1. #1
    Lounger
    Join Date
    Jul 2002
    Location
    Hampshire, Illinois, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple Back-Ends ??? (2000)

    I want to separate my client's data by geographical area into separate back-end .mdb's... and allow them to (re)link them on demand. All the samples I find only allow a refresh/relink of existing linked tables... but won't change the back-end or redirect them to a different location altogether... without physically relocating the original back-end (so it can't be found) the code I'm using (obtained from the MS Web-site) doesn't work. Anyone else done anything like this...???

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiple Back-Ends ??? (2000)

    Could you explain more about why you wnat to use multiple back ends? If these databases are all going to be on the same server, there isn't much point that I can see, and analyzing the data to compare between geographic areas would be a real headache under the design you're proposing. Are you concerned about overall size of the database or what?
    Charlotte

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Multiple Back-Ends ??? (2000)

    I want to echo Charlotte's thoughts on this matter. While relinking to different backends is not particular difficult, the situation you described does not seem to be a good use for this technique. The arbitrary splitting of your data (whether it be into multiple tables or multiple databases) generally is not a good idea. You are always left with the problem of how to bring it all together. Plus you have data entry problems, etc. What benefits do you hope to derive from splitting it this way?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Lounger
    Join Date
    Jul 2002
    Location
    Hampshire, Illinois, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Back-Ends ??? (2000)

    Thanks to you both for responding. Here's what I was thinking, and please feel free to tell me I'm out of my mind... The client has little reason to the combine the data (except perhaps for quarterly or annual reports) but for the day-to-day they need to access geographical location's data. Their db is almost 25 mb combined. My thought was to split them up geographically and break links and re-link as needed. The other cavaet is that they have two locations with the data located on a server in one of them. It would improve performance (I think) to let the one location have their data on their end and the other to have theirs. Then on an as-needed basis link to the remote server to do whatever they need to do... They only have 20,000 records (although there is a lot of related data in other tables resulting in the db size) and although a SQL back-end may become necessary eventually, with some normalization and clean-up it seems like overkill right now. They connect to the other location via DSL now... The other thing is I'm going to need to replicate for users on laptops... So that's my thinking.... Whaddaya think?

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Multiple Back-Ends ??? (2000)

    Hi - chiming in late here, but . . .
    Our experience has been that when you link to a back-end .mdb file, even if the front-end is on your local server, and you have a T1 connection (1.544Mb) or better, it is still quite slow. The problem is that Access returns a complete record set if you want to look at a table, or even if you do a query, since the query is in the front-end, it has to pull all the data over to run a query. Replication would be one solution that could work reasonably well - it runs fine on a DSL connection, and happens fairly quickly, though in general you want to take exclusive control of the .mdb file before you run a synchronize.

    Another possibility would be to use Windows Terminal Services on a Win2K server at the server end. That avoids the hassle of replication, which can be a pain, and gives good response, as all you send across the link are the screen paints. That definitely is a good choice if you have SQL Server lurking in the background as a potential replacement for the back-end. Hope this makes sense - if you want further clarification, please post again.
    Wendell

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Multiple Back-Ends ??? (2000)

    I can't even pretend to know all the nuances of your situation, and every situation is different. The only thing I can do is to give you general guidelines (somewhat shaded by my personal opinions), from which you will have to factor in the specifics of your situation, so you can weigh the pros and cons.

    In general, I think you will have trouble by splitting the databases geographically. You will end up with alot of duplicate data, loss of referential integrity, etc. I don't know what kind of data you have, but let's take an accounts payable situation with vendors and invoices. If I decide to breakup my payables geographically, I will have some vendors who will be in both databases. But because they are separate, changes made to the vendor on one side will not automatically show up on the other. And it will be difficult (if not virtually impossible) to get any sort of combined vendor totals.

    Database and form design are usually the big factors in performance issues. Lack of indexes, bound forms that return large recordsets, too many subforms on a form; these are some of the design factors than can make a database work, or kill it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    New Lounger
    Join Date
    May 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Back-Ends ??? (2000)

    Eileen,
    I have developed an A2K database application that has approx 150 tables split over 5 separate back-end mdb files. Just as designers would normalise their data into tables, I have logically grouped these tables into domains for: 1) Contact details; 2) Inventory; 3) Assets; 4) Financials and 5) Medical history.
    This application is essentially an OLTP system with the back-end running on a fileserver and the frontend installed on various PCs. The interconnection is a 100mbs LAN.
    Consequently, with split database files, I miss out on A2K's "inbuilt" referential integrity features for those relationships that span the MDBs but use (where deemed necessary or from laziness) such features for relationships inside the MDB. I manage other integrity through the application. I use long integers as surrogate keys but I don't use Autonumbering and nor do I use replication (burnt from earlier experience with earlier releases).
    I have facilities in the application for the user to connect/switch between different copies of the back-end. For example, if I need to take the server off-line I will push the back-end MDBs to another fileserver or peer computer and get the users to re-connect to the re-located back-end files.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiple Back-Ends ??? (2000)

    Was there a reason for doing it that way instead of using the much faster and more robust SQL Server for the single backend?
    Charlotte

  9. #9
    Lounger
    Join Date
    Jul 2002
    Location
    Hampshire, Illinois, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Back-Ends ??? (2000)

    Cost, cost, cost..... They may go that way, but have spent so much money developing the application already (and I've never seen such a mess) that they are skittish about any added expenses right now. I'll push for that down the road though... The guy wrote this app with no thoughts to data integrity or ease of use (not to mention cosmetics...) in mind. It's actually got addresses separated into 5 tables... the street number in one table, the street name in another..... yikes! And there is no back-end front-end scenario in place now. They have multiple .mdbs so any changes made have to be made in all.... etc.... It's a huge debacle as far as I'm concerned. I've separated data from code but noticed the back-end was over 25 mb and started thinking along the lines of separating.... there is no need to keep them together (vendors, etc.) because the nature of their business isn't like that... the records are indeed unique and the only "shared" stuff would be city, state, county... other than that, there's no reason to keep them together. Even contact records relate only to their respective records within the geographic location...

  10. #10
    Lounger
    Join Date
    Jul 2002
    Location
    Hampshire, Illinois, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Back-Ends ??? (2000)

    Can I beg you for a sample??? It sounds exactly like what I need... I've gotten pretty close to where I want it, but I'd love to see how you did it to make sure that my code is clean...

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiple Back-Ends ??? (2000)

    It sounds like the person who wrote it tried too hard to normalize the data. I've never found it productive to carry normalization of addressess that far.

    But surely there must be some level of crossover in the data, or do they never intend to do any analysis across all these areas? If they tell you that, do NOT believe them. <img src=/S/liar.gif border=0 alt=liar width=25 height=22> As soon as everything works, the next request will be for comparisons. Trust me, I've had years of experience to back up that judgment.
    Charlotte

  12. #12
    Lounger
    Join Date
    Jul 2002
    Location
    Hampshire, Illinois, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Back-Ends ??? (2000)

    Yup! I know how that goes. But, this may really be an exception. They purchase properties where folks neglect to pay the taxes (hence the geography) and generate correspondence, legal paperwork, etc. So, based on everything I've seen, and certainly I'm not disputing your comments, it would be a rarety (annual stats maybe with counts by status or something... although I am only anticipating that... they do not do that now) that they should ever have to share/combine the data.

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Back-Ends ??? (2000)

    Not to dispute any of the discussion about the wisdom of splitting your database... but your original question was about how to relink the tables. I use a module written by Carl Tribble with great success. It has an optional parameter to force the reconnection regardless of the existence of an "incorrect" database. It can be found at:
    http://puma.agron.ksu.edu/~sgsax/download/

    I've added to the module by including a table in the frontend that stores all the potential locations for the backend, complete with a description field. Then I simply select one of the locations using a combo box on a "relink" form, and all the tables are automatically relinked. I use it all the time to switch between "live" and "development" copies of the backend.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  14. #14
    New Lounger
    Join Date
    May 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Back-Ends ??? (2000)

    Charlotte,
    You've asked me a question that, in my politeness, I shall respond.
    There is much I could debate of the RDBMS chosen for my app. In the end it all comes down to the bottom line - $$$$$$$$ ;-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •