🚀 go-pugleaf

you can export data (tables) from access to any odbc data source with
just a menu call and a few dialog boxes?

On Fri, 27 Feb 1998 14:39:04 +0100, "J�rgen"
<senkelw@uni-duesseldorf.de> wrote:

That's  true, but we don't to do this by menu calls. We'd like to do it by a
Visual Basic function.

directyodbc:
create connection (take a look for create connection help )
create querydef, which will append whole table
Experience showing that the most effecient transaction size is about 10,000
records.
Then open querydef as recordset and cycling by 10,000 records , using start
tarnsaction,committransaction methods add whole table
Anyway, it will be more efficient then using BCP and even fatser then using
direct store procedure.
 

J�rgen <senkelw@uni-duesseldorf.de> wrote in article
<6d6fnh$cu5@selena.rz.uni-duesseldorf.de>...
> Hello
> following szenario:
> SQL-Server 6.5 and Access 97
> We have to copy a whole, in Access existing table to the SQL-Server. We
> managed to do this by a stored procedure which must be called for every
row
> of the table. The problem is that this lasts to long.
> Now our question:
> How to copy a whole table by only one call to a  stored procedure. We
don�t
> want to use the BCP Programm.
> Thanks in advance
> Willi & Klaus
> 
> 
> 
> 

The upsizing tools uses DoCmd.TransferDatabase. How about that?

Michael

Wilfried <senkelw@uni-duesseldorf.de> wrote in message
news:6d6onu$3qh@selena.rz.uni-duesseldorf.de...
>That's  true, but we don't to do this by menu calls. We'd like to do it by
a
>Visual Basic function.
>
>
>
>

Use the upsizing tools.  I'm not sure if it comes with SQLServer it might be
a seperate package.

theKEG

J�rgen wrote in message <6d6fnh$cu5@selena.rz.uni-duesseldorf.de>...
>Hello
>following szenario:
>SQL-Server 6.5 and Access 97
>We have to copy a whole, in Access existing table to the SQL-Server. We
>managed to do this by a stored procedure which must be called for every row
>of the table. The problem is that this lasts to long.
>Now our question:
>How to copy a whole table by only one call to a  stored procedure. We don�t
>want to use the BCP Programm.
>Thanks in advance
>Willi & Klaus
>
>
>

Why not just link the SQL table to Access and then use an update query to
move the data from one table to the other - the entire update will be
treated as one transaction.  WARNING - depending on the size of temp DB and
the number of records you are moving, you may want to do this in several
smaller transactions - just place a criteria on the Access table when doing
the update.  I have moved millions of rows using this method and it works
quite well - not as fast as fast BCP but very flexible and easy to run. 

Harley Arnett 

theKEG <keg@ozemail.com.au> wrote in article
<6dmoff$iln$1@news.wantree.com.au>...
> Use the upsizing tools.  I'm not sure if it comes with SQLServer it might
be
> a seperate package.
> 
> theKEG
> 
> J�rgen wrote in message <6d6fnh$cu5@selena.rz.uni-duesseldorf.de>...
> >Hello
> >following szenario:
> >SQL-Server 6.5 and Access 97
> >We have to copy a whole, in Access existing table to the SQL-Server. We
> >managed to do this by a stored procedure which must be called for every
row
> >of the table. The problem is that this lasts to long.
> >Now our question:
> >How to copy a whole table by only one call to a  stored procedure. We
don�t
> >want to use the BCP Programm.
> >Thanks in advance
> >Willi & Klaus
> >
> >
> >
> 
> 
> 

What do you exactly do with your stored procedure?
Else, to copy an access table into sqlsrvr table, you can attach your
sqlsrvr table in access and then use it like an access table.

Else, with your method, you can use begin tran (at the begining of the copy,
send this statement with a pass-through query) and commit tran (at the end)
to speed up the copy.

Arnaud