Recordset problem 1

[gnt]

Hello-
I'm trying this thread again, because I haven't had much luck with it...
I'm trying to pull data from a database on our server and import it into a local table in Access. The only way I've found to do it is to create a recordset with the data and run an INSERT SQL statement cycling through all the records. I need to import about 50,000 records from 2 different db's. Needless to say, this method takes a bit long (it has to run 50,000 INSERT statements). Is there a more efficient way to do this?
A couple things:
-Using Access 2000
-I can't use a Pass through query
-I can't use linked tables

Thanks for any help!

 

[xlbo]

Does it not have the same fields as the table you are inserting into ??
If it does, just use an APPEND query (although by the sounds of it you know enough about Access to've already considered that)

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[gnt]

Geoff-
Yeah, that's what I've been trying. Unless you know of another way to append records besides the INSERT statement. In theory, what I want to do is this:

INSERT INTO tblMaster (shipno, shpnam, sinout)
SELECT (shipno, shpnam, sinout)
FROM [My recordset]

But as far as I know (which is not much when it comes to ADO), I can't query a recordset. Correct me if I'm wrong. So I'm stuck with:

While Not objRS.EOF
  strShipno = objRS("shipno")
  strShpnam = Replace(Trim(objRS("shpnam")), "'", "")
  strSinout = objRS("sinout")
  strSQL = "INSERT INTO tblMaster VALUES ('" & 
  strShipno & "', '" & strShpnam & "', '" & strSinout & "'"
  DoCmd.RunSQL strSQL
  objRS.MoveNext
Wend
[\code]
But with 50,000 records, this drags on for quite awhile.
Thanks-

 

[xlbo]

My apologies - I'm not much good on SQL (but I know a bit about Access / databases) - didn't realise that INSERT was the same as APPEND
In the back of my mind, I seem to recall that there's an ADD statement in ADO - that may be quicker

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[gnt]

Thanks-
I'll look around for that.

 

[gnt]

Yahoo!
Thanks so much for pointing me in that direction. You don't know how many weeks I've been working on this! My INSERT query took over 5 minutes to run, but with the objRS.AddNew command - 50,000 records in 15 seconds!
Thanks also to koukouroukou in thread705-208622 for the syntax.