Copy database schema from Access to SQL Server

[schneider]

Hi all,

I'm trying to write an application that takes an Access database as
template to customize a new or existing SQL Server database. When
something in the template database is changed, I want to apply these
changes to the SQL database. For example if new columns are added to a
table or constraints are changed, the application parses each Access
table and relation and whatever and applies this to the SQL Server
database. The data in the tables is irrelevant, only the schema is
important. I have to apply default values, check contraints (I think
it's validation rules in Access), maximum column lengths, ...
How can I accomplish this? Do I have to loop through the Access tables
and relations and create a DDL SQL statement for each? What if the
column/contraint already existed? Seems complicated, maybe there is an
easier solution.
I'm using Visual Studio 2005 and C# 2.0, so this should be the way to
go.
Thanks in advance.

Regards,
Hannes

 

[William Vaughn]

Check out the Access Upsizing wizard. It can pull over the data and schema
(but not the indexes).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[schneider]

Check out the Access Upsizing wizard. It can pull over the data and schema
(but not the indexes).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speakerwww.betav.comwww.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visitwww.hitchhikerguides.netto get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

If you're talking about the Wizard in MS Access to convert single
projects to SQL Server, this won't work for me. I need to
programatically copy the database schema. I think the key issue is "Do
I have to dynamically create DDL SQL statements for each element I
want to copy to SQL Server or is there a simpler solution?" Using C#
is mandatory.

 

[Paul Clement]

On Wed, 24 Oct 2007 02:16:37 -0700, (e-mail address removed) wrote:

¤
¤ If you're talking about the Wizard in MS Access to convert single
¤ projects to SQL Server, this won't work for me. I need to
¤ programatically copy the database schema. I think the key issue is "Do
¤ I have to dynamically create DDL SQL statements for each element I
¤ want to copy to SQL Server or is there a simpler solution?" Using C#
¤ is mandatory.

The easy method is to use the wizard. If you want to use code then you will have to read the Access
schema and then use DDL code to create the corresponding objects in SQL Server.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[William Vaughn]

I would use the OleDbConnection.GetSchema method to pull down the metadata.
I would show you a working example, but you don't understand VB.NET.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[schneider]

On Wed, 24 Oct 2007 02:16:37 -0700, (e-mail address removed) wrote:

¤
¤ If you're talking about the Wizard in MS Access to convert single
¤ projects to SQL Server, this won't work for me. I need to
¤ programatically copy the database schema. I think the key issue is "Do
¤ I have to dynamically create DDL SQL statements for each element I
¤ want to copy to SQL Server or is there a simpler solution?" Using C#
¤ is mandatory.

The easy method is to use the wizard. If you want to use code then you will have to read the Access
schema and then use DDL code to create the corresponding objects in SQL Server.

Paul
~~~~
Microsoft MVP (Visual Basic)

Thanks for your response. It seems like I have to parse the complete
template database and the target database and compare their schemata.
For differing elements, I will have to create a DDL statement as you
suggested.
My last resort was a solution like it was possible with dao, as you
could parse through the tabledef, relations, ... collections and apply
the changes directly. However, as OleDb and SqlClient seem to differ
very much in the way they treat constraints and other concepts,
sticking back to basic DDL code seems the best solution. Furthermore,
maybe sometimes Oracle or another database solution comes into play
and using DDL code I will have less trouble integrating it into my
app.
Anyway, thanks for your help Bill and Paul. You pointed me in the
right direction.

Regards,
Hannes

 

[William Vaughn]

I still don't know why you're reinventing the wheel. I would leverage the
considerable work done by Microsoft when it created SSIS. It can import a
JET database in a single stroke. It's programmable, can run on a schedule
and is very fast.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

On Wed, 24 Oct 2007 02:16:37 -0700, (e-mail address removed) wrote:

¤
¤ If you're talking about the Wizard in MS Access to convert single
¤ projects to SQL Server, this won't work for me. I need to
¤ programatically copy the database schema. I think the key issue is "Do
¤ I have to dynamically create DDL SQL statements for each element I
¤ want to copy to SQL Server or is there a simpler solution?" Using C#
¤ is mandatory.

The easy method is to use the wizard. If you want to use code then you
will have to read the Access
schema and then use DDL code to create the corresponding objects in SQL
Server.

Paul
~~~~
Microsoft MVP (Visual Basic)

Thanks for your response. It seems like I have to parse the complete
template database and the target database and compare their schemata.
For differing elements, I will have to create a DDL statement as you
suggested.
My last resort was a solution like it was possible with dao, as you
could parse through the tabledef, relations, ... collections and apply
the changes directly. However, as OleDb and SqlClient seem to differ
very much in the way they treat constraints and other concepts,
sticking back to basic DDL code seems the best solution. Furthermore,
maybe sometimes Oracle or another database solution comes into play
and using DDL code I will have less trouble integrating it into my
app.
Anyway, thanks for your help Bill and Paul. You pointed me in the
right direction.

Regards,
Hannes

 

[schneider]

Hi Bill,

the problem is (excuse me for not having made this clear earlier) that
the customers are not required to have MS Access or any database
application installed on their machines. They typically don't know
nothing about databases, they just want to use them. It is mandatory
that when opening a project, their database schema has to be checked
for up-to-dateness and changes have to be made if applicable without
requiring the customers to do anything else. In addition, certain
tasks may have to be performed where rows are copied, created or
deleted casually and where I need a customized tool anyway. We don't
use high-end database features neither in Access nor in SQL Server,
that's why we just use basic database concepts (tables, pkeys, fkeys,
constraints) that are available to all relational database engines.
Last not least, we are planning Oracle (and maybe other dbms) support,
too. That's why I don't want to use or rely on a SQL Server-specific
tool to do the tasks.

Regards,
Hannes

I still don't know why you're reinventing the wheel. I would leverage the
considerable work done by Microsoft when it created SSIS. It can import a
JET database in a single stroke. It's programmable, can run on a schedule
and is very fast.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speakerwww.betav.comwww.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visitwww.hitchhikerguides.netto get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

¤
¤ If you're talking about the Wizard in MS Access to convert single
¤ projects to SQL Server, this won't work for me. I need to
¤ programatically copy the database schema. I think the key issue is "Do
¤ I have to dynamically create DDL SQL statements for each element I
¤ want to copy to SQL Server or is there a simpler solution?" Using C#
¤ is mandatory.

The easy method is to use the wizard. If you want to use code then you
will have to read the Access
schema and then use DDL code to create the corresponding objects in SQL
Server.

Paul
~~~~
Microsoft MVP (Visual Basic)

Thanks for your response. It seems like I have to parse the complete
template database and the target database and compare their schemata.
For differing elements, I will have to create a DDL statement as you
suggested.
My last resort was a solution like it was possible with dao, as you
could parse through the tabledef, relations, ... collections and apply
the changes directly. However, as OleDb and SqlClient seem to differ
very much in the way they treat constraints and other concepts,
sticking back to basic DDL code seems the best solution. Furthermore,
maybe sometimes Oracle or another database solution comes into play
and using DDL code I will have less trouble integrating it into my
app.
Anyway, thanks for your help Bill and Paul. You pointed me in the
right direction.

Regards,
Hannes[/QUOTE]