strSQL = "DELETE tblOfferDetails.* " & _
"FROM tblOfferDetails " & _
"WHERE OfferID=" & Me.OfferID & ";"
db.Execute strSQL, dbFailOnError
I get this error message (as usual):
"Run-time error '3622':
You must use the dbSeeChanges option with OpenRecordset when accessing
a SQL Server table that has an IDENTITY column."
So what I did is change the parameters of the "db.Execute" command so
that it now reads:
db.Execute strSQL, dbFailOnError + dbSeeChanges
The job is done no more error messages and the job is rightly done!
Well, is seems so!
Do you think this is alright? Should I still change something?
I'm afraid I don't quite understand what you mean by saying that I
shouldn't specify those options in an assignment to strSQL. What is
wrong? Is there a better way to code it?
Your absolutely right about the transaction option but I don't yet
feel confident enough to write one down. I still have to learn how to
write transanctions code.
Best regards, Theodore
--- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote:
>
> Theodore-
>
> First, you shouldn't be trying to specify those options in an
assignment to
> strSQL. And secondly, you don't need those parameters for an
execute of an
> "action" query. And finally, you probably should wrap all of this
inside a
> transaction so that either the order, the order details, and delete
of the
> offer all happen successfully or none of it does.
>
> John Viescas, author
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> Running Microsoft Access 2000
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
> For the inside scoop on Access 2007, see:
> http://blogs.msdn.com/access/
>
>
> -----Original Message-----
> From: [email protected] [mailto:[EMAIL PROTECTED]
On Behalf
> Of tyki9799
> Sent: Saturday, May 06, 2006 4:35 AM
> To: [email protected]
> Subject: [ms_access] Re: Adding a new record
>
> John, I understand that the breakpoint simply stops the code being
> executed at that point. It's not a cause of errors.
>
> If I move the "lngOrderID = rstO!OrderID" statement before the
> "rstO.Update" statement, ie if code looks like this:
>
> rstO!field3 = rstI!field3
> .....
>
> lngOrderID = rstO!OrderID
>
> rstO.Update
>
> I then get this error message: "Run-time error '94': Invalid use of
Null."
>
> It seems no OrderID (autonumber) value is been supplied by SQL Server
> until the "rstO.Update" statement is proccessed.
>
> I followed your istructions and added this piece of code:
>
> Set rstI = db.OpenRecordset("SELECT @@IDENTITY As NewOrderID
" & _
> "FROM tblOrders;", dbOpenDynaset, dbSeeChanges)
>
> lngOrderID = rstI!NewOrderID
>
> rstI.Close
>
> Set rstI = Nothing
>
> At last the Offer Items are turned to Order Items with the correct
> OrderID!
>
> I'm really gratefull! Thanks very much!
>
> One last thing: I have this SQL string to delete the Offer Items as
> they are now turned into Order Items. The problem is that I get
> something wrong in adding the "dbOpenDynaset, dbSeeChanges" parameter
> so I get an error message that the string is wrong. I know it's the
> "dbOpenDynaset, dbSeeChanges" parameter as the SQL string worked fine
> in earlier versions. Any clue?
>
> strSQL = "DELETE tblOfferDetails.* " & _
> "FROM tblOfferDetails " & _
> "WHERE InterestOfferID=" & Me.InterestOfferID & ";" &
> ", dbOpenDynaset, dbSeeChanges"
>
> --- In [email protected], "John Viescas" <JohnV@> wrote:
> >
> > Theodore-
> >
> > Adding a breakpoint shouldn't cause an error. Is it actually
dying on:
> >
> > lngOrderID = rstO!OrderID
> >
> > ??
> >
> > Try moving that statement inside the rst.Update - anywhere after
you've
> > assigned some values to the new row. I know in a JET table, the new
> > "autonumber" is available as soon as you dirty the row. If that
doesn't
> > work, then you'll have to open another recordset on "SELECT
> @@IDENTITY As
> > NewOrder" and use the value of the NewOrder column returned by that
> query.
> >
> > John Viescas, author
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > Running Microsoft Access 2000
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> > For the inside scoop on Access 2007, see:
> > http://blogs.msdn.com/access/
> >
> >
> > -----Original Message-----
> > From: [email protected] [mailto:[EMAIL PROTECTED]
> On Behalf
> > Of tyki9799
> > Sent: Thursday, May 04, 2006 11:56 PM
> > To: [email protected]
> > Subject: [ms_access] Re: Adding a new record
> >
> > John, the "dbSeeChanges + dbAppendOnly" addition works fine!
> >
> > As about the second problem, OrderID is the primary key of tblOrders.
> > It's an integer (int, identity, 4-bit long, ie an autonumber).
> >
> > If I place a breakpoint at the db.Execute strSQL command I get the
> > following error message:
> >
> > "Run-time error '3021': No current record."
> >
> > My regards, Theodore
> >
> > --- In [email protected], "John Viescas" <JohnV@> wrote:
> > >
> > > For the first problem, do:
> > >
> > > Set rstO = db.OpenRecordset("SELECT * FROM tblOrders;", _
> > > dbOpenDynaset, dbSeeChanges + dbAppendOnly)
> > >
> > > Do you have a valid value in lngOrderID? If not, you might need
> to do a
> > > SELECT @@Identity As NewOrder to get the correct order ID.
> > >
> > > You could also try putting a halt on the db.Execute, copy what's in
> > strSQL
> > > into the SQL View of a new query, and see if you can run the query
> > from the
> > > user interface.
> > >
> > > John Viescas, author
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > Running Microsoft Access 2000
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Paris, France)
> > > For the inside scoop on Access 2007, see:
> > > http://blogs.msdn.com/access/
> > >
> > >
> > > -----Original Message-----
> > > From: [email protected] [mailto:[EMAIL PROTECTED]
> > On Behalf
> > > Of tyki9799
> > > Sent: Thursday, May 04, 2006 12:01 AM
> > > To: [email protected]
> > > Subject: [ms_access] Re: Adding a new record
> > >
> > > Hi John! Thanks for your help.
> > >
> > > I followed your advice (adding dbOpenDynaset, dbAppendOnly) so I
ended
> > > up having:
> > >
> > > Set rstO = db.OpenRecordset("SELECT * FROM TableB;", _
> > > dbOpenDynaset, dbAppendOnly)
> > >
> > > Then, unfortunately I faced the following error message.
> > >
> > > "Run-time error '3622': You must use the dbSeeChanges option with
> > > OpenRecordset when accessing a SQL Server table that has an IDENTITY
> > > column."
> > >
> > > Then I tried instead this (I didn't supply the "dbAppendOnly"
> parameter:
> > >
> > > Set rstO = db.OpenRecordset("SELECT * FROM TableB;", _
> > > dbOpenDynaset, dbSeeChanges)
> > >
> > > I tried it and it seems to be working OK though I'm not sure whether
> > > by not supplying the "dbAppendOnly" parameter I'm getting into
future
> > > troubles...
> > >
> > > But then I'm not yet done!
> > >
> > > TableA is tblOffers and TableB is tblOrders. So what I do
(worked fine
> > > in Jet, prior to upsizing) is turning an offer into an order so that
> > > the order hasn't to be created right from the start. Also the items
> > > offered have to be turned into items ordered.
> > >
> > > This is done by getting the new OrderID and assign it to each of the
> > > offered items being turned into ordered items.
> > >
> > > The problem is that when the INSERT INTO query is about to be
executed
> > > I get the following error message:
> > >
> > > "Run-time error '3001': ODBC--call failed."
> > >
> > > And then it points to "db.Execute strSQL, dbFailOnError" command
after
> > > the INSERT INTO query.
> > >
> > > So I end up with the offer turned into order alright but offered
items
> > > aren't turned to ordered items.
> > >
> > > Is there something wrong with the variable declaration or with the
> > > query itself?
> > >
> > > Cheers, Theodore
> > >
> > > p.s. The whole code follows as it is now.
> > >
> > > Private Sub cmdAmendToOrder_Click()
> > >
> > > Dim db As DAO.Database, rstI As DAO.Recordset, rstO As
> > DAO.Recordset
> > > Dim lngOrderID As Long
> > > Dim strSQL As String
> > >
> > > Set db = DBEngine(0)(0)
> > >
> > > Set rstI = db.OpenRecordset("SELECT * FROM tblOffers WHERE...
> > > ;", dbOpenDynaset, dbSeeChanges)
> > >
> > > Set rstO = db.OpenRecordset("SELECT * FROM tblOrders;",
> > > dbOpenDynaset, dbSeeChanges)
> > >
> > > rstO.AddNew
> > >
> > > rstO!field1 = rstI!field1
> > > rstO!field2 = rstI!field2
> > > rstO!field3 = rstI!field3
> > > .....
> > >
> > > rstO.Update
> > >
> > > lngOrderID = rstO!OrderID
> > >
> > > rstI.Close
> > > rstO.Close
> > >
> > > Set rstI = Nothing
> > > Set rstO = Nothing
> > >
> > > strSQL = "INSERT INTO tblOrderDetails (OrderID, field2...)
> " & _
> > > "SELECT " & lngOrderID & " As OrderID, field2...
" & _
> > > "FROM tblOfferDetails " & _
> > > "WHERE OfferID=" & Me.OfferID & ";"
> > >
> > > db.Execute strSQL, dbFailOnError
> > >
> > > End Sub
> > >
> > > --- In [email protected], "John Viescas" <JohnV@> wrote:
> > > >
> > > > You need dbSeeChanges only for a read/write navigable recordset.
> > > For the
> > > > second one, do:
> > > >
> > > > Set rstO = db.OpenRecordset("SELECT * FROM TableB;", _
> > > > dbOpenDyanaset, dbAppendOnly)
> > > >
> > > > You had the dbAppendOnly in the wrong place.
> > > >
> > > > John Viescas, author
> > > > Building Microsoft Access Applications
> > > > Microsoft Office Access 2003 Inside Out
> > > > Running Microsoft Access 2000
> > > > SQL Queries for Mere Mortals
> > > > http://www.viescas.com/
> > > > (Paris, France)
> > > > For the inside scoop on Access 2007, see:
> > > > http://blogs.msdn.com/access/
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: [email protected] [mailto:[EMAIL PROTECTED]
> > > On Behalf
> > > > Of tyki9799
> > > > Sent: Wednesday, May 03, 2006 1:46 AM
> > > > To: [email protected]
> > > > Subject: [ms_access] Adding a new record
> > > >
> > > > Hi everyone!
> > > >
> > > > I recently upsized my Access database to SQL Server (MSDE sp4).
> > > > I now have an .adp file which I use to manage the database (an
> > > > interface to the MSDE backend) and an mdb file that holds the UI
> > > > (forms, etc.). The .mdb file has the MSDE database tables linked
> to it
> > > > through ODBC. The application so far seems to run rather
> smoothly but
> > > > I get a few glitches.
> > > >
> > > > Here is one: I have this code for getting values from TableA
and get
> > > > them to a new record in TableB. It used to run fine prior to
> upsizing
> > > > but now a get an error message (the db.OpenRecordset
"dbSeeChanges"
> > > > parameter was added after the upsizing).
> > > >
> > > > Dim db As DAO.Database, rstI As DAO.Recordset, rstO As
> > > DAO.Recordset
> > > >
> > > > Set db = DBEngine(0)(0)
> > > >
> > > > Set rstI = db.OpenRecordset("SELECT * FROM TableA
WHERE... ;",
> > > > dbOpenDynaset, dbSeeChanges)
> > > >
> > > > Set rstO = db.OpenRecordset("SELECT * FROM TableB;",
> > > > dbAppendOnly, dbSeeChanges)
> > > >
> > > > rstO.AddNew
> > > >
> > > > rstO!field1 = rstI!field1
> > > > rstO!field2 = rstI!field2
> > > > rstO!field3 = rstI!field3
> > > >
> > > > rstO.Update
> > > >
> > > > rstI.Close
> > > > rstO.Close
> > > >
> > > > Set rstI = Nothing
> > > > Set rstO = Nothing
> > > >
> > > > When I run the code (through a command button) I get the following
> > > > error message:
> > > >
> > > > "Run-time error '3251'. Operation is not supported for this
type of
> > > > object."
> > > >
> > > > The error is pointed to the "rstO.AddNew" statement.
> > > >
> > > > Any clue of what's wrong and how to fix it?
> > > >
> > > > Thanks in advance, Theodore
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > Yahoo! Groups Links
> > > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > Yahoo! Groups Links
> > >
> >
> >
> >
> >
> >
> >
> >
> >
> > Yahoo! Groups Links
> >
>
>
>
>
>
>
>
>
> Yahoo! Groups Links
>
SPONSORED LINKS
| Microsoft access database | Database development software | Database management software |
| Database software | Inventory database software | Membership database software |
YAHOO! GROUPS LINKS
- Visit your group "ms_access" on the web.
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
