thank you for the advice. There are two cases were I have an 1-1
relation and they both give me trouble so I think I'll take care of
them following your suggestion.
Regards, Theodore
--- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote:
>
> Theodore-
>
> If all the columns pertain to one offer for one customer, then they
should
> all be in one table. It doesn't make any sense to separate them
into two
> tables related 1-1.
>
> 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: Monday, May 08, 2006 10:17 AM
> To: [email protected]
> Subject: [ms_access] Re: Adding a new record
>
> John,
>
> tblOffers has the primary key identity "OffersID" and it has a number
> of columns such as dates, data about customer requirements, various
> statistical data (various categories), the saleperson that makes the
> offer, etc. All in all there are 32 columns!
>
> Then tblOfferCustomer has again "OffersID" as primary key (not
> identity) and holds all customer data, just as company name, contact
> name, address, phone numbers, etc. This table has 20 columns. Not bad
> at all as you can see.
>
> If I'd like to have instead a single table it will sport as many as 51
> columns! This is the very reason I got them as two separate tables.
>
> The two tables are jointed as a 1-to-1 relationship on their primary
> key, ie "OffersID".
>
> What is your opinion in this case?
>
> Theodore
>
> --- In [email protected], "John Viescas" <JohnV@> wrote:
> >
> > Theodore-
> >
> > It doesn't make sense to me that these two tables are 1-1. What
is the
> > structure of the two tables?
> >
> > 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: Monday, May 08, 2006 2:05 AM
> > To: [email protected]
> > Subject: [ms_access] Re: Adding a new record
> >
> > John, thanks for your instructions on transactions. I'll try to
> > implement transactions as soon as possible..
> >
> > In this database I have tblOffers and tblOfferCustomer. The first
> > table (tblOffers) stores the regular offer data. The second one
> > (tblOfferCustomer) stores all data about an offer's customers. I've
> > decided to have these two tables with a one-to-one relation between
> > them on primary key identity column OfferID, as a single tblOffers
> > table will carry way too many columns (fields). Is this a good
practice?
> >
> > Although in reality I haven't came across any particular problem I
> > know that many db designers strongly oppose this practice. They'd
> > rather have a single table.
> >
> > One problem that I'm facing now that I have upsized the db to SQL
> > server is this:
> > In native Access days (Jet) I had form frmOffers and its Record Source
> > (a query, as usual stored within the form as it's my practice)
> > had fields from both tables. I had no problem to view and add data
> > through this form. Now as I upsized to SQL Server this doesn't work
> > any more. Having fields from both tables won't let me add a new offer.
> > What I did is let the main form having tblOffers as its Record Source
> > and then let a subform to carry the other table's fields.
> >
> > Is there a way to avoid having a subform and still make it work?
> >
> > You help is always appreciated, Theodore
> >
> > --- In [email protected], "John Viescas" <JohnV@> wrote:
> > >
> > > Theodore-
> > >
> > > In your post below you said you were trying to do:
> > >
> > > strSQL = "DELETE tblOfferDetails.* " & _
> > > "FROM tblOfferDetails " & _
> > > "WHERE InterestOfferID=" & Me.InterestOfferID & ";" & _
> > > ", dbOpenDynaset, dbSeeChanges"
> > >
> > > That looked to me like you were trying to embed the dbOpenDynaset,
> > > dbSeeChanges inside the SQL.
> > >
> > > I should have checked - dbSeeChanges is also an option when you use
> > Execute.
> > > Glad to hear you figured it out!
> > >
> > > Transactions are easy. I normally do something like:
> > >
> > > Dim intTrans As Integer
> > >
> > > On Error GoTo Ooops
> > >
> > > '... some code
> > >
> > > BeginTrans
> > > ' Set flag to indicate transaction started
> > > intTrans = True
> > >
> > > ' various updates / inserts / deletes
> > >
> > > CommitTrans
> > > intTrans = False
> > >
> > > ' more code
> > >
> > > ExitPlace:
> > > Exit Sub
> > >
> > > Ooops:
> > > If intTrans Then Rollback
> > > MsgBox "Unexpected error: " & Err & ", " & Error
> > > Resume ExitPlace
> > >
> > >
> > > 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: Sunday, May 07, 2006 3:08 AM
> > > To: [email protected]
> > > Subject: [ms_access] Re: Adding a new record
> > >
> > > John, if I use this SQL sting
> > >
> > > 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" <JohnV@> 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
> > > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > 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.
