migrate access to SQL server

[associates]

Hi,

I was wondering if anyone might be able to help me here. I'm very new to SQL server and has been asked to convert microsoft access database .MDB to SQL server. In another word, i'd like to migrate access to SQL Server. It's not a huge database. The size of it is roughly 6MB. The reason for migration is simply because our clients don't have microsoft office running on their system. They use SQL Server. This's a new challenge to me as i'm more comfortable with using Access now.

I have been searching and studying it thru the online tutorial about what it's and what it does. I have also downloaded and installed the SQL Server 2005 Express edition because it has GUI Interface and it does have the ability to produce reports like Access does. Now my question is how do i start? how do i migrate it to SQL Server for a testing sake?

I really need some guidance here please.

Thank you in advance

 

[shadow9449]

Hi,

I was wondering if anyone might be able to help me here. I'm very new to SQL server and has been asked to convert microsoft access database .MDB to SQL server. In another word, i'd like to migrate access to SQL Server. It's not a huge database. The size of it is roughly 6MB. The reason for migration is simply because our clients don't have microsoft office running on their system. They use SQL Server. This's a new challenge to me as i'm more comfortable with using Access now.

I have been searching and studying it thru the online tutorial about what it's and what it does. I have also downloaded and installed the SQL Server 2005 Express edition because it has GUI Interface and it does have the ability to produce reports like Access does. Now my question is how do i start? how do i migrate it to SQL Server for a testing sake?

I really need some guidance here please.

Thank you in advance

Did you do a search? Here is one place where this is addressed:

http://www.access-programmers.co.uk/forums/showthread.php?t=111984


SHADOW

 

[associates]

Hi Shadow9449,

Thank you for your reply.

"created the Access application with client/server in mind". OK..., but unfortunately, when i created the database, i wasn't aware of all of these client/server stuff before. Hence, i did not have client/server in mind. Is that mean i have to start it all over again using SQL Server?

What should i do now?

Thank you in advance

 

[shadow9449]

Hi Shadow9449,

"created the Access application with client/server in mind". OK..., but unfortunately, when i created the database, i wasn't aware of all of these client/server stuff before. Hence, i did not have client/server in mind. Is that mean i have to start it all over again using SQL Server?

What should i do now?

You should read around this excellent forum for advice in redoing your application so the forms are not based on tables. There are a few other considerations as well.

SHADOW

 

[wonn1377]

I use data loader for migrating almost any data, it helps me to convert access to SQL, MS access to MSSQL, mysql, csv loader, foxpro and MSSQL to MS access, MYSQl, CSV, foxpro etc. In my view this is a best Data Migration Tool

Download Free : http://www.dbload.com

 

[SQLDude]

[FONT=Verdana, Arial, Helvetica]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.

http://www.21stcenturyecommerce.com/SQLDDL/ViewDBTables.asp

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)
sales@designersyles.biz
(This is a VP-ASP Demo Site)[/FONT]