Newbie - Copy table from SQL Server 2005 to Access

I need to copy a farily large table (+200,000 records) to Access. I
need to do this from a VB.NET 2005 program. I can get this to work
using .NET code. But it's too slow.
I've heard of DTS, but know nothing about it. Is this what I should
be using? Does any one have some good code that I can use?Hi Paul
"Paul" wrote:

> I need to copy a farily large table (+200,000 records) to Access. I
> need to do this from a VB.NET 2005 program. I can get this to work
> using .NET code. But it's too slow.
> I've heard of DTS, but know nothing about it. Is this what I should
> be using? Does any one have some good code that I can use?
>
In SQL 2005 SQL Server Integration Services supersedes DTS, you can find out
more about this by looking in Books online. Sites such as
http://www.sqlis.com/ are also useful. You could use the Export Wizard to
create the initial SSIS package for you invoke this by right clicking the
database node in SSMS choose Tasks, then Export Data and step through the
wizard specifying your Access database as the destination.
Another possibile solution may be to use a linked server.
John|||Paul,
Why not link the table directly to the Access database and eliminate the
copy step?
[url]http://www.sqlservercentral.com/columnists/awarren/linkingaccesstosqlserver.asp[/u
rl]
-- Bill
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174497377.108640.157780@.l77g2000hsb.googlegroups.com...
>I need to copy a farily large table (+200,000 records) to Access. I
> need to do this from a VB.NET 2005 program. I can get this to work
> using .NET code. But it's too slow.
> I've heard of DTS, but know nothing about it. Is this what I should
> be using? Does any one have some good code that I can use?
>|||> Why not link the table directly to the Access database and eliminate the
> copy step?http://www.sqlservercentral.com/col...gaccesstosql...

Thanks for the replies. AlterEgo, the application that I'm creating
is a VB.NET 2005 executable that will run at night via a scheduled
task. So running a query from Access is not possible. I thought
about running a query to do that but do not know how this is done from
within VB.NET. Plus, I didn't really want to do that.|||I successfully created an Access object within my VB.NET app and was
able to run the necessary queries to copy the data from the SQL Server
table to the Access table. It is fast and efficient. The code was
simple:
Dim accessMdb As New Access.Application
accessMdb.OpenCurrentDatabase("P:\WebSite\Databases
\WebDataDatabase.mdb", True)
accessMdb.DoCmd.OpenQuery("Delete_Web_Data_Table")
accessMdb.DoCmd.OpenQuery("Append_SQL1_WebData_to_Web_Data")
accessMdb.CloseCurrentDatabase()
Only other thing I needed was to add a reference to the Microsoft
Access 10.0 Object Library.