Solved

compacting and repairing replicas

Posted on 2013-01-22
8
252 Views
Last Modified: 2013-02-06
hey guys,

1) here's my set up. i've got a BE replica on each local computer. it connects to a network drive BE replica and synchronises every day. i want to compact and repair all the BE replicas. must i go to each computer and compact and repair, open the network drive BE replica and C&R it as well or can i C&R one local replica and then when it synchronises it will C&R all the other replicas?

none of the above mentioned BE replicas are the design master.

2) what if i C&R my design master and then synchronise to one replica, then get that replica to synchronise to all the other replicas, will the C&R cascade down?

3) the network connection is SUPER SLOW and so it's very hard to C&R the network replica. if i copy it to my local machine then C&R it then put it back, it will take it as a new replicaID and then there will be conflicts, is that correct?
0
Comment
Question by:developingprogrammer
  • 4
  • 4
8 Comments
 
LVL 84
ID: 38804962
No, the C&R will not traverse replicas. C&R is used to remove dead space in the local database. Replication moves DATA, not structure, to the local resourrce.

You should never C&R over a network. I'm not sure if moving the replica for C&R would cause issues, but my guess is that it would. In general the replica should be maintained by a local IT person, not someone over the network.

Why are you using replication in this  manner? Replication is intended to allow a single database to somewhat support remote users (think travelling salesmen).

I'm sure you're aware that 2007 and 2010 have done away with replication, so when you move to those platforms you'll have to find another way to handle this.
0
 

Author Comment

by:developingprogrammer
ID: 38812904
thanks LSMconsulting, good point about the C&R. i'm using replication in this manner cause having users open the database directly on the network is way to slow. like 6 minutes to open and a couple of minutes for every operation - i really thought i was about to lose my job when i saw that = ( so thankfully replication was faster.

yup am aware that 2007 and 2010 they're using sharepoint. but i'm kinda doing covert IT ops cause it's more for a consulting project where everyone's IT scared as ever but databases offer a heap of benefits that excel can't. so if the database way goes seamlessly, no one will complain - just get the benefits. if something messes up on the IT end then i'm crucified = (((

hrmm, regarding the database on the network, when i need to C&R it i don't think i can bring it to my computer and then C&R then load it up. it would change the replicaID from what i understand (i don't understand it on an expert level though). so if i C&R the network database from my computer access it over the network, what problems will i face? why is this not recommended? thanks LSMconsulting!!
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 38813788
yup am aware that 2007 and 2010 they're using sharepoint
2007 and 2010 CAN use SP but they're not required to do so (and many have reported issues with that as well). I would strongly suggest you NOT go the SP route, if that's what you're after.

If you need the end users to be able to compact their own backends locally, you should develop a routine that allows the end user to compact their replica. This would involve shelling out from Access, performing the compact, and then reopening. Kaplan has SOON (Shut One Open New) that can be used: http://www.trigeminal.com/lang/1033/utility.asp?ItemID=8#8. Note you can compact and Access db with a simple SHELL call like this:

Shell "full path to msaccess.exe" "full path to the database" /compact

This will run the compact routine, so you could shell out (using SOON) to a new Access database, run the Shell routine above, and then reopen the database AFTER the compact.

However, given your network setup, it would seem that a bound Access application is not the best choice for your needs. It would seem that a true client/server app (with fully unbound forms) or a web-based application would be called for. Trying to force an Access app to comply with a poorly implemented network setup is a recipe for disaster
so if i C&R the network database from my computer access it over the network, what problems will i face? why is this not recommended?
The problems are generally corruption and data loss. If you have a poor network connection, as you've stated, then you will almost certainly experience this. I would strongly suggest you adopt a backup strategy which will enable you to recover from the inevitable data loss (i.e. always make a copy before you do the compact).
0
 

Author Comment

by:developingprogrammer
ID: 38853712
LSMConsulting, thanks so much for your help, it is greatly appreciated.

However, given your network setup, it would seem that a bound Access application is not the best choice for your needs. It would seem that a true client/server app (with fully unbound forms) or a web-based application would be called for. Trying to force an Access app to comply with a poorly implemented network setup is a recipe for disaster

i do feel i'm on a recipe for disaster already - my replica on the network keeps on getting corrupted = ((((( can you please share with me more in detail what you mean by a bound access application and how can i implement a true client / server app with full unbound forms? i cannot use web applications cause i'm only restricted to access.

my FE and BE is on each local machine and then it sync to the network BE when the user quits. the network is really really really killing me. it sounds to me that i need to do my FE and BE on the local machine like what i'm doing already, and then do a manual appending / deletion (synchronisation in other words) the my BE - is that what you mean by a true client / server app? i can't keep connecting to the server everytime a my colleagues edit a record or pull up a record. it's way too slow = (( i feel i'm all set for disaster already = (((((
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 84
ID: 38854366
An unbound application is one where the Form has no Recordsource and the Controls have no controlsource. When the user requests a record you query the database, cache the results in a recordset, and then display them on the form. When the user edits those records, you then send that data back to the database using SQL statements like INSERT or UPDATE.

Migrating to an unbound app is a LOT of work, and is not well suited for someone with little experience in programming. Unbound work if much different from the standard bound Access apps, and can really get overwhelming very quickly.

However, if you are stuck with Access then you're out of luck. If you can use Access with a true server backend (like SQL Server, Oracle, etc) then you could do this, but with Access you'll run into the same issues as you have now.

Have you considered using some sort of remoting technology, like Remote Desktop or Terminal Server or Citrix? That would be the quickest solution to your problems. With remote technology you setup a machine to manage the remote desktops, and your remote users log into that machine and run your Access app there. There's no network involved with the Access part - the FE and BE are both stored on the same machine - so the only latency is with the connection between the remote user and the server. You could do away with the replication, since you'd basically have all your users on the same LAN (i.e. the remote server).

There's also eqldata.com, which is a hosted service for Access apps. There's a monthly fee involved, but it works well by most accounts.
0
 

Author Comment

by:developingprogrammer
ID: 38856313
WHAO!!! EQLDATA!!!! that is really, really, really, really, REALLY COOL!! it's like Access is converted to an iPhone app!! haha, literally! i always found access quite useless outside the corporate zone cause we need connectivity and things to work online / everywhere. though it's not as portable as an iphone app but bringing it online is a huge step in the right direction!

though my nick is developingprogrammer, i am proud to say that i feel safe changing my name to developingprogrammerslightlymoredeveloped! haha, through all the great coaching and help from experts like yourself on EE LSMConsulting! = ))

so with regard to the unbound form - assuming i know how to do it (i've been reading up a lot and learning a lot too = ))    ), how will that help me in a multiuser environment where things need to be synchronised? that would lead to conflicts if i fetch a recordset to a local FE and then after the user is done loads the data back to the network BE right?

what i'm thinking is table based locking right? so when i fetch a table other users can't use it so i won't have conflicts moving forward. but then that doesn't allow my database to be a multiuser one right?

and another question is if i'm not fetching a whole table but rather a query that pulls certain records from a few tables, the right way to go is by record level locking is that correct? but what if another user runs a different query which slightly overlaps the aforementioned query (lets say 5 out of 100 records are the same so they are fetching those 5 records at the same time and "checking out" those records at the same time) what happens then?

how would i implement unbound forms in a multi user environment?

oh! just to share one thing i've done to make my whole set up more stable is that when one user is synchronising to the network database it creates a .ldb file of the network database. so when another user wants to sync at the same time, my FE now will check to see if there is a .ldb. if there is, it will tell the user to wait and try again later. i could also put a recursive code to sleep for 30 sec then resync again with a doevents way of getting out should the user want to stop the "redial" attempts at syncing.
0
 
LVL 84
ID: 38856920
how will that help me in a multiuser environment where things need to be synchronised? that would lead to conflicts if i fetch a recordset to a local FE and then after the user is done loads the data back to the network BE right?
The correct way to work with a client/server environment is to request ONLY the data needed at the time (generally one record), and to only hold the data as long as is needed - in other words, you fetch the record and present it to the user on the form. The user would then make edits and save the data back. When the user saves the data back, you'd use standard SQL to write those updates back to the table.

In cases like this, you either adopt a "last update wins" sort of strategy, or you use timestamps to determine if the record has been edited since you fetched it.

The "last update wins" is pretty much what is says - if two or more users are editing the same record, then the last user to save their changes wins.

The "timestamp" method uses things a bit differently. The tables would be modified to include a "timestamp" column. Anytime the record was modified, the timestamp column would be updated to the current server time (down to the second). When you fetch the record, you fetch the timestamp as well, and then BEFORE you write back to the database you compare the timestamp you have stored on the client side with the value in the timestamp of the table. If it is identical, then you save the changes. If it's different, you assume someone else has modified the data since you fetched it, and you take actions as appropriate to your application (i.e. resolve the conflict, alert the user, etc).

so when i fetch a table other users can't use it so i won't have conflicts moving forward. but then that doesn't allow my database to be a multiuser one right?
In a client/server setup you don't fetch an entire table. You fetch only the records you need, and work with those.

and another question is if i'm not fetching a whole table but rather a query that pulls certain records from a few tables, the right way to go is by record level locking is that correct?
If you're still referring to Access queries, then you don't really have the option of pulling only certain records. Access determines which records it needs to pull across the wire, and Access handles the conflicts and such.

However, if you're that concerned with users overwriting other data, then you'll need to rethink you design. In most cases, users don't work on the same record, but if they will do so in your application you would need to implement some sort of a push-pull stack, where users would "pull" a record off the stack, work on it, and the "push" it back. If another user requests the same record while it's "pulled", then you alert the user and refuse the request.

And - not to be blunt - but if you're just learning the ins-and-outs of the database world and programming in general, then I would STRONGLY recommend you seek out the help of seasoned professional who can mentor you on these things. From the content of this question (and others you've posted) you are obviously just starting out in the db world, and while you're eager to learn it's sometimes also good to understand your limitations.
0
 

Author Comment

by:developingprogrammer
ID: 38862428
i see, yup yup definitely things i should take into consideration!

yup i totally agree with you LSMConsulting on knowing my limitations and seeking a seasoned professional to mentor me, unfortunately my team is all superbly IT phobic and EE is my only avenue of mentorship at the moment. but yup, absolutely i have a lot to learn so just gotta be diligent and listen more to the advice of others on EE! = )
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now