Re: export access to sql server
From: Bala (Bala_at_discussions.microsoft.com)
Date: 07/15/04
- Next message: Bala: "Re: export access to sql server"
- Previous message: Veign: "Re: Exporting Access database to Excel"
- In reply to: Andrew Faust: "Re: export access to sql server"
- Next in thread: Bala: "Re: export access to sql server"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 15 Jul 2004 10:12:04 -0700
Hi Andrew,
Thanks for the valueable information.
i tried in different way. like i created the DTS Package and saved as vb module. and i try to execute. its wont do anything.
i refered these dlls
Microsoft DTSDataPump Scripting Object Library
Microsoft DTSPackage Object Library
Microsoft DTS Custom Tasks Object Library
i followed article from sql server books MSDN.
Running a DTS Package Saved as a Visual Basic File
any idea? how to execute the dts package from vb?
Thanks
Bala
"Andrew Faust" wrote:
>
> "Bala" <Bala@discussions.microsoft.com> wrote in message
> news:11681353-BAD0-428B-BDCE-38C9520ABE68@microsoft.com...
> > Hi Andrew,
> >
> > Thanks for the reply. Actually my software will support access
> and sql server. suppose initially the client using access, after
> some time he try to upgrade access to sql server, at that time i
> need to export all the access db values to sql server from my
> application (vb).
> >
> > is it possible?
>
> Sure. The way I would go about copying the data is to first
> create the table structures in SQL Server. Assuming that the
> database structure is constant you can just hard code the Create
> Table queries. If the app needs to dynamically recreate the
> structure then you will need to query the actual table structure.
> I'm not sure how to do this in Access, but in MySQL for example
> you use the desc command. Then just execute the queries to create
> the tables.
>
> The next step is to read in the data from the access database in
> to some recordsets, then just loop through the recordsets. For
> each record generate an insert query, and insert the record. The
> actual queries you'll need to perform are pretty straightforward,
> and if you have basic knowledge of SQL it shouldn't be too tough.
>
> A couple of things you'll want to keep in mind if you are making
> an app that supports mutliple DBs.
>
> First) Abstract ALL data access to seperate classes that expose
> the exact same inteface. This way all you need do is switch
> between which class is being used, and the front end of the app
> doesn't change at all. If you try mixing the interface with the
> data access code then you will have a maintenance nightmare.
>
> Second) Don't require the app to depend on features of one DB
> that another DB doesn't support. For example, don't make the app
> require transaction support, as if they are using access for the
> DB it won't work. You can still support transactions, it just
> needs to be done in such a way so that the app doesn't try to use
> transactions when it is talking to access.
>
> Third) Rather than supporting Access to start with you may want
> to consider either MySQL or Postgres instead. They are both
> excellent databases which cost next to nothing. MySQL is free for
> Non-Commercial use, and runs on Windows and *nix. Postgres is
> entirely free, however it only runs on *nix. This gives you a low
> cost alternative to SQLServer with far more power than Access. In
> fact I know of companies who have switched from Oracle to
> Postgres because it's far cheaper, and has all the capabilities
> they needed.
>
> Good Luck,
>
> Andrew Faust
>
>
>
- Next message: Bala: "Re: export access to sql server"
- Previous message: Veign: "Re: Exporting Access database to Excel"
- In reply to: Andrew Faust: "Re: export access to sql server"
- Next in thread: Bala: "Re: export access to sql server"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
