• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5306
  • Last Modified:

Insert ADO recordset to table

Hi,

I have a VBA that queries a database and gets a set of data into a recordset.  I need a way to copy this data into the table.   The recordset and the table both have a same structure (1 column) and the recordset is usually large (100,000 rows) and the table is cleared before it is filed with the data.

Any way I can do an insert that copies whole recordset into table?

I've seen lots of examples where we go through each recordset and do an insert, but due to the size, i can't imagine this method being efficient.
0
iamnamja
Asked:
iamnamja
  • 6
  • 3
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Any way I can do an insert that copies whole recordset into table? >>

  In short, no.    But what you can do is execute an INSERT with a SELECT based on the one you just used to get the record set.

Jim.
0
 
iamnamjaAuthor Commented:
Hi Jim,

Could you give me an example of how this would work?


Here's my current code inserting line by line..

all_SQL = "SELECT distinct i.sid " & _
            "FROM common..idlist i"


Set all_RS = query_DB(globconn, all_SQL)

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM Latest_TIDs"
DoCmd.SetWarnings True

If all_RS.State <> 0 Then
    If Not (all_RS.EOF And all_RS.BOF) Then
        all_RS.MoveFirst
        DoCmd.SetWarnings False
        Do While Not all_RS.EOF
            DoCmd.RunSQL "INSERT INTO Latest_SIDs (sid) VALUES(""" & all_RS!sid.Value & """)"
            all_RS.MoveNext
        Loop
        DoCmd.SetWarnings True
    End If
End If

Open in new window

0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
all_SQL = "INSERT INTO Latest_SIDs ( SID ) SELECT distinct i.sid FROM common..idlist i"

CurrentDB.Execute all_SQL, dbFailOnError

This assumes the tables are available as linked tables as with CurrentDB().Execute or DoCmd.RunSQL, you are not using ADO to execute the query (linked tables use ODBC).

Jim.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
iamnamjaAuthor Commented:
Hi Jim,

Unfortunately the tables are NOT linked - actually can't be linked, so i would need to query it the way I did above.  Does that mean the only method is the way that I have currently written?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Unfortunately the tables are NOT linked ->>

 I don't understand how your RunSQL works then.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Does that mean the only method is the way that I have currently written? >>

 missed this...no, you can execute the SQL via ADO:

<connection object>.Execute all_SQL

Jim.
0
 
iamnamjaAuthor Commented:
So just so that we're on the same page... The all_rs query is run on a sybase database and the recordset data fetched is what i need to store into my table called Latest_SIDs.

How do i run a query that inserts a data from a sybase database into access?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
ah, so Latest_SIDs  is local...now I understand the problem.

In that case, you will need to use the IN clause on the select statement to specify the external database.   With that, you must call an ODBC driver.   Basically, your doing a linked table on the fly.  Will look something like this:

all_SQL = "INSERT INTO Latest_SIDs ( SID ) SELECT distinct i.sid FROM common..idlist IN '' [ODBC; Driver={MySQL ODBC 5.2 ANSI Driver};Server=localhost;Database=myDataBase;
User=myUsername;Password=myPassword;Option=3;"

CurrentDB.Execute all_SQL, dbFailOnError

Your connection info will vary of course, but that's the jist of it.   You probably can do this with ADO as well, but off-hand I don't know how.

Jim.
0
 
PatHartmanCommented:
I don't understand why you can't link the table.  The link doesn't need to be permanent.  You can use TransferDatabase to link and then delete the link when you are done.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
A linked table is faster performance wise too...

Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now