Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Replication without Replication (Access 2K)

    I have an Access Database that was set up for replication. I am in the process of migrating the Backend data to SQL Server 2000. The current database is laden with a lot of tables that I want to keep both local and on the server. I want to only have to use one main table and several views (4-5) from SQL Server while keeping the other tables (which for the most part will not change) in the local Access frontend. However, there will be occassions where I want or will need to update the local tables, for example I would need to add an new employee. Since this will not be a daily occurrence, I don't want to link this table to SQL Server due to speed issues (I need this frontend to operate as fast as possible since it is being threatened to be redesigned in ASP.)

    My initial idea is to maintain links to all the SQL Server tables, but use the local tables in Access for the forms that have combo boxes (the majority if not all of the reasons for these tables.) I would then utilize a series of queries to compare both for additions to both and then somehow combine all the unique records and ensure that they both the local tables and SQL Server tables are the same.

    Another idea would be to allow the user a way to update the SQL Server table that they needed to through a linked table and then force the updated information to the local table?

    I hope this makes sense.

    I am using SQL Server ODBC driver for connection.

    Additionally could someone point in the direction of code that would automatically update the linked SQL Server tables at startup.

    Thanks in advance for any assistance, advice, or comments.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Replication without Replication (Access 2K)

    Several comments on your post:<UL><LI>If your database has been truly replicated, then you probably should actually remove replication - there is a topic on how to do that in the Help files for Access 2000. It is a rather tedious process, as well as being time-consuming. In addition, replication works best with data - we almost never use it with Front Ends.<LI>Having lookup tables used with combo boxes in both the front-end and the back-end is a strategy often recommended to maximize response time, but in some recent performance tuning it appears that if you have a fast (100MB) LAN with a workstation with lots of RAM and a reasonably robust dedicated SQL Server, the gains are minimal. The thing you do want to avoid, is doing joins on a table in Access and a table in SQL Server, especially if the SQL Server table is large. The reason behind that is that SQL Server must pass the entire table to Access so Jet can do the join. If both are in SQL Server, it does the join and then passes only the requested data.<LI>If you decide you want to put lookup tables locally, the most common strategy is to copy the SQL Server table to Access each time the front-end is started. If that isn't sufficient, then you might consider putting a timer task that checks every so often for an update to the table, but that typically involves keeping a version value (typically a date) in a separate table in both SQL Server and the front-end. In our applications, we often put lookup tables in the front-end, and when updates are made, we deploy a new copy of the front-end, but that works only if the tables are pretty static, and works best with some sort of deployment tool.<LI>Finally, there is a topic on the subject of updating the links to SQL Server tables on the Access Web at http://www.mvps.org/access/tables/tbl0010.htm. Frankly, we find it infrequent that we have to relink SQL Server tables, and just use the manual approach in most cases. We do occasionally have a test version of the SQL Server database, and in those cases when we need to deploy a new front-end we simply relink manually, as we nearly always put all the tables in the same SQL Server database.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replication without Replication (Access 2K)

    Thanks for the response.

    I have removed the replication from the version I am linking to SQL Server, which originally housed both the forms and tables. The front end is now split. I was trying to avoid having to redeploy the front end when the lookup tables changed, but will look into updating the tables at startup. I assume you have all the SQL tables linked and then just run a series of Delete and Append queries to update the local lookup tables?

    I will check out the link tonight as well.

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •