[AccessD] Anyone used dbSync?

[Fred Hooper]

Hi Dan,

That's what I did, but it brought the critical form-opening time from 10
seconds to 3 minutes on their network (instant on my PC, so I was
surprised).

However, since then I've (1) changed from nvarchar to varchar (hoping to
reduce the data quantity by 40% or so); and, (2) asked them to upgrade
the single cat-5 cable bottleneck (100Mhz) from the server to the switch
to  2 cat-7 cables (600Mhz each). I'm hoping that the combination of the
two changes will provide the necessary speed. However, I can't test it
until next week so I'm trying to explore alternatives.

However, the increased speed just postpones the problem, as their
business grows they will have the problem again. I've got to do more to
move the processing from the FE to the server. I've converted their
queries to views but dragging whole views across the network for Access
to use the few records it actually needs is not going to provide a long
term speedup either.

I've tried three ways to move the processing to the server:

 1. On-the-fly modified passthru queries, but you can't edit through these.
 2. On-the-fly created separate views for each user (a/k/a "user view")
    with the link to the server's table modified to point to that view.
    To avoid SQL Server's limitation of editing only one table through a
    multi-table view, I retained the queries locally (although I created
    them on the server too, for when they are useful) -- and created
    those user views for each table needed by the local query. Their
    second most commonly used form has 9 subforms, some fed by queries
    -- requiring a total of 12 "linked tables" fed by user views.
 3. ADO recordsets as the forms' Recordset. This is noticeably quicker
    than 2 on my PC; the differences will probably be less when masked
    by network traffic, but I prefer this simpler solution so far. For
    this, my solution to the multiple-table problem is to force updating
    after every field's change (kludgy, but it works). In retrospect, I
    could have done this with 2 and had only 9 user views, but that's
    not much of an improvement from 12.

Unless someone has some experience with dbSync to guide me, I expect I'm
going to try it unless the network change and the varchar's speed the
program up enough to allow the direct switch and gradual changes from
there. BTW, I'm working in Access 2002 & 2003 and SQL Server 2012.

Best,
Fred
> Dan Waters <mailto:df.waters at outlook.com>
> Monday, November 02, 2015 12:40 PM
> Hi Fred,
>
> It looks to me like you may not need the synchronization product. I
> went to
> the site - what it does is keep two databases (or two backends) in synch
> with each other on a periodic basis (for $149). But it sounds like
> what you
> need to do is this:
>
> 1) From your Access BE, upsize the Access tables to SQL Server tables.
> (do a
> search for 'upsize Access tables to SQL Server tables')
> 2) In the Access FE, remove the FE table links to the Access database with
> the data tables.
> 3) In the Access FE, create ODBC links to the same tables in the SQL
> Server
> database. (do a search for 'link Access to SQL Server)
> 4) Try out your application - it should run exactly like it did with the
> Access tables.
>
> You won't need to do this again so a synchronization product won't be
> of any
> help. Once it's all working, you won't need your Access BE file anymore.
>
> HTH,
> Dan
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Fred Hooper
> Sent: Monday, November 02, 2015 9:47 AM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Anyone used dbSync?
>
> I found this product: https://dbconvert.com/access/mssql/, which claims to
> keep an Access database in sync with a SQL Server database. Has anyone
> used
> it?
>
> I'm converting the record-keeping program for my brother-in-law's software
> program from an Access back end to a SQL Server back end. If the program
> works as described, it would reduce the risk of the switchover.
>
> I'm not interested in their convert product as I've written something I'm
> happy with and that gives me control at the points where I need it.
> As I have plenty of time right now, I've tried every permutation of how to
> link the front and back end that I've read or thought of; this was
> necessary
> as the first test of simple links to SQL Server brought a critical
> form from
> 10 seconds to 3 minutes -- so I did a lot of rethinking. If anyone is
> interested I'd be happy to provide more detail.
>
> Thanks,
> Fred
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> Fred Hooper <mailto:fahooper at gmail.com>
> Monday, November 02, 2015 10:47 AM
> I found this product: https://dbconvert.com/access/mssql/, which
> claims to keep an Access database in sync with a SQL Server database.
> Has anyone used it?
>
> I'm converting the record-keeping program for my brother-in-law's
> software program from an Access back end to a SQL Server back end. If
> the program works as described, it would reduce the risk of the
> switchover.
>
> I'm not interested in their convert product as I've written something
> I'm happy with and that gives me control at the points where I need
> it. As I have plenty of time right now, I've tried every permutation
> of how to link the front and back end that I've read or thought of;
> this was necessary as the first test of simple links to SQL Server
> brought a critical form from 10 seconds to 3 minutes -- so I did a lot
> of rethinking. If anyone is interested I'd be happy to provide more
> detail.
>
> Thanks,
> Fred
>