Re: Simple replication situation/question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Gary Schuldt (garyschuldt_at_comcast.net)
Date: 10/16/04

  • Next message: Gary Schuldt: "Re: Simple replication situation/question" 
    Date: Sat, 16 Oct 2004 11:57:47 -0700

    Hi, Jack,

    Sorry for the delay in response; we've had a week of tandem GI afflictions
    in the house, and I've been pretty much flat on my back for the past couple
    of days.

    > Are you regularly Compacting the database?

    When I was first faced with email this 36M database, compacting was one my
    early tactics for dealing with the problem. I'm using Access 2K. When I
    select Compact & Repair and select the mdb file, it asks me for a file to
    compact it into, and I give it compact.mdb. It runs for a few seconds, with
    no progress bar in the lower left, and then just says "Ready". There is no
    file "compact.mdb" at the end of the process, but there is no error message,
    either.

    I tried the same process on another unrelated 31M database, and it ran a
    couple of minutes with a progress bar, and the compacted file was a little
    under 2M.

    It sounds like I have a problem here. Any ideas on what to do next?

    > Depending on how your photos are store in the file system (ie., the
    > directory structure), here's a technique that I have used and you
    > might want to consider:
    >
    > - store a global variable that designates a root folder for your
    > photos

    Good idea; I can implement that. Thanks.

    > I am sending via email an A97 database called TableSize. Import the
    > two objects into your application and open the "TableSize" form. Press
    > the button, and it will show you the size of each table in your
    > database.

    OK, I received it fine. I'll do that right after I finish this post.

    > Splitting the database is (IMHO) easier done manually than using the
    > Wizard.
    > - preserve a backup copy of the database
    > - make a new copy as the BACKEND -- I typically append "BE" to its
    > name
    > etc . . .

    That procedure makes sense and sounds easy. I'll give it a go. So the FE
    gets linked to the BE; is the linkage 2-way, so that I could double-click
    the BE.mdb and start the app as well?

    Gary

    "Jack MacDonald" <jackMACmacdonald@telus.net> wrote in message
    news:po7lm011vc8l4dj8a3dadssljpjo17avld@4ax.com...
    > On Sun, 10 Oct 2004 23:13:28 -0700, "Gary Schuldt"
    > <garyschuldt@comcast.net> wrote:
    >
    > >
    > >> BTW -- what kind of database do you have for a hobby that tallies to
    > >> 36MB? That's a lot of data for a hobbiest. Are you including photos?
    > >
    > >I've often wondered why the file size is 36M, but I'm not sure how to
    find
    > >out how that number is distributed between the various tables and the
    other
    > >objects, such as the forms, the queries, the reports, and the VBA code.
    If
    > >I knew that, it would help me determine whetherI could reduce the adipose
    > >tissue! It Winzips to about 5M.
    > >
    > >Yes, there are photos, but no, they are not stored in the database.
    There
    > >are UNC addresses for local ones of specific plants and URL addresses for
    > >botanical photos published on the web. My profession is includes
    teaching
    > >database analysis and design (but not MS Access programming! yet!), and
    the
    > >data structures are pretty much normalized with little duplicated data.
    >
    > OK - a common mistake for many Access beginners is to store photos in
    > the database. You haven't done that... Storing photos is a certain
    > recipe for database bloat.
    >
    >
    > Are you regularly Compacting the database?
    >
    > >
    > >> It might work. And if you take the added precaution of naming each
    > >> computer identically and creating identical folder structures on each
    > >> computer, it just might work even better. For a hobby database, it
    > >> might be worth the risk.
    > >
    > >My computer is on a LAN with 4 other computers, and it uses a proxy
    server
    > >to get onto the Internet, so I am wary of renaming it. Furthermore, it
    > >would invalidate all the UNC addresses (e.g., addresses of all those
    local
    > >plant photos) that contain its name. I have to assume that future new
    users
    > >might be in a similar situation. So having to rename is a significant
    vote
    > >against cheating.
    >
    > Depending on how your photos are store in the file system (ie., the
    > directory structure), here's a technique that I have used and you
    > might want to consider:
    >
    > - store a global variable that designates a root folder for your
    > photos
    > - presumably you have a database field that holds the photo file name
    > - store just the portion of the filename relative to the photo root
    > folder
    > - use a custome VBA function to generate the full pathname to the
    > photo file
    >
    > A benefit of this convention is that you can reference a different
    > folder structure by changing just one parameter (the name of the photo
    > root folder)
    >
    >
    > >
    > >Thanks for your mother's Patience Poem!
    > >
    > >> - ensure the database is split into Frontend and Backend components
    > >> (standard Access practice)
    > >
    > >I am self-taught in Access and just started building the database while
    > >reading the books. It wasn't until well into the project that I ran
    across
    > >some reference to splitting. I do recognize it as a good design practice
    > >but haven't done it because I didn't know how and everything was working,
    > >anyway. Does splitting create two MDB's from one? I guess splitting
    would
    > >at least tell me how the 36M was allocated between the frontend objects
    and
    > >the backend data.
    >
    >
    > That will give you a basic size distribution -- data vs application.
    >
    > I am sending via email an A97 database called TableSize. Import the
    > two objects into your application and open the "TableSize" form. Press
    > the button, and it will show you the size of each table in your
    > database.
    >
    > Splitting the database is (IMHO) easier done manually than using the
    > Wizard.
    > - preserve a backup copy of the database
    > - make a new copy as the BACKEND -- I typically append "BE" to its
    > name
    > - make a new copy as the FRONTEND -- typically append "FE"
    > - delete everything EXCEPT tables from the BE
    > - delete ONLY the tables from the FE
    > - in the FE, File > Get External Data > Link tables, then point to
    > the BE, and select all the tables.
    >
    >
    > **********************
    > jackmacMACdonald@telusTELUS.net
    > remove uppercase letters for true email
    > http://www.geocities.com/jacksonmacd/ for info on MS Access security

  • Next message: Gary Schuldt: "Re: Simple replication situation/question"

    Relevant Pages

    • Re: Wine Database getting out of hand!
      ... store a link to the photos. ... severe bloat. ... Either store the link to the photos, or break your database into more than ...
      (microsoft.public.access.tablesdbdesign)
    • Re: Join on varchar and int columns
      ... my internal table not having an ident column would be difficult. ... You would then do the join on the database specific key only. ... > internal key to store it as a varchar. ... >> I also store FTP'd image information in my own Photos table which idents ...
      (microsoft.public.sqlserver.programming)
    • Re: Simple replication situation/question
      ... I did an X-ref listing of the database objects using Rick Fisher's "Find ... There is still a problem in my Reports 'module'. ... I haven't done the splitting yet; I wanted to get the junk out first. ... >>Yes, there are photos, but no, they are not stored in the database. ...
      (microsoft.public.access.replication)
    • Re: Simple replication situation/question
      ... Yes, there are photos, but no, they are not stored in the database. ... > computer identically and creating identical folder structures on each ... To do any kind of replication other than within a LAN, ...
      (microsoft.public.access.replication)
    • Re: Access database suddenly increased in size
      ... database bloat even if you link them, ... COM-automation enabled software for that image type will generate and store ... >I have an autograph database that is normally around 7 MB. ... I link to any photos. ...
      (comp.databases.ms-access)