new records not displayed in Access adp / SQL 2

[ronspree]

Hi,

I use an Access2000 ".adp" front end to add records to an SQL 7.0 database. My form's recordsource in a query involving four tables, two input parameters. Everything seems fine until I add records. It displays:


The data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source.


And true enough, the new records are not displayed. When I re-open the form, however, the new records are now displayed.

Why is this happening? How do I prevent this odd behavior?

Thanks in advance.
Ron

 

[Steve101]

Ron,

This is a "feature" that I thinki is particular to SQL Server 7 connections, and not SLQ Server 2000.

You can address it by programatically using the form's ResyncCommand method on the form's BeforeUpdate event; an example from one of my programs is:

'--------------------------------------------------------------
'Condition the resync command to work in both insert and update situations.
'--------------------------------------------------------------
sq = "SELECT * from tblIndividual " & _
     "WHERE  IndividualId="
If IsNull(frmFMMI!IndividualId) Then
   frmFMMI.ResyncCommand = sq & "@@Identity"
Else
   frmFMMI.ResyncCommand = sq & frmFMMI!IndividualId
End If

I hope that you can adapt this to resolve your situation. You may need to further research the method, and the @@Identify SQL identifier, to better understand whats happening.


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[ronspree]

Thanks Steve, I'll see if I can resolve it.

 

[cjowsey]

The message can happen with SQL 2000 as well. You don't need to use code to get around it. Set the UniqueTable field on the form to the table that drives the SQL view and set the ResyncCommand field with question marks for all columns in the primary key.

For example, I have a form with a recordsource of "vewLedgerCard"[/color blue] driven primarily from the table "Ledger"[/color blue], which I set as the UniqueTable. My ResyncCommand is "SELECT * FROM dbo.vewLedgerCard WHERE MatterID = ? AND LedgerID = ?"[/color blue]. Initially, I missed one of the primary key fields out of the Resync statement and spent a very long time working out what was wrong.

Access updates tables and views bound to forms through the primary key. In many cases it can work it out by itself. However, it never does any harm to give Access a helping hand.

Clive

 

[Steve101]

Clive: beautiful; works like a charm! though I dont understand why as the 'something = ?' is not valid sql syntax.
What is the source of your solution; I searched high and low for resolving this some time ago, and got the problem solved eventually through trial and error using the method I proposed. I'd love to know though where your solution is documented.

Cheers,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[cjowsey]

I found it in the Access help under ResyncCommand and also through a Google search of newsgroups looking for the data error which I think was 31003. The thing that tripped me for a long time was ensuring that I had ALL the primary key fields in the Resync statement. Normally I have a single numerid column for the primary key. In that case, I foolishly used a compound key.

 

[ronspree]

Thanks Clive,

I remember giving up .adp primarily because of this annoyance. Haha. Thanks for making the solution clearer.

Ron

 

[BobJacksonNCI]

Ron,

FYI, the Microsoft response to this issue is in the Knowledgebase:

http://support.microsoft.com/default.aspx?scid=kb;en-us;287627&Product=acc2000

I'm also struggling with Access Projects using Access 2000 connected to SQL 7. I've wrestled with numerous issues, many of which suggest the problems exist in this combination only. No coincidence Access 2000 was the first release with the .ADP feature...

That's pretty much confirmed in the chat that included Bill Ramos, Lead Program Manager:

http://msdn.microsoft.com/chats/office/office_092602.asp

PERTINANT EXCERPT FROM THAT CHAT:
Host Guest_Bill_Ramos_MS
Q: AndyG: Are there major functional or procedural differences for accessing SQL from Access XP vs. Access2K?

Host Guest_Bill_Ramos_MS
A: Huge. Access XP now has extended property support against SQL Server 2000. This gives you lookup support, caption text, all the goodies that you get against Jet. We also have a new stored procedure designer for making action queries.

Host Guest_Bill_Ramos_MS
A: cont. - We also fixed a bunch of bugs with regards to maintaining cursors in your application when changing a sort or filter condition.

Host Guest_Bill_Ramos_MS
A: cont. - If you want to use ADPs, you really need to use Access 2002 (XP)



HTH,
Bob

 

[Steve101]

Thanks for that further qualification Bob.


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)