VP-ASP :: Shopping Cart Software

Shopping Cart Software Solutions for anywhere in the World

US/Canada(Toll Free): +1 888 587 2278
Europe/UK: +44 (020) 7193 9408
Australia/New Zealand: +61 3 9016 4497

VP-Cart StoreFront Customer Forum

Home | Profile | Register | Active Topics | Members | Search | FAQ
Save Password
Forgot your Password?

 All Forums
 VPCart Forum
 Add-ons for VP-ASP
 Convert Access versions to SQL Server 2005/2008
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

41 Posts

Posted - September 16 2009 :  23:37:01  Show Profile  Visit SQLDude's Homepage  Reply with Quote
I am finishing up development for a generic Database Upgrade Conversion process involving the automated conversion of Access Table, View and Index Structures in a VPASP Shopping or any other Access System to their SQL Server 2005/2008 equivalents. It runs right from your server without the need for any outside assistance from external staff or consultants.

After creating a clone of your Access tables, indexes and views in SQL Server this data migration routine will selectively migrate all the data from your Access tables into your new SQL Server 2005/2008 tables without having to give out either your actual Access Database or the Table Contents or your passwords to anyone.

Here is the Reverse Engineering part of the process running against a system with almost 200 tables and almost 300 indexes and Views which is being done as a system acceptance test. Still a work in progress, but the core pieces are in place.


I do the automated reverse engineering of the Access Table DDLs (Data Definition Language) and convert them into SQL equivalent DDL Statements, because table structures and even extra tables might be slightly different for every VPASP customer and for every version of VP-ASP out there.

I am finishing the actual data conversion routine which would migrate the data from Access to SQL Server after these new SQL Tables have been created including any views or indexes. It is written entirely in ASP, with VB Scripting, the File System Object (FSO), the Dictionary Object, XML, DHTML, JavaScript right now and runs pretty quickly as you will see against a SQL Server 2008 Database just for the sake of an example.

It takes perhaps 15-20 seconds to reverse engineer almost 500 different database objects. There might be a total of over 2,000 columns involved in this example for the 170 tables and 270 indexes involved.

I have even come up with a way for you to run both VPASP systems in parallel using 2 different database connection files on the same server just to be sure that orders entered on the Access System and the SQL Server system produce the same results before actual cutover to production.

On a separate note:

Would there be any interest from any VP-ASP customers in a 100% SQL Server 2008 version of VPASP which used stored procedures instead of in-line SQL as part and parcel of this Access to SQL Server conversion process? Let me know. I have always found something between a 50% to 100% performance boost after converting from Access to SQL Server especially for larger databases for most complex ASP systems.

Access can have some performance issues after reaching the 250 MB to 400 MB levels as some of you may have discovered. Data loss due to Access Table corruption is always a possibility as well. Not quite as bad as MySQL with their data corruption and database disruption issues, but an issue nonetheless.

Are there any other issues in your possible Access to SQL Server migration which you would like addressed? There are other ways to upsize or upgrade Access to SQL Server but this method has always been the most reliable and the best that I have ever seen in actual operation.

John (a/k/a The SQL Dude)
[email protected]
(This is a VP-ASP Demo Site)

  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Snitz Forums 2000