How to Not sync 5 tables in a 4 set replica? (1 Viewer)

[bignose2]

Hi,

I am trying to find an easier way to rebuild my system of 4 replica's where I have 5 tables that can't be syncronised because the data changes dramatically and would cause many conflicts.

Access 2007 but using 2003 data to keep replication & MDE (runtime ability) as some computers don't have full office copies.

If I make any small design changes or rebuild to single master because system might get unstable (Rarely, though) I have to:-

1) Import tables into existing design master mdb backup. Delete all other replica's.
2) Make as MDE
3) Create Replica's (over the wireless LAN)
4) Edit the properties of the 5 tables to not replicate.
5) Sync them all, then from the main computer open the other replica's via the LAN, import the unreplicated tables and repeat.
This is painfully slow as running access effectively from the networked computer is asking a lot even though computers are fast & network running pretty fast.

Is there an easier way, I am trying to avoid major design changes as I programmed the system many years ago and whilst done a few tweeks not really that sharp on the programming nowadays. Reading other threads people seem to hate replication but I don't think I have the skills to make major changes any more. This system has worked for 10'ish years, never, ever had a full system failure. Over reliable wireless LAN, again never crashed due to a WiFi failure.

Rebuilt a few times a year as perhaps not running correctly but generally because I change something.

It there a function that could automatically check & change the property of these tables when each replica is started.
I am guessing maybe front end back end stuff, but I am not familiar with this and suspect still issues.

I don't mind building back from the mdb but the importing tables etc. especially if I was away on holiday and system died, I could never talk anyone through the whole procedure.

Any ideas would be appreciated, but keeping as simple as possible.

Many thanks I/A.

 

[bignose2]

Following on from my own question,

It seems the best way to go is to split the database, put the tables that need to stay separate in the Front End & just Link the other table to a central computer and stay away from any replication.

Couple of things so far. Linked tables can't use open-table so had to replace with open-dynaset, not sure of the significance of this yet but seems to work (bit worrying if something is not working underneath in the way it should) although I do have a number of seek commands but yet to find where they error.

I did like the idea of having 4 copies on the go, I could always recover 99% of data if there was a problem, now with only one set of data!. Backing up the BE seems to be little tricky. Also seperate laptop would always work if power cut or main computer failure but this will be useless if can't access the main desktop which holds the backend.

Also some computers use the runtime MDE so can't get at link manager if I needed to move the back end. If I could get a backup, if I can't refresh links to e.g. laptop also not too safe.

There does seem to be some code for linking but looks very complicated, not keen of simply copying others without any understanding, Too much a stake.

If anyone has any comments on my observations so far.

Greg.

 

[dfenton]

You seemed to have figured out the issue of splitting and not using replication to push out changes to your front end. I see only one remaining question, and that's the table-type recordset question.

Dynaset is the PREFERRED recordset type, and is what you should have been using even with local tables. It is not slower.

If you for some reason really, really need table-type recordsets, you can open the back end database and create your recordset from that database variable. Assuming that tblPerson is a linked table in your front end and you want to open a table-type recordset directly on the back-end table, you'd do this:

  Dim strBackEnd As String
  Dim dbBackEnd As DAO.Database
  Dim rs As DAO.Recordset

  strBackEnd = Mid(CurrentDB.TableDefs("tblPerson").Connect, 11)

  Set dbBackEnd = DBEngine.OpenDatabase(strBackEnd)
  Set rs = dbBackEnd.OpenRecordset("tblPerson", dbOpenTable)
  [do whatever it is you want to do]
  rs.Close
  Set rs = Nothing
  dbBackEnd.Close
  Set dbBackEnd = Nothing

But let me stress again: you really don't need table-type recordsets.

 

[bignose2]

Hi,

Many thanks for that info. I probably only used the recordset type from example code back in the access 97 days not really knowing what or why.

So far simply using find/replace throughout the project seems to have it working fine, but was worried by simply swapping (I think 30+ finds) could cause something I am not immediately aware of.

I have quite a few seek commands which I believe will not work, I assume will error If actually used in the running of the program, they may all be reduandant. I have tested quite a lot but there are quite a few things that might only show up in extended use.

My headache at the moment is trying to find a way to make a backup of the BE onto the notebook (from the Main Computer) so that in the event of the Main BE computer failing, I would have a backup on the laptop I could then Link the Table to, and at least have some sort of reserve (I know the data might be a bit old & would loose any changes done on the original Main BE) also restore link later, but better than nothing.

Being A Runtime MDE no access to the link manager and also not sure how to do a Back UP of the BE without the office/manage/button.
Thinking about it could do from Main computer & save over the network, would like to automate it so might need code anyway.

There does seem to be quite a lot of example code on re-linking so prob. can work through this.

Regards.

 

[dfenton]

In regard to Seek:

It's Jet-specific, so never a good idea to use it, since it won't work with any other database engine.

Secondly, the circumstances in which you need to jump around a recordset a large number of times and need the performance benefit of Seek over FindFirst are very, very, very few. My bet is that you shouldn't be using either Seek or FindFirst and that you should instead be using a WHERE clause when you open your recordset.

But that's just a guess based on experience.

You should take the opportunity to get rid of Seek. I would wager that it's very unlikely that it was the right choice even in the original context with local tables.

 

[pkstormy]

I fully agree with David. A WHERE clause is always preferred versus a filter or any other seek/find type command. I've seen lots and lots of issues with filtering but never with a where type statement.

With a where clause, you're essentially limiting the recordset to the criteria in the where clause. Whereas with a filter or other type seek/find commands, you're dealing with the entire recordset which can be slow/problematic (especially when subforms are involved.)

If you're interested, I created some helpful tips here on using the where clause and how to open forms/recordsets efficiently:

http://www.dbforums.com/microsoft-access/1605962-dbforums-code-bank-6.html

 

[bignose2]

Many thanks for all your help, I have not actually had any errors ref. Seek so wonder if it was old redundant code anyway.

Working well through setup now, quite a few issues have come up, checking links but usually solved.

My main issue now is speed, for the reasons above I have replicated the back end and it works, however it has slowed down quite a lot.
Even thought the linked table is on the same PC & same folder most forms but one in particular takes 4-5 seconds to completely load when it use to take about half a second. If I test with a linked but not replicated BE it is as fast as before.
The form is complicated with lots of controls & functions on some of them but I have a fast Quad PC.
I am guessing to do with the replication but before I spit the Database it was instant to load but was still replicated.

Any idea's?

 

[dfenton]

Have you deleted the table links and recreated them since you replicated the back end? If not, do so, because the links store metadata about the target table that doesn't necessarily get updated when you update the connect string alone.

 

[bignose2]

Many thanks,

Sorry about the double post but bit desperate & worried this old thread might not get seen.

Being a bit thick here, not sure how to re-link to a back end if the tables are completely deleted in the FE. Will search for it but if you have a quick guide would be appreciated.

Have re-linked using link manager as if picking a new location but I assume that is not what you mean.

Greg.

 

[bignose2]

Silly me,

Just realized you use external data/import link.

Deleted & re-linked my main DB & So far not helped,

Do the back end replicated tables know they are linked to FE's ?
i.e. could there still be a connection here although all the other FE's have their own replicated BE on there local computers?

It seems to be the fact the BE is replicated, if its not is seems as fast a before.

At the moment I have the Track Auto Name Off in my MDE but original design MDB it is still on, (by default, I have not changed) this is the one I just tested the delete & re-link on, Should this be off to fix any link issues,
as per my double post this option concerns be, when I turned off & then back on lost of forms data seemed to dissapear.

Can any harm come of turning Track Auto Name OFF & leaving off?

Greg.

 

[bignose2]

Seems to be purely down to replication.
All on Local computer, all links local C:\

FE I deleted all linked tables, create a fresh BE, import tables un-rep'd

Link to BE and if runs great.

Replicate BE, compact etc. Instantly slows down
Can delete links agains & link to already rep'd BE, still slow.

It is slower on most things but perhaps not that noticable but my mostly used form has 4 subforms & quite a few calculated controls with Dlookup.
Prob. not programmed very well but I don't why replicating it can make such a difference. Before the whole DB was spilt & all replicated it was even faster than ever.

Not sure if a clue but if I link to an UN-replicated BE over the LAN it seems to run at an identical slow speed as my local Rep'd BE.
(Over a wireless LAN I am quite impressed at its speed)
I have done all I can to expect the FE to think the BE is still local, how or why it could change & somehow think its over the LAN once rep'd I dont know... Clutching at straws.

Driving me mad that I have done the right thing by splitting and now regreting it. It is un-usable in its current state & may have to go back to the original.
I just do not have the time to consider a whole new angle.

I may end up not using a replicated BE, keeping the BE on the main computer where speed is most important but linking 2 other computers over a wireless LAN seems risky, especially as one does have a habbit of losing connection, rarely & have never,ever corrupted whilst syncing but thats a few seconds a few times a day.

This is what I was really trying to avoid so if there are any other idea's I would be very grateful.

Greg.

 

[dfenton]

Well, you just revealed the key piece of information: you have a wireless LAN.

You can't use Access across a wireless LAN. You can't do a direct synch across a wireless LAN safely.

You need to set up a Terminal Server and run the app remotely. That is the only workable solution I can see.

 

[bignose2]

Hi,

That's why I included ".. have never,ever corrupted whilst syncing but thats a few seconds a few times a day"

Been running my old wireless system perhaps 8+ years, (pre Office 2007) Corrupted data a handful of times & actually never at the time of syncing (But prob. due to it). Never not been able to recover & rebuild data easily. Since access 2007 it has been a bit more flaky hence my attempts to make it "better". So despite being wireless I can consider this very reliable.

But as I say, a few seconds syncing compared to constant connection is why I was keen to keep replication, as well as very current backup copies.

I am currently going with the unrep'd version over wifi to the main computer. Is working well & fast'ish. If I disconnect from network seems to deal with it quite well, warns of lost connection & waits till back online, no lost data. Will have to set up some more robust backup procedure's & put backup copies on other computers regularly.

Thanks anyway.

 

[dfenton]

You are asking for trouble. Get off WiFi as soon as possible. It is just not viable with Access except with a server-based back end, i.e., something other than an MDB/ACCDB file.