Board index » delphi » Copy SQL Server Table to Access Table

Copy SQL Server Table to Access Table

What would be the best way to copy a SQL Server table to an Access table
from a delphi program.  I tried querying the SQL Server Table and inserting
the records row-by-row in to the Access table but this is too slow.  This
seems like it should be so easy!  Any help would be grealy appreciated.

Thanks,
Jeff

 

Re:Copy SQL Server Table to Access Table


Dear Jeff,

To do a bulk insert in the manner that you require means not using the
standard interface, be it ODBC, BDE, ADO or whatever.  These are all SQL
interfaces and as such only offer the one row at a time INSERT statement.

Two other approaches are possible.

First to use an alternative interface.  SQLServers Data Transformation
Services is an example and is great for the bulk loading of SQL Server
tables.  Access being a Microsoft product, you might be lucky and find that
DTS will do a bulk load into Access, but I suspect that it would use ODBC.
You probably need to use Access itself.  If you must control from Delphi,
you could create an Access DLL or COM Server.

Secondly read up on the Access file format and create a file yourself from
scratch - a lot of work!

If you find a better solution please post it, as many of us would find it
useful.

Have fun
Simon

Quote
"Jeff Burgoon" <jburg...@sakon.com> wrote in message

news:3c7d3a0d$1_1@dnews...
Quote
> What would be the best way to copy a SQL Server table to an Access table
> from a delphi program.  I tried querying the SQL Server Table and
inserting
> the records row-by-row in to the Access table but this is too slow.  This
> seems like it should be so easy!  Any help would be grealy appreciated.

> Thanks,
> Jeff

Re:Copy SQL Server Table to Access Table


Why not try adding the SQL Server Table as a linked table in access and then
just do a select into? DTS is probably the next best option it will use OLE
DB to insert into Access which is pretty quick but there is no fastload into
Access through OLE DB.

-Euan

Quote
"Jeff Burgoon" <jburg...@sakon.com> wrote in message

news:3c7d3a0d$1_1@dnews...
Quote
> What would be the best way to copy a SQL Server table to an Access table
> from a delphi program.  I tried querying the SQL Server Table and
inserting
> the records row-by-row in to the Access table but this is too slow.  This
> seems like it should be so easy!  Any help would be grealy appreciated.

> Thanks,
> Jeff

Re:Copy SQL Server Table to Access Table


Thanks guys for your responses.  I didn't want to use DTS or Linked servers
because my application uses many access databases and this process must
occur with each new access database.  What I did was create a DSN to my
database on the same machine as the program and then used a query such as:

INSERT NewTable SELECT * FROM OldTable IN "" "DSN=MyDSN..."

This works just fine for me.

Jeff

Other Threads