Replication with front end, back end database

[Cornerstone]

I have a back-end database on a server (tables only) and a front-end design master database also on the server (Queries, Forms, Reports, Macros and Modules and some Temp tables as well as tables with large lists of standard selections.). I have replicas created on several PC's that are replicas of the design master which points to the back-end for the data. (Get that?) So all in all I have 3 databases. My goal is to minimize network traffic and be able to work on design changes while users begin to use this system. <br><br>Server<br>Back-end<br>Front-End Design Master<br><br>Local<br>Replica-this is the database they open<br><br>My question is: <br>Does anyone have any recommendations/concerns regarding this setup. Any experience with replication/ frontend/backend setups you would like to share would be greatly appreciated. We will be going live with this system in about 2 months.<br><br>I suspect about 5 concurrent users. We will import information from text files downloaded from InfoLease and generated Legal documents out of Word from the system.<br><br>Thanks.

 

[johnk]

Just a caution, we ran into serious locking issues with 5 users and the Jet Engine.&nbsp;&nbsp;Unless your use of relication is comprehensive enough to nearly eliminate concurrent hits in the server resident database, you may also get some locks.<br><br>Our solution was to go to SQL Server with its row locking.<br><br>Please post any relevant info you come up with about using replication with Jet.&nbsp;&nbsp;We have a client interested.&nbsp;&nbsp;My post of some weeks ago asking for feedback from experienced users of replication went without a response. <p>John Kisner<br><a href=mailto:jlkisner@jlkisner.com>jlkisner@jlkisner.com</a><br><a href= > </a><br>

 

[elizabeth]

John, just curious, did this happen with Access 2000's new record-level locking feature? I thought this was supposed to be a great new feature...

 

[Cornerstone]

FYI<br><br>I converted the database to a replicated system today and have noted the following: I had several procedures that used For Each loops to loop through all of the fields in several back-end tables and in replicated local temp tables. Well all replicated tables have hidden system fields added to them and this screwed up several procedures. However, links to tables in the back-end database did not have any extra fields showing. Therefore my temp tables and the back-end tables no longer had the same number of fields.&nbsp;&nbsp;I added code to check for &quot;s_&quot; at the beginning of each field name to make sure not to loop through those fields. If you display system objects you can see the hidden system fields added to your tables.

 

[johnk]

Elizabeth,<br><br>Our experience is all with Access 97 & previous.&nbsp;&nbsp;I didn't even know that Access 2000 had row locking.&nbsp;&nbsp;I presume that this is a feature with Jet engine with WIN/2000 also?<br><br>By the way, so far we are very happy with MSDE as a replacement for Jet.<br> <p>John Kisner<br><a href=mailto:jlkisner@jlkisner.com>jlkisner@jlkisner.com</a><br><a href= > </a><br>

 

[elizabeth]

Cornerstone, but documentation I'm looking at suggests that when you when you consider your replication scheme, you are choosing whether eac object will be local <i>or</i> replicable.&nbsp;&nbsp;I'm hoping to do some testing with replication soon, so I wonder why you might want to replicate temp tables?<br><br>John, thanks, may I ask where did you get documentation for the MSDE and how do you handle it without the admin tools? I have seen a bit of MSDE documentation but most of it is supposed to be covered in the SQL Server documentation, and that never mentions how you manage without the tools that it notes are provided with every version of SQL Server except MSDE.

 

[Cornerstone]

Elizabeth,<br><br>The TEMP tables do not belong in the back-end database with the other data tables and they are not there. They reside on the front end design master on the server and in the replicas on the pc's. Keep in mind that the client only uses the desing master when they need to synchronize. As far as the users setup is concerned the user uses a local database that links to database tables in the back-end database. I want to be able to continue to improve the form designs, add features and possibly adjust fields in the TEMP tables so I need a design master on the server with the TEMP tables in that they can synchronize after changes have been made.

 

[elizabeth]

OK, I'm feeling like a slow learner ... so why aren't the temp tables for the front end being created and destroyed on each user's own desktop, instead of on the server?

 

[Cornerstone]

Ok. Here we go..<br><br>You have a database residing on your PC. Lots of forms, reports, queries, a few modules. Most of the data resides on the server and your tables have links to that database. <br><br>For now, pretend that that is all you have.<br><br>You need to import info from text files, clean and process that info and then append that data to your data tables. As the programmer you could programmatically create temp tables on the client, import the text files, clean the data and append the records. Then delete the tables.<br><br>However what will you do if you want to change the design of a form or change a temp table so you can import more information?&nbsp;&nbsp;Well you could create a design master on the server that everyone could synchronize with to get the updates to the program.<br><br>So that is what I have done. As far as the temp tables are concerned, yes I could create them programmatically on the client, import, clean , append and then delete the tables but I am the only one in my company that knows how to do that. If I want to delegate some of this to someone and want them to say, change datatype of a field in the temp table all they have to do is go into the design master and set the property of the field and then have everyone synchronize. Otherwise I have to go into the design master and change the code to have the table created differently.&nbsp;&nbsp;Does that make any sense?<br><br>By the way, I appreciate all the posting you do. You have some good ideas.

 

[johnk]

Elizabeth,<br><br>We are now primiarilly a VB and SQL Server shop.&nbsp;&nbsp;Some of our applications are created as stand-alone apps using the Jet database for smaller users.&nbsp;&nbsp;We customarilly shipped them with only the run time abilities without the user needing to license Access.&nbsp;&nbsp;Of course, they had no ability to do many of the administrative DB chores, but typically had no need to do so.<br><br>Similarly, with MDSE we can distribute a run time SQL Server (with some limitations) with no royalty fee.&nbsp;&nbsp;We do all of the developoment administration tasks in our shop with our SQL Server 7.&nbsp;&nbsp;Many of the on-site administration tasks for MSDE such as adding tables & columns, changing column widths, etc. can be done through SQL statements.&nbsp;&nbsp;These, in turn, can be placed more easily in our installation scripts so that updates to table schema can be accomplished without the mess of creating a whole new database and copying all records as had to be done sometimes with the Jet database.<br><br>I am not myself an in-depth techie (any more), so be sure and double check any of the particulars.&nbsp;&nbsp;However, our end result is good so far.&nbsp;&nbsp;We expect to begin shipping apps with MSDE this month.<br> <p>John Kisner<br><a href=mailto:jlkisner@jlkisner.com>jlkisner@jlkisner.com</a><br><a href= > </a><br>

 

[elizabeth]

<b>John,</b>&nbsp;&nbsp;that's good to know. FYI, Help in v2000 says: &quot;Record-level locking:&nbsp;&nbsp;A Microsoft Access database now supports record-level locking, in addition to page-level locking (which locks all records on a 4K page). You enable the locking level with the new database option, Open databases using record level locking (Tools menu, Options command, Advanced Tab). The actual level that is used depends on how the Access database is programmed.&quot; and that whatever you choose becomes the default for forms, datasheets and code that uses a recordset to loop through records, but not for action queries or code that runs SQL stmts.<br><br><b>Cornerstone,</b> thank you for the feedback, and for your many contributions as well. This is a great forum not only because there are lots of good ideas here but also because you don't get flamed or shamed when you <i>think</i> you have the answer but it's just &quot;another dumb idea&quot;! Well, <i>hardly</i> ever!&nbsp;&nbsp;:-} Thanks to your thorough explanation I believe I just had a different concept of a temp table than you.