Re: Copy database schema from Access to SQL Server by schneider
schneider
Mon Oct 29 00:51:05 PDT 2007
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 right=
s=2E
> __________________________________
> 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:1193292855.440585.215650@q5g2000prf.googlegroups.com...
> On 24 Okt., 15:26, Paul Clement
>
>
>
> <UseAdddressAtEndofMess...@swspectrum.com> wrote:
> > On Wed, 24 Oct 2007 02:16:37 -0700, schnei...@rehm.de wrote:
>
> > =A4
> > =A4 If you're talking about the Wizard in MS Access to convert single
> > =A4 projects to SQL Server, this won't work for me. I need to
> > =A4 programatically copy the database schema. I think the key issue is =
"Do
> > =A4 I have to dynamically create DDL SQL statements for each element I
> > =A4 want to copy to SQL Server or is there a simpler solution?" Using C#
> > =A4 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