Using GetOleDbSchemaTable and Visual Basic .NET for MS-Access and SQL Server Express

[Marcus]

I need to write a VB.Net application that will take an mdb file (Access
2000) and create corresponding copy of it in SQL Express, creating all
the tables and relationships, and transferring over the data (using an
existing application like DTS in SQL Server or something else is NOT an
option - this must be coded from scratch). The Access databases that it
must be able to handle will only have tables, relationships, and data
(i.e. no forms, macros, queries, reports). I see that in the
System.Data.OleDb namespace there is the method GetOleDbSchemaTable
that returns all sorts of schema information. I haven't fully explored
how to use this method, but do you think I will be able to pull out all
that I need from the various schema information returned to rebuilt the
mdb file in SQL Express? I just don't want to start heading down this
road and invest quite a bit of effort only to hit an impassable road
block.

Thank you for any thoughts on this!

Marcus

 

[Dan Guzman]

I don't know of any gotchas with using GetOleDbSchemaTable to roll your own
upsizing tool. However, you'll need to come up with your own Access to SQL
Server data type mappings.

 

[Deck]

I am in the middle of the same dilema now . Actually I will just
start my own thorough tests with GetOleDbSchemaTable() to see the
results. Until now I read good references about this method and my only
test until now (retrieving the list of tables) worked well on SQL
Server and ORACLE.
I propose you to keep each other in touch in order to share the test
results, ok?

Deck

 

[tommaso.gastaldi]

Here is a pointer to a (free) application which can be of help in this
preliminary stage.

It will download and show the entire DB structure (tables, links,
passthroughs, relationships,...), by querying through
GetOleDbSchemaTable.

It will display contents of all the OleDbSchemaGuids.

It has functionalities to reload data from Access to SQL server.

It shows data type mappings mapping and allows changing it at reload
time.

It's possible to ask (free) for new functionalities to solve your
problems.

http://151.100.3.84/technicalpreview/

 

[Marcus]

Sounds good, Deck. I'll post back here with anything that might be
useful.

Cheers,
Marcus

 

[Marcus]

Excellent. That is ceratinly a useful tool for exploring what is schema
info is available via GetOleDbSchemaTable.

Thanks!
Marcus

 

[Lyle Fairfield]

If Ms-Acess is installed then one could automate it, create an ADP,
attach the ADP to the SQL-Server and create the new SQL DB with
DoCmd.TransferDatabase. I guess the whole thing would be fewer than ten
lines of simple code.

If Ms-Access is not part of the solution then ... why CDMA?

 

[Marcus]

Here is a project that someone put together in C# that reads the
schema from an Access database using GetOleDbSchemaTable() and creates
the SQL to regenerate it. I think this will be very useful for my
needs:

http://www.codeproject.com/csharp/sqljet.asp?df=100&forumid=16681&exp=0&select=599515

Marcus