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

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)
-----------------------------------------------------------------------------------------------------------------------

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

On 23 Okt., 19:27, "William Vaughn" <billvaNoS...@betav.com> wrote:
> 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)
> -----------------------------------------------------------------------------------------------------------------------
>
> <schnei...@rehm.de> wrote in message
>
> news:(E-Mail Removed)...
>
> > 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

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.

On Wed, 24 Oct 2007 02:16:37 -0700, (E-Mail 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)

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)
-----------------------------------------------------------------------------------------------------------------------

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On 23 Okt., 19:27, "William Vaughn" <billvaNoS...@betav.com> wrote:
>> 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)
>> -----------------------------------------------------------------------------------------------------------------------
>>
>> <schnei...@rehm.de> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>> > 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
>
> 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.
>

On 24 Okt., 15:26, Paul Clement
<UseAdddressAtEndofMess...@swspectrum.com> wrote:
> On Wed, 24 Oct 2007 02:16:37 -0700, schnei...@rehm.de 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

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)
-----------------------------------------------------------------------------------------------------------------------

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
On 24 Okt., 15:26, Paul Clement
<UseAdddressAtEndofMess...@swspectrum.com> wrote:
> On Wed, 24 Oct 2007 02:16:37 -0700, schnei...@rehm.de 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

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

On 25 Okt., 17:51, "William Vaughn" <billvaNoS...@betav.com> wrote:
> 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)
> -----------------------------------------------------------------------------------------------------------------------
>
> <schnei...@rehm.de> wrote in message
>
> news:(E-Mail Removed)...
> On 24 Okt., 15:26, Paul Clement
>
>
>
> <UseAdddressAtEndofMess...@swspectrum.com> wrote:
> > On Wed, 24 Oct 2007 02:16:37 -0700, schnei...@rehm.de 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