 | |
ADODB, access2007, open recordset error
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: ADODB, access2007, open recordset error -
08-25-2011
, 10:56 AM
|
On Aug 25, 7:43 am, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote: Bob Barrows wrote: sSQL="SELECT handoverId,lineNbr " & _ "FROM tblSlsDetail WHERE 1 = 2" Of course, you should substitute the actual name of the table in sql server rather than using the name of the linked table (if different). in this case the original error message is misleading and incorrect I debug.printed all the fields in the recordset and tried to create the record manually turns out one of the actual fields that I was updating has a foreign key to a product table, and it was missing a record the manual entry gave me the correct error message, and I fixed the data |
your recordset inside the loop - highly inefficient.
Open the recordset once: outside the loop. Then close it after the loop
finishes.
#12
| |||
| |||
|
|
as to an insert statement, it's a idea, but the example I've provided highly simplifies the fields that are updated - actually there are many fields and the contents is sometimes based on conditions - but I'll look into an INSERT |
and one for the values but a field or two at a time. I think I have
one routine which is around a thousand lines long whose only job is to
do an update/insert from the very complex form to the transactions
table.
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
#13
| |||
| |||
|
|
I would expect he wants to open an empty recordset with the intention of doing nothing but add new records. Of course, executing a sql INSERT statement directly would be a better plan. |
while he's doing the inserts...
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
#14
| |||
| |||
|
|
the ADO version worked fine until yesterday we are in the process of updating from office2007 to office2010, except for access which will stay at 2007 when we did the update on the development server, this MDB failed with an ADO issue looks like the msado15.dll was updated by the office2010 update to a version incompatible with access2007 |
you could be having ADO problems.
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
#15
| |||
| |||
|
|
On Aug 25, 7:43*am, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote: Bob Barrows wrote: sSQL="SELECT handoverId,lineNbr *" & _ "FROM tblSlsDetail WHERE 1 = 2" Of course, you should substitute the actual name of the table in sql server rather than using the name of the linked table (if different). in this case the original error message is misleading and incorrect I debug.printed all the fields in the recordset and tried to create the record manually turns out one of the actual fields that I was updating has a foreign key to a product table, and it was missing a record the manual entry gave me the correct error message, and I fixed the data |
office2003 and you upgrade the rest of ms-office to 2010
this upgrade installs a newer version of ado15.dll causing ado not to
work with access2007
the fix is to open access2007, under options, select resources and run
'microsoft office diagnostics'
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.