Table read before inserting a record using Recordset update
2 réponses
cbaskara
Hi,
Trying to insert a record using the records section option
(rs.addNew()). I think it reads the whole table. I'm using the query
"select * from table where primary key = 1" for opening the record
set. My DBA is reporting that she is seeing "select * from table"
query is issued very frequently (where as we never issue this query
from any where else).
I'm suspecting that addNew() function reads the whole table before
inserting the record, but not sure. Any ideas or suggestions?
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Trevor Best
On 14 Aug 2003 08:12:44 -0700 in comp.databases.ms-access, (baskar) wrote:
Hi,
Trying to insert a record using the records section option (rs.addNew()). I think it reads the whole table. I'm using the query "select * from table where primary key = 1" for opening the record set. My DBA is reporting that she is seeing "select * from table" query is issued very frequently (where as we never issue this query from any where else).
I'm suspecting that addNew() function reads the whole table before inserting the record, but not sure. Any ideas or suggestions?
Are you using ADO or DAO?
I don't see it in DAO or ADO.
Perhaps you have forms based on tables? Queries written in Access that are so complex that Access doesn't know how to translate to T-SQL and so pulls all records and filters on the workstation?
-- Ride Free (but you still have to pay for the petrol)
(replace sithlord with trevor for email)
On 14 Aug 2003 08:12:44 -0700 in comp.databases.ms-access,
cbaskara@yahoo.co.in (baskar) wrote:
Hi,
Trying to insert a record using the records section option
(rs.addNew()). I think it reads the whole table. I'm using the query
"select * from table where primary key = 1" for opening the record
set. My DBA is reporting that she is seeing "select * from table"
query is issued very frequently (where as we never issue this query
from any where else).
I'm suspecting that addNew() function reads the whole table before
inserting the record, but not sure. Any ideas or suggestions?
Are you using ADO or DAO?
I don't see it in DAO or ADO.
Perhaps you have forms based on tables? Queries written in Access that
are so complex that Access doesn't know how to translate to T-SQL and
so pulls all records and filters on the workstation?
--
Ride Free (but you still have to pay for the petrol)
On 14 Aug 2003 08:12:44 -0700 in comp.databases.ms-access, (baskar) wrote:
Hi,
Trying to insert a record using the records section option (rs.addNew()). I think it reads the whole table. I'm using the query "select * from table where primary key = 1" for opening the record set. My DBA is reporting that she is seeing "select * from table" query is issued very frequently (where as we never issue this query from any where else).
I'm suspecting that addNew() function reads the whole table before inserting the record, but not sure. Any ideas or suggestions?
Are you using ADO or DAO?
I don't see it in DAO or ADO.
Perhaps you have forms based on tables? Queries written in Access that are so complex that Access doesn't know how to translate to T-SQL and so pulls all records and filters on the workstation?
-- Ride Free (but you still have to pay for the petrol)
(replace sithlord with trevor for email)
Matthew Reeves
"baskar" wrote in message news:
Hi,
Trying to insert a record using the records section option (rs.addNew()). I think it reads the whole table. I'm using the query "select * from table where primary key = 1" for opening the record set. My DBA is reporting that she is seeing "select * from table" query is issued very frequently (where as we never issue this query from any where else).
I'm suspecting that addNew() function reads the whole table before inserting the record, but not sure. Any ideas or suggestions?
Thanks !
Regards, Baskar
You are correct. Upon opening the recordset the first time, and using the AddNew function, the entire recordset is enumerated before returning control to the code.
However, here's a cute quirk. If all you need to do with this particular recordset is to append new records, then you can open the recordset with this SQL: "SELECT TOP 1 * FROM TABLE;"
...which will only spend time enumerating one record from the source table. Then you can AddNew to your heart's content, without the overhead delay of Access enumerating the entire table. This is especially advantageous on tables with thousands of records.
"baskar" <cbaskara@yahoo.co.in> wrote in message
news:b5a71bcf.0308140712.2c70d16e@posting.google.com...
Hi,
Trying to insert a record using the records section option
(rs.addNew()). I think it reads the whole table. I'm using the query
"select * from table where primary key = 1" for opening the record
set. My DBA is reporting that she is seeing "select * from table"
query is issued very frequently (where as we never issue this query
from any where else).
I'm suspecting that addNew() function reads the whole table before
inserting the record, but not sure. Any ideas or suggestions?
Thanks !
Regards,
Baskar
You are correct. Upon opening the recordset the first time, and using the
AddNew function, the entire recordset is enumerated before returning control
to the code.
However, here's a cute quirk. If all you need to do with this particular
recordset is to append new records, then you can open the recordset with
this SQL:
"SELECT TOP 1 * FROM TABLE;"
...which will only spend time enumerating one record from the source table.
Then you can AddNew to your heart's content, without the overhead delay of
Access enumerating the entire table. This is especially advantageous on
tables with thousands of records.
Trying to insert a record using the records section option (rs.addNew()). I think it reads the whole table. I'm using the query "select * from table where primary key = 1" for opening the record set. My DBA is reporting that she is seeing "select * from table" query is issued very frequently (where as we never issue this query from any where else).
I'm suspecting that addNew() function reads the whole table before inserting the record, but not sure. Any ideas or suggestions?
Thanks !
Regards, Baskar
You are correct. Upon opening the recordset the first time, and using the AddNew function, the entire recordset is enumerated before returning control to the code.
However, here's a cute quirk. If all you need to do with this particular recordset is to append new records, then you can open the recordset with this SQL: "SELECT TOP 1 * FROM TABLE;"
...which will only spend time enumerating one record from the source table. Then you can AddNew to your heart's content, without the overhead delay of Access enumerating the entire table. This is especially advantageous on tables with thousands of records.
