Re: What's the best way to open a recordset for insert only?

I should have added that you could close that connection while the
recordset was disconnected, and then reopen it just before reconnecting
it.

Spencer wrote:
Just in case you were wondering, the application is now inserting
3000-4000 records in the test environment per tick of the progress
bar/call to BatchUpdate. Setting rs.ActiveConnection to Nothing
between batches doesn't seem to improve that significantly.

"Bob Barrows [MVP]" wrote:

Spencer wrote:

So it was a failed experiment, and it looks like I'll have to use
the recordset. But I'm wondering if there's a way to open the
recordset so that I can call AddNew on it, but doesn't query the
entire table first.

Use a disconnected recordset opened using a sql query that includes
"WHERE 1=2"

rs.CursorLocation = adUseClient
m_rsname = "select <fields> from " & m_rsname & " WHERE 1=2"
rs.Open m_rsname, m_conn, , adLockBatchOptimistic, adCmdText
Set rs.ActiveConnection = Nothing
'add your records, then
Set rs.ActiveConnection = m_conn
rs.UpdateBatch

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages

  • RE: In VBA how do I link the fields of a form to an on-the-fly SQL
    ... Now I create a SQL query for just those stars in Orion. ... Dim db as Database, rst as Recordset, strSQL as String ... linked to the entire table-- all the stars in all the constellations. ...
    (microsoft.public.access.queries)
  • Re: Query SQL database
    ... Is this an SQL query in Enterprise ... Manager or Query Analyzer, in VB or VBScript, linked servers? ... Recordset object, for example, to return a recordset that meets the ... This is an SQL server database on server ...
    (microsoft.public.windows.server.scripting)
  • Re: Recordset query?
    ... Thanks Adrian ... >> be able to excute an SQL query on that recordset. ... >> Dim oRstDest as ADODB.Recordset ...
    (microsoft.public.vb.database.ado)
  • Re: Query over disconnected recordsets
    ... You cannot use any SQL query, but you could use xPath query against XML. ... > I'm programming an standard OCX which allow me to filter a recordset. ...
    (microsoft.public.vb.database.ado)
  • Re: Whats the best way to open a recordset for insert only?
    ... OK, this helps a lot, although I suppose we'll have to trust SQL Server not ... But I'm wondering if there's a way to open the recordset ... Use a disconnected recordset opened using a sql query that includes ... Please reply to the newsgroup. ...
    (microsoft.public.data.ado)