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

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

.

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: SQL statement error
    ... Yet you missed answering the question we asked. ... >Dim rsCodevault 'Holds the recordset for the records in the database ... Response.Write strSQL ... Which is because the SQL Query has a syntax error, ...
    (microsoft.public.inetserver.asp.db)