[AccessD] Anyone used dbSync?

Mon Nov 2 13:09:46 CST 2015

Hi Fred,

Are you logged onto their network using a VPN and Remote Desktop from
outside that network?  If so, your massive increase in speed is expected.
ODBC table links connected to a SQL Server database from outside the network
will never have acceptable speed.  No table link, Access or ODBC, ever has
acceptable performance using a VPN on the internet.  

If the application will only be actually used within the network, then just
do the testing on identical tables on your PC.  When you're done just ship
the FE to your brother in law and let him know he'll need to relink the ODBC
table links to SQL Server on his server.

If that doesn't work there are three other paths:

1) if possible, log into their network and do all the work on their server.
2) You could rewrite all the FE code using ADO.  This does not require table
links and does provide good performance.  About 10 years ago I started to
learn this but I no longer remember
3) Download Visual Studio Community 2015 (free) and learn to write a
WinForms app.  Develop on your PC using SQL Server Express (or SQL Server
Developer).  Very similar look and feel, great performance, and has the same
purpose as an Access app.

Short term 1 is best, 2 is OK.  Long term 3 is best.  2 used to be called an
Access Project but today, learning Visual Studio is a better path long term.

Still, unless you are going to keep tables in an Access .mdb file and in
identical tables in SQL Server synchronized continuously, I don't believe
that dbSync would be helpful.

Good Luck!
Dan

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Fred Hooper
Sent: Monday, November 02, 2015 12: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