[AccessD] Anyone used dbSync?

Mon Nov 2 12:39:59 CST 2015

Fred, on the cable change, the Cat5 and Cat7 ratings will not help you if the switch the server is connected to can only go 100meg or even a gig (Cat 5 will still get you fine throughput at a gig).  The switch will be the bottleneck. If on the other hand you have a 10gig switch that the server is connected to and 10gig ports on the server, then the better cable will aid you there, and then whatever speed network card the users have (typically a gig) will dictate how much and how fast they can connect to the network to send/receive data.  Good luck.

John

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Fred Hooper
Sent: Monday, November 02, 2015 1:31 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Anyone used dbSync?

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
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com