Access DB Support

Aug 30, 2011 at 8:17 PM

I know about the upsizing wizard as well as the Migration Assistant but would like to be able to take an Access DB and "upsize" it to SQL Server via a asp.net website.

Any chance of supporting Access DB?

Coordinator
Sep 1, 2011 at 9:14 AM

I checked and the schema reader does read the Northwind Access database.

The standard OldDb provider provides incomplete information though- no primary and foreign keys, and the column datatypes are the OleDbType numeric value.

I can easily add a custom reader to grab the extra information.

It doesn't read stored procedure SQL, but converting stored procedures is beyond the scope of this reader anyway.

 

Translating the tables to SqlServer will require some small changes. There is some simple datatype conversion to do. Check constraints aren't valid - note the name below has the table and column prefix and the column is not in the check expression.

ALTER TABLE [Order Details] ADD CONSTRAINT [[Order Details].[Discount].ValidationRule] CHECK (Between 0 And 1);
ALTER TABLE [Order Details] ADD CONSTRAINT [[Order Details].[Quantity].ValidationRule] CHECK (>0);

Fixing the checks based on the above is possible, but I don't know my way around Access check constraints so I'm not sure I'll go that far.

So for simple databases (like Northwind- just tables and constraints) I think it will be possible. Look for a checkin in a few days.

Coordinator
Sep 4, 2011 at 11:07 AM

I just checked in a version which will convert the tables and data from a standard Nthwind.mdb (and accdb version of it) to SqlServer, SqlServerCe 4.0 and SQLite.

The CopyToSqlite.exe UI (which does SqlServerCe and Sqlite) works.

It won't support conversions to Oracle, Db2, MySQL or PostgreSql. I don't think those are likely targets.

It works with Nthwind, but it may well have problems with other schemas - let me know if you have problems. It won't do queries to stored procedures, and it's certainly not a replacement for the Migration Assistant.

Sep 7, 2011 at 4:02 PM

Thank you so much for taking the time to support Access mdb files, even if not completely. This is really great! I have also done some modification work to support Access, concurrently with your efforts, and when I have a chance I'll compare our works and possibly provide some additions which you may find usefull to incorporate. One thing I noticed, is in Access, relationships seem more loosey-goosey when compared to SQL Server which makes translation problemmatic. I need to find the time to see how the upsizing wizard and Migration Assistant translates Access relationships.

Thanks again! I'll take a look at your modifications this week.