|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Using GetOleDbSchemaTable and Visual Basic .NET for MS-Access and SQL Server ExpressI 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 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. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Marcus" <holysmoke***@hotmail.com> wrote in message news:1139699982.671856.64020@g44g2000cwa.googlegroups.com... >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 > 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 Sounds good, Deck. I'll post back here with anything that might be
useful. Cheers, 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 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/ Excellent. That is ceratinly a useful tool for exploring what is schema
info is available via GetOleDbSchemaTable. Thanks! Marcus 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?  |
|||||||||||||||||||||||
Other interesting topics