Re: Simple replication situation/question

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

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

Date: Sat, 16 Oct 2004 19:08:29 -0700

Hi, Jack,

more progress:

1. I ran your TableSize form, and it showed about 4.4M of data distributed
in a way that makes sense to me (2 tables containing 98% of the data). That
leaves, on a total of 35M for the database, a 30M Front End. Didn't know I
was such a prolific coder, especially since most of the FE is vanilla forms
and queries (no reports that amount to much).

2. I did an X-ref listing of the database objects using Rick Fisher's "Find
and Replace" Add-In. I started deleting some unused junk, mostly objects I
used to experiment with. All of a sudden, Windows Explorer shows a size of
5.5M for the entire database!

3. I did a WinZip on the 5.5M, and it reduced to 1.4M compressed.

4. There is still a problem in my Reports 'module'. I tried to delete
about 6 obsolete ones, and Access would apparently process the delete, but
it would still appear in the object list. I got rid of most of those by
being 'creative'--I imported reports having the same name from a much
earlier version. I got the popup "There is already a report by the name
...". After OK-ing out of that, I was able to delete all but one.
Attempting to delete this last one at first gave weird messages, like 'No
current record'. Now the entry is there in the object list, but Delete and
Rename are greyed out.

I haven't done the splitting yet; I wanted to get the junk out first.

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

Relevant Pages

  • Re: Temprarily locking out other users?
    ... reports and code? ... That's a major ADVANTAGE to ... Performance is better; the risk of database corruption goes way down; you have ... And, other benefits aside, would splitting solve the issue I mention ...
    (microsoft.public.access.modulesdaovba)
  • 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: Speed after Splitting Database
    ... application speed when splitting the data base. ... unified application on the network, ... But when I split the database, ... >>reports and the ...
    (microsoft.public.access.externaldata)
  • 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: 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)