splitting the database

[Gitche Gumee]

Please advise.

I'm the designer, my husband is the user. The design method is "seat of the
pants", and communication regarding the user's needs is sometimes, well,
challenging.

Anyway, it's basically a one-user system. I design at home, he takes the
working system on the road on his laptop in the form of a replica.

My question is, should I split this database? It's a work in progress, as I
am a learner, so I am making design changes all the time in response to my
user's evolving needs. There are times when, because the system was not
perfect before deployment, data fixes need to be made, which I have done
while working on the Design Master. Then, the entire system, forms/reports as
well as tables/queries, is synchronized.

I seem to have a knack for hosing the entire system with my great design
ideas. This causes no end of problems when we depend on synchronizing the
replica, both data and forms, with the Design Master. It seems it would make
sense to split the database so I can freely hose the interface without
jeopardizing data integrity, and install a clean front end when/if I get one
working to my satisfaction and synchronize only the tables.

The second part of my question concerns how to accomplish the split. Pretend
I have hosed the interface while my husband is away (ahem). Can I, at this
point, working from a backup, split the database, clean up the interface/FE,
and then synchronize tables and install a front end for him when he returns?

Thanks in advance for your sage advice.

 

[Jack MacDonald]

Most definitely you should split the database. The real question is
whether you should replicate the database...

Replication is intended ONLY for the backend part of a split
application. In other words, only for the actual tables. Everything
else should be in an unreplicated frontend. If your husband is the
only user, then I suspect that replication is overkill for the
application. I suggest keeping just one "live" copy of the backend
database on his notebook. You keep a throw-away copy on your
developement computer. The only reasons for *copying* the backend file
onto your computer would be for 1) backup and 2) having "real" data
that you can use to test your development.

Replicating the frontend (or a monolithic database) is known to cause
database corruption.

When you make a change to your frontend database, you can simply copy
it to the laptop computer, overwriting the existing copy. You will
save yourself some headaches if the folder structure on the laptop is
identical to the folder structure on the development computer (so you
won't need to change the links between the frontend and backend).

The easiest way to split a database is to make two copies -- one with
"backend" figured into the filename, and the other with "frontend"
figured into the filename. Delete the tables from the frontend, and
delete everything else from the backend. Create links from the
frontend to the backend using File > Get External Data > Link. Google
for Tony Toews site for some excellent advice about split databases.

Your only challenge is what to do with the current replicated
database. Un-replicating is not obvious, although I recently found
this article http://www.pacificdb.com.au/MVP/Code/UnReplicate.htm that
seems to help.

Please advise.

I'm the designer, my husband is the user. The design method is "seat of the
pants", and communication regarding the user's needs is sometimes, well,
challenging.

Anyway, it's basically a one-user system. I design at home, he takes the
working system on the road on his laptop in the form of a replica.

My question is, should I split this database? It's a work in progress, as I
am a learner, so I am making design changes all the time in response to my
user's evolving needs. There are times when, because the system was not
perfect before deployment, data fixes need to be made, which I have done
while working on the Design Master. Then, the entire system, forms/reports as
well as tables/queries, is synchronized.

I seem to have a knack for hosing the entire system with my great design
ideas. This causes no end of problems when we depend on synchronizing the
replica, both data and forms, with the Design Master. It seems it would make
sense to split the database so I can freely hose the interface without
jeopardizing data integrity, and install a clean front end when/if I get one
working to my satisfaction and synchronize only the tables.

The second part of my question concerns how to accomplish the split. Pretend
I have hosed the interface while my husband is away (ahem). Can I, at this
point, working from a backup, split the database, clean up the interface/FE,
and then synchronize tables and install a front end for him when he returns?

Thanks in advance for your sage advice.

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[John Vinson]

Please advise.

I'm the designer, my husband is the user. The design method is "seat of the
pants", and communication regarding the user's needs is sometimes, well,
challenging.

<chuckle> It often is in any case but I can see that if a spousal
arrangement is involved it could get even messier!

Let's try to avoid any solutions which involve someone having to spend
the night in the doghouse...

Anyway, it's basically a one-user system. I design at home, he takes the
working system on the road on his laptop in the form of a replica.

My question is, should I split this database? It's a work in progress, as I
am a learner, so I am making design changes all the time in response to my
user's evolving needs. There are times when, because the system was not
perfect before deployment, data fixes need to be made, which I have done
while working on the Design Master. Then, the entire system, forms/reports as
well as tables/queries, is synchronized.

I'd say yes... but maybe not for the reason you think.

Replication does NOT play nicely with forms, reports, code and other
such objects. The redoubtable Replication expert MichKa (Michael
Kaplan) recommends that ONLY tables be replicated, *never* code
bearing objects.

In your case, once the table design has stabilized and you're working
mainly on forms, reports, and other user interface stuff, I'd suggest
a split database with a replicated backend (so you can share the same
data, kept up to date) and an *unreplicated* frontend. Just give your
user a complete new frontend, linked properly to the backend on his
laptop, whenever you make a change; since the frontend contains no
data, there's no harm in simply deleting and replacing it with a new
version.

John W. Vinson[MVP]

 

[Gitche Gumee]

Thank you! That's exactly what I needed to know. I had a fear of splitting, I
guess, but now I comprehend just what it means. I've only learned recently,
through this forum, that replicating forms and reports is a bad idea.

I couldn't get the unreplicate tool to work, but as there are only about a
dozen tables I did it manually following these instructions:
http://support.microsoft.com/kb/q153526/

After unreplicating, the database went from about 13MB to less than 3.

 

[Gitche Gumee]

Thank you, John. That is most helpful and I feel courageous and powerful now.
I have been wanting to load a replica onto my husband's business partner's
computer, but I've been hesitating because replication has been so wonky.
I'll set it up as you suggest, so that those two can synchronize their tables.

Thanks again.