Re: Simple replication situation/question

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

From: Jack MacDonald (jackMACmacdonald_at_telus.net)
Date: 10/11/04 

Date: Mon, 11 Oct 2004 15:13:57 GMT

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

Relevant Pages

  • Re: Simple replication situation/question
    ... When I was first faced with email this 36M database, ... > - store a global variable that designates a root folder for your ... >>Yes, there are photos, but no, they are not stored in the database. ...
    (microsoft.public.access.replication)
  • 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
    ... the form in the database. ... >I haven't done the splitting yet; I wanted to get the junk out first. ... Storing photos is a certain ... >> remove uppercase letters for true email ...
    (microsoft.public.access.replication)
  • Re: How to indicate database home folder
    ... will give you the path and name of the data source that is attached to the ... append the name of the folder containing the photos ... folder (relative to folder in which the database is created). ...
    (microsoft.public.word.mailmerge.fields)
  • 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)