Programmatically convert Access mdb to SQL 2005 Database

[Piggy]

Hi all,

I need to programmatically convert user supplied Access database files (mdb)
to SQL 2005. Just to explain, I need to create a customized version of the
Access "Upsizing Wizard".

The user interface must be web (using ASP.NET and VB.NET), but if needed, I
can also store the user input (and the mdb file) and execute the conversion
process asyncronously with a windows form application or service.

I can use all the SQL Server 2005 Standard Edition technologies.

Does anybody have an idea on how to approach the conversion process?

 

[Armin Zingler]

Hi all,

I need to programmatically convert user supplied Access database
files (mdb) to SQL 2005. Just to explain, I need to create a
customized version of the Access "Upsizing Wizard".

The user interface must be web (using ASP.NET and VB.NET), but if
needed, I can also store the user input (and the mdb file) and
execute the conversion process asyncronously with a windows form
application or service.

I can use all the SQL Server 2005 Standard Edition technologies.

Does anybody have an idea on how to approach the conversion process?

Don't know if it is the best way....:

1. Programmatically create a linked server (which is a link to the mdb) by
using sp_addlinkedserver.
2. Loop through all tables and execute a SELECT INTO with the linked
server/table as the source and an SQL database table as destination.

Optionally, have a look at OPENROWSET that allows specifying an OLE DB
provider...:
http://msdn.microsoft.com/en-us/library/ms190312.aspx


Maybe you find something here, too:
http://msdn.microsoft.com/en-us/library/ms175937.aspx



Armin

 

[Piggy]

Thank you very much Armin!!!

Your suggestion sounds to be very effective in solving my problem.
I will get deeper on it and will check back with the results of the tests.

Thank you again
Alberto

 

[Rick Sawtell]

Thank you very much Armin!!!

Your suggestion sounds to be very effective in solving my problem.
I will get deeper on it and will check back with the results of the tests.

Thank you again
Alberto

You will run into issues with the "autonumber" fields in Access converting
to IDENTITY columns in SQL Server.
Indexes will not hop accross either. You could use a similar approach with
the indexes, however, Access has a knack for creating indexes on just about
anything it recognizes.


Just a couple of quick thoughts.


Rick Sawtell

 

[Piggy]

Hi Rick,

I am aware there will be some problems about any access feature different
from the plain table. I will try to avoid the complete replication of the
access objects, asking more user inputs (such as asking the columns to be
indexed) and modifying on the fly the target SQL DB structure (with smo).

However, as for the access sample files I received, the structure seems to
be very simple, just similar to excel worksheets with no other then a PK.

Thank you
Alberto

 

[gillardg]

I'm not sure but try "DTSWizard.exe"

http://msdn.microsoft.com/en-us/library/ms187710.aspx

http://msdn.microsoft.com/en-us/library/ms188664.aspx

 

[Lingzhi Sun [MSFT]]

Hi,

Thank you very much, Armin, Rick and Alberto, for your enlightening ideas.

Piggy, you can follow Armin's suggestion. First create a linked server in
the SQL Server for the Access database file, and then use T-SQL to copy
the data. Or you can also use the Import and Export Wizard of SQL Server
to convert the data, as Alberto has suggested.

For some supplement, we can use this tool, SQL Server Migration Assistant
2005 for Access V4.0, to make the data conversion as well. This tool is
especially designed to migrate databases from Microsoft Access to Microsoft
SQL Server 2005. For detail, please see
http://www.microsoft.com/downloads/details.aspx?familyid=4ECD1E67-C64E-49E6-
821E-C4D83D9D5FED&displaylang=en
&
http://www.microsoft.com/sqlserver/2005/en/us/migration-access.aspx

If you have any further questions regarding this case, please be free to
let me know.

Have a great day!

Regards,
Lingzhi Sun ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

MSDN Managed Newsgroup support offering is for non-urgent issues where an
initial response from the community or a Microsoft Support Engineer within
2 business day is acceptable. Please note that each follow up response may
take approximately 2 business days as the support professional working with
you may need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.