[AccessD] upsize mdb to SQL Server

[Jim Lawrence]

Hi John:

I personally think that if a developer is using MS Access to connect to another database than the default preconnected MDB, it is better to build the appropriate database than to use some upsize wizard, if required and to use an ADO type connection string. The following is some links to a few tested connectors strings, to various datasources:

http://www.carlprothman.net/Default.aspx?tabid=90

https://www.dofactory.com/reference/connection-strings

https://www.connectionstrings.com/

etc etc etc...

The beauty of using ADO connectors is that many Datasources can be attached (simultaneously) and detached at any time or at any position within the application. This process also allows a reliable managed data connection from a remote site or from an unstable connection. (Note that the ADO program is already available on all versions of MS Windows...95 to 10 so no external searching or installation is required).

I do not know whether the following link is accessible at this moment but if it, it will open to an example that shows how to optimize an ADO connection string and apply reporting. I wrote it so I make no absolute claims but all the code has been pre-tested:

http://dba.creativesystemdesigns.com/newsletters/newsletter112003/0311UnboundReports.asp.html
http://bit.ly/33rztkA
  
HTH
Jim

----- Original Message -----
From: "John Bodin" <jbodin at sbor.com>
To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com>
Sent: Wednesday, November 6, 2019 8:44:08 AM
Subject: [AccessD] upsize mdb to SQL Server

I was wondering if someone could send me some links to docs & how tos, or share advice on converting access back-end mdb to SQL Server, and continue to use access front-end to access that data.  I know there are some data types that change or don't convert (yes/no fields I believe is one) and Queries and SQL statements need to be re-written, among other things.  I have a long time access database a company is running successfully that needs a face-lift and more robust BE.

This will be a multi-phase year-long project and I was hoping to upsize the BE data to SQL Server and initially, just link the FE to those tables to run the program as is (I know there will be no speed gains but hopefully data integrity will be solid and users will not know that BE has changed.)  Then proceed to convert FE to use ADO(?) while another developer writes a Mobile/web-based app for customer's technicians to use, that integrates with the SQL data (tried ASP.Net with MDB file and although it worked, it wasn't reliable enough when a lot of people were on the mobile piece.)  Once Mobile piece working and FE optimized to use ADO or something else, app to integrate QuickBooks w/Access will be written, and when that is done, Access FE will get re-written for the web.  Will not be an overnight project, everyone understands that.  And data is small enough that SQL Express probably can be used, at least initially. Have room left on host for test VM's for project.

Appreciate any guidance.

John



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com