If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Access ADO Form Insert Bug
Strange that a Resync command doesn't work well; as this should work in
every case. Here an exemple of the dummy Resync command that I use: CREATE PROCEDURE dbo.DummyResync (@p1 int, @p2 int, @p3 int, @p4 int, @p5 int) AS SELECT @p1, @p2, @p3, @p4, @p5 GO and I call it this way: DummyResync ?, ?, ?, ?, ? The article referenced in your previous post says to use the following syntax instead: "{call CustordersResync (?)}" but I didn't have the time to make a full analysis of this. For the Profiler, click on File -- New -- Trace; then give the correct connection parameters and click OK. After that, click on Run and you should see a basic trace showing you all the commands sent by Access. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF "Pete Nurse" wrote in message ups.com... Sylvain, thanks for the response. 1/ Yes, I use a single field primary key of type INT with identity set (ie autonumber). 2/ As you suggest, I've tried entering a dummy Resynch command and it seemed to overcome the reported problem, though I don't see how I can enter "a SP without the right number of parameters" without receiving an error. In fact, it seems that entering a non-existent, nonsense SP name works just as well (ie you get an error message). Could you please explain how I can overcome that. I tried entering the Resynch command as recommended at . . . http://msdn.microsoft.com/library/de...ynccommand.asp It seemed to work sometimes but not always. Certainly not as well (apart from the error message) as the dummy SP idea. 3/ I had a look at Profiler, but it's pretty arcane - I have no idea what I'm looking at. Thanks for your response - maybe we're homing in on the solution. |
#12
|
|||
|
|||
Access ADO Form Insert Bug
When I use "DummyResync ?, ?, ?, ?, ?", I get the error "No value
given for one or more required parameters". When I just use "DummyResych" (no parameters), I get no error - especially I don't get the errors that brought me here, except . . . .. . . remember the rogue ID problem from the original post? In the example below empID is the autonumber employee ID and I'm entering a new record "Craigie, Vanessa". After inserting the record, empID entry changes from "(Autonumber)" to the rogue ID (in this case 528, it increments for reason I don't understand - when I started this the ID numbers were in the 400s):- empID Last Name First Name 17853 Boon Elizabeth 17852 Evans Susan 528 Craigie Vanessa Now, empID 528 has now been deleted and long forgotten and I'm sure SQL Server will make sure I never use it again. BTW this rogue ID is the parameter Access sends to the Resync Command SP (I understood enough in SQL Profiler to see that!). After I refresh the form, it reorders and corrects the empID:- empID Last Name First Name 17853 Boon Elizabeth 17852 Evans Susan 30068 Craigie Vanessa The more worrying thing is that the rogue ID may actually exist, in which case I think it can update the wrong data (ie an employee from a totally different company)! This I find quite scarey. I saw this behaviour last week but it was so confused with the other insoluble problems that I didn't follow it up. I can work around the problem (sort of) with the following code which immediately refreshes the form and moves the cursor to the new (empty) record . . . Private Sub Form_AfterInsert() 'Due to a weird bug in Access ADP (in which a completely unrelated record is magically displayed!), 'we need to refresh then reset and then move to the bottom of the subform: Me.Refresh Me.SelTop = Me.RecordsetClone.RecordCount + 1 End Sub .. . . but that is an intellectually unacceptable way to solve the problem. I think this now becomes the main problem. I guess I should start another post, but I'll see how this one flies. Any ideas? |
#13
|
|||
|
|||
Access ADO Form Insert Bug
Any trigger performing any kind of insert (even in a temporary table) on the
SQL-Server side? -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF "Pete Nurse" wrote in message oups.com... When I use "DummyResync ?, ?, ?, ?, ?", I get the error "No value given for one or more required parameters". When I just use "DummyResych" (no parameters), I get no error - especially I don't get the errors that brought me here, except . . . . . . remember the rogue ID problem from the original post? In the example below empID is the autonumber employee ID and I'm entering a new record "Craigie, Vanessa". After inserting the record, empID entry changes from "(Autonumber)" to the rogue ID (in this case 528, it increments for reason I don't understand - when I started this the ID numbers were in the 400s):- empID Last Name First Name 17853 Boon Elizabeth 17852 Evans Susan 528 Craigie Vanessa Now, empID 528 has now been deleted and long forgotten and I'm sure SQL Server will make sure I never use it again. BTW this rogue ID is the parameter Access sends to the Resync Command SP (I understood enough in SQL Profiler to see that!). After I refresh the form, it reorders and corrects the empID:- empID Last Name First Name 17853 Boon Elizabeth 17852 Evans Susan 30068 Craigie Vanessa The more worrying thing is that the rogue ID may actually exist, in which case I think it can update the wrong data (ie an employee from a totally different company)! This I find quite scarey. I saw this behaviour last week but it was so confused with the other insoluble problems that I didn't follow it up. I can work around the problem (sort of) with the following code which immediately refreshes the form and moves the cursor to the new (empty) record . . . Private Sub Form_AfterInsert() 'Due to a weird bug in Access ADP (in which a completely unrelated record is magically displayed!), 'we need to refresh then reset and then move to the bottom of the subform: Me.Refresh Me.SelTop = Me.RecordsetClone.RecordCount + 1 End Sub . . . but that is an intellectually unacceptable way to solve the problem. I think this now becomes the main problem. I guess I should start another post, but I'll see how this one flies. Any ideas? |
#14
|
|||
|
|||
Access ADO Form Insert Bug
No triggers - I've disabled them all.
|
#15
|
|||
|
|||
Access ADO Form Insert Bug
Your pieces of code ring an old bell: in your 1-1 relations, instead of
storing the primary ID of the unique table Employee into the foreign table BenefitType, you are storing the primary ID of the foreign table BenefitType into the primary table Employee. I remember having many problems with this kind schemas under Access many years ago. If I were you, I would try reversing this schema. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF "Pete Nurse" wrote in message oups.com... Thanks Malcolm. 1 How are you passing the company id to the subform's sp? Generally by setting the recordsource eg "EXEC spEmployee 23554" but I have also tried setting the InputParameters property. This problem seems to show up in both instances. 2 What is the param list to the sp? I work in two different ways. Where I had an SP which I wanted to use for several different situations, I pass a string like 'empID=2435' or 'coyID=436'. The SP then decodes the prefix and acts accordingly. Otherwise, I just pass the ID as shown above. 3 What is the complete text of the sp, both the one that gives errors and the one that does not? These two seem to work... SELECT * FROM dbo.tblBenefitType BT RIGHT OUTER JOIN dbo.tblEmployee E ON BT.btID = E.emp_btID LEFT OUTER JOIN dbo.tblCompany E ON E.emp_coyID = C.coyID WHERE (emp_coyID = @coyID) SELECT E.*, BT.* FROM dbo.tblBenefitType BT RIGHT OUTER JOIN dbo.tblEmployee E ON BT.btID = E.emp_btID WHERE (emp_coyID = @coyID) However either of the following creates problems:- SELECT C.*, BT.*, E.* FROM dbo.tblBenefitType BT RIGHT OUTER JOIN dbo.tblEmployee E ON BT.btID = E.emp_btID LEFT OUTER JOIN dbo.tblCompany E ON E.emp_coyID = C.coyID WHERE (emp_coyID = @coyID) SELECT * FROM dbo.tblBenefitType BT RIGHT OUTER JOIN dbo.tblEmployee E ON BT.btID = E.emp_btID LEFT OUTER JOIN dbo.tblCompany E ON E.emp_coyID = C.coyID LEFT OUTER JOIN dbo.tblCorporation Crp ON E.emp_coyID = Crp.crpID WHERE (emp_coyID = @coyID) 4 Do you have 'Link Child Fields' and 'Link master Fields' set on the parent's subform object? No. I've never found that that works with SPs. 5 Do you have 'Input Parameters' set on the subform.Form object? I've tried it. See 1. 6 What were the exact values you tried as the 'Resync Command' and what was the exact behavior that resulted (per Syvain's line) I tried "spDummy" with no parameter. Code below. ALTER PROCEDURE spDummy @Prm1 VARCHAR(50) As PRINT @Prm1 Thanks again. |
#16
|
|||
|
|||
Access ADO Form Insert Bug
No, the relationships are all 1-many - I don't use 1-1. 12 years of
experience designing databases makes me pretty sure the structure is correct. |
#17
|
|||
|
|||
Access ADO Form Insert Bug
I didn't say that your structure was incorrect but that I've seen in the
past the multi-step updating feature of Access having trouble with updating some kind schemas. Are you sure that when you make an insert in the table tblEmploye, new records don't get created into one of the other tables such as tblBenefitType, tblCompany or tblCorporation? Is it possible that the rogue ID could come from one of these three other tables? Also, I took a second look at your original post and you say that the table tblEmploye is the subform and the table tblCompany the main form. However, in your requests, you are extracting the fields of the associated table tblCompany with a Left Outer Join. I don't see the usefulness of this as only one associated company can be displayed at the same time in the main form. In fact, I don't understand at all the use of a Left Outer Join here instead of a regular Inner Join. From your first and third exemple, Access seems to make a difference between « SELECT C.*, BT.*, E.* » and « SELECT * ». What happens if you write down explicitely all the fields? Finally, which version of Access are you using? I hope it's not A2000. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF "Pete Nurse" wrote in message ups.com... No, the relationships are all 1-many - I don't use 1-1. 12 years of experience designing databases makes me pretty sure the structure is correct. |
#18
|
|||
|
|||
Access ADO Form Insert Bug
Thanks for sticking with me, Sylvain. Quick answers to your questions.
I'm using Access 2003. Records aren't being created in other tables. I used "Left Outer Join" so I would display the employee even if the employee didn't have a benefit type. I didn't try further varying the SQL because I'm prepared to mark that solved thanks to you and the the Resync Command (though why it works I'd love to know). My bigger (let's face it my biggest) concern now is the rogue ID problem. I created a brand new ADP file containing only tables, inserted new records into a couple of tables and (depending on the table) one of three things happened:- 1/ The record added as you would expect (just like DAO); 2/ I got the message "The data was added to the database but the data won't be dispalyed in the form because it doesn't satisfy the criteria for the underlying recordsource" 3/ Our old friend the rogue ID appeared and (this is the cruncher) if, after the rogue ID appears, you enter data into the record the real record with that same ID updates! So no forms, VBA code or SPs and the problem still exists. This problem is of too much concern to be buried deep in this current thread. I'd appreciate it if you could follow me to the new thread at microsoft.public.access.adp.sqlserver where I'll include more info. |
#19
|
|||
|
|||
Access ADO Form Insert Bug
I have now convinced myself that the rogue ID problem is SQL Server not
Access (I reproduced the behaviour in Enterprise Manager), so I won't pursue it on the newsgroup. I'll pick it up at microsoft.public.sqlserver. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
One user cannot access custom form????? | Jenai | Contacts | 0 | September 27th, 2005 09:35 PM |
Why is a French Spellchecker a "required" update for English speak | French Spellcheck Required? | General Discussion | 23 | April 26th, 2005 01:17 AM |
Adding staff photographs to my database | KK | New Users | 2 | September 3rd, 2004 07:41 AM |
Automatically insert in an Access Form | Peter L | Using Forms | 3 | September 1st, 2004 12:36 PM |
Upload Image | Jason MacKenzie | General Discussion | 1 | September 1st, 2004 04:38 AM |