Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2012
    Posts
    31

    Thumbs up Answered: In the words of Monty python...SPLITTER! (splitting a database)

    I have an MS database on computers C:\ DRIVE I have made a copy:
    With the copy I have:

    Split the database via the wizard into two files:

    - Back end
    - Front end

    I have put the back end on a network (all intended users have the necessary privileges to access it)
    The copy on my hard disk has been saved as an ACCDE file. This is now the front END that I will distribute

    Question 1)
    Can I put the ACCDE file in the same folder as the backend (as everyone will have access to it) They can then navigate to it and put a shortcut on their respective desktops:

    Question 2)
    Should I ever want to make a change to the database (add another field or dropdown box etc) in the future how do I do it?

    Thanks for your time

  2. Best Answer
    Posted by healdem

    "in an ideal world each user should have a copy of the front end on their own workstation.. there is code to manage the deployment in the code bank. IIRC provided by PKStormy
    there is code to connect to a backend at runtime, again pretty certain its in the code bank from the same author, if not there are examples elsewhere on t'net. and failing that there are examples in the excellent Access Developer Handbook, published by Sybex. its in the Enterprise edition

    for development purposes you work on an ACCDB, and I'd strongly recommend connecting to a separate development back end
    that back end will almost certainly be an ACCDB or MDB. and can include queries to create test cases for development QA/QC.

    any schema design changes need to be recorded and implemented in the new back end using a DDL query. SQL has two variants, the commonly used one is DML (Data M(manipulation)? Language) and DDL (Data Definition Language. Its SQL but instead of manipulating data you can manipulate the design eg ALTER TABLE, CREATE TABLE, meddle with indexes, columns and so on. the technique I'd recommend for updating a live DB is record the SQL, paste it into a form then run that form when you need to do the update. after taking backups and so on.

    user server URL's not hard coded drive letters, UNLESS you have a company wide policy on what hardcoded drive letters are on each and every PC that will run this application. a server URL is a smarter solution. instead of X:/path/to/file its //server/path/to/file. the only problem is when you have to migrate servers. the smart solution to that is to replace a server with a new one of the same name but sometimes thats not possible"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in an ideal world each user should have a copy of the front end on their own workstation.. there is code to manage the deployment in the code bank. IIRC provided by PKStormy
    there is code to connect to a backend at runtime, again pretty certain its in the code bank from the same author, if not there are examples elsewhere on t'net. and failing that there are examples in the excellent Access Developer Handbook, published by Sybex. its in the Enterprise edition

    for development purposes you work on an ACCDB, and I'd strongly recommend connecting to a separate development back end
    that back end will almost certainly be an ACCDB or MDB. and can include queries to create test cases for development QA/QC.

    any schema design changes need to be recorded and implemented in the new back end using a DDL query. SQL has two variants, the commonly used one is DML (Data M(manipulation)? Language) and DDL (Data Definition Language. Its SQL but instead of manipulating data you can manipulate the design eg ALTER TABLE, CREATE TABLE, meddle with indexes, columns and so on. the technique I'd recommend for updating a live DB is record the SQL, paste it into a form then run that form when you need to do the update. after taking backups and so on.

    user server URL's not hard coded drive letters, UNLESS you have a company wide policy on what hardcoded drive letters are on each and every PC that will run this application. a server URL is a smarter solution. instead of X:/path/to/file its //server/path/to/file. the only problem is when you have to migrate servers. the smart solution to that is to replace a server with a new one of the same name but sometimes thats not possible
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by healdem View Post
    ...in an ideal world each user should have a copy of the front end on their own workstation...
    And just to expand on this, having multiple users sharing a single Front End, on a network drive, is the sure way to repeated episodes of corruption, speed and timing problems, and all manner of strange, odd and curious behavior!

    Being in forced retirement, I spend 8-10 hours a day here and on other Access forums/newsgroups, and over the past ten years have seen literally dozens and dozens of reports of split databases, using links to a single Front End, causing these kinds of problems, as well as massive data loss! The really insidious thing is that this kind of setup can work for extended periods of time before the troubles begin! But once it does, they become chronic, which is to say they occur over and over and over again!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #4
    Join Date
    Apr 2012
    Posts
    31
    Thanks for the taking the time to supply detailed useful info - even I could follow it (and I've got GCE's in Art and Maffs...lovely jubbly!)

  6. #5
    Join Date
    Apr 2012
    Posts
    31
    Great advice. I am sure this has saved me a lot of misery down the line. Sorry to hear about the forced retirement. Hope all is well!

Tags for this Thread

Posting Permissions

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