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" <[EMAIL PROTECTED]> 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
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.
