Append from Access To Linked Table SLow

[nospam]

Hello,

I am using Access 2000 and SQLServer 2000.

I have 2 tables - one in Access and one in SS. They are identical in
structure.
I want to copy the Access or local table ( table_local)
to the SS table ( table_SS).

strSql = "INSERT into table_SS Select * from table_Local"
dbsCurr.Execute strSql, dbFailOnError

This takes a very, very long time.
Is there another mechanism to use here?
Is there a straight forward way to just append?
Is BULK Insert available here?

Thanks in advance.

Regards,
bruce

[John Winterbottom]

DTS, (Data Transformation Services) will probably be your fastest
option. You can use SQL Server Enterprise Manager to create and save a
DTS script and then execute this from VB. The problem with this method
is that you need the SQL Server DMO object libraries. Another option is
to export the data to text and then use BCP or BULK INSERT to load the
SQL Server table.

An alternative but slower method is to open an ADO recordset on the SQL
Server table, and a DAO recordset on the Jet (Access) table, then loop
through the latter and populate the former. You should find all the code
to do this if you search the newsgroups.

nospam wrote:

Hello,

I am using Access 2000 and SQLServer 2000.

I have 2 tables - one in Access and one in SS. They are identical in
structure.
I want to copy the Access or local table ( table_local)
to the SS table ( table_SS).

strSql = "INSERT into table_SS Select * from table_Local"
dbsCurr.Execute strSql, dbFailOnError

This takes a very, very long time.
Is there another mechanism to use here?
Is there a straight forward way to just append?
Is BULK Insert available here?

Thanks in advance.

Regards,
bruce

[nospam]

Thanks John.

The DTS solution worked. That's one I didn't think of ....

Regards,
bruce

"John Winterbottom" <jo*****@rogers.cawrote in message
news:5i*************@mid.individual.net...

DTS, (Data Transformation Services) will probably be your fastest option.
You can use SQL Server Enterprise Manager to create and save a DTS script
and then execute this from VB. The problem with this method is that you
need the SQL Server DMO object libraries. Another option is to export the
data to text and then use BCP or BULK INSERT to load the SQL Server table.

An alternative but slower method is to open an ADO recordset on the SQL
Server table, and a DAO recordset on the Jet (Access) table, then loop
through the latter and populate the former. You should find all the code
to do this if you search the newsgroups.

nospam wrote:

>Hello,

I am using Access 2000 and SQLServer 2000.

I have 2 tables - one in Access and one in SS. They are identical in
structure.
I want to copy the Access or local table ( table_local)
to the SS table ( table_SS).

strSql = "INSERT into table_SS Select * from table_Local"
dbsCurr.Execute strSql, dbFailOnError

This takes a very, very long time.
Is there another mechanism to use here?
Is there a straight forward way to just append?
Is BULK Insert available here?

Thanks in advance.

Regards,
bruce