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: ms_access@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf
Of tyki9799
Sent: Monday, May 08, 2006 10:17 AM
To: ms_access@yahoogroups.com
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 ms_access@yahoogroups.com, "John Viescas" <[EMAIL PROTECTED]> 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: ms_access@yahoogroups.com [mailto:[EMAIL PROTECTED]
On Behalf
> Of tyki9799
> Sent: Monday, May 08, 2006 2:05 AM
> To: ms_access@yahoogroups.com
> 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 ms_access@yahoogroups.com, "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: ms_access@yahoogroups.com [mailto:[EMAIL PROTECTED]
> On Behalf
> > Of tyki9799
> > Sent: Sunday, May 07, 2006 3:08 AM
> > To: ms_access@yahoogroups.com
> > 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 ms_access@yahoogroups.com, "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: ms_access@yahoogroups.com [mailto:[EMAIL PROTECTED]
> > On Behalf
> > > Of tyki9799
> > > Sent: Saturday, May 06, 2006 4:35 AM
> > > To: ms_access@yahoogroups.com
> > > 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 ms_access@yahoogroups.com, "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: ms_access@yahoogroups.com [mailto:[EMAIL PROTECTED]
> > > On Behalf
> > > > Of tyki9799
> > > > Sent: Thursday, May 04, 2006 11:56 PM
> > > > To: ms_access@yahoogroups.com
> > > > 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 ms_access@yahoogroups.com, "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: ms_access@yahoogroups.com
[mailto:[EMAIL PROTECTED]
> > > > On Behalf
> > > > > Of tyki9799
> > > > > Sent: Thursday, May 04, 2006 12:01 AM
> > > > > To: ms_access@yahoogroups.com
> > > > > 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 ms_access@yahoogroups.com, "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: ms_access@yahoogroups.com
> [mailto:[EMAIL PROTECTED]
> > > > > On Behalf
> > > > > > Of tyki9799
> > > > > > Sent: Wednesday, May 03, 2006 1:46 AM
> > > > > > To: ms_access@yahoogroups.com
> > > > > > 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.
