Solved

Recordset error after SQL2008 db restored to SQL2012 platform

Posted on 2016-10-28
6
45 Views
Last Modified: 2016-11-08
I have a MS Access2007 application talking to a SQL Server 2008 database; this has been running for 6 years without a gliche.  I have just restored the database to SQL2012 server, running MS Access 2013 (with Access2007 format file) as the application client, and I now get the following error message when I try to edit new records:

"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."

Strangely, I can edit records that were entered into the database PRIOR to the move to SQL 2012.  This is the code behind the sub-form causing the error:

******

Private Sub Form_Open(Cancel As Integer)

On Error GoTo smsErrHandle

'   set variables
    Dim varDate As Variant, strCriteria As String, strTicket As String
    Dim dStart As Variant, dEnd As Variant
   
    dStart = Format(Date, "mm/dd/yyyy")
    dEnd = Format(Date, "mm/dd/yyyy")
   
    strCriteria = "DATEDIFF(day, Tbl_Job_Load.Record_Entered, '" & dStart & "') <= 1 AND DATEDIFF(day, Tbl_Job_Load.Record_Entered, '" & dEnd & "') >= 1 "
    'strCriteria = strCriteria & " AND Tbl_Job_Detail.Record_Business = " & basBusiness()
   
'   open connection
    If cnn.State = 0 Then
        intCNN = basOpenConnection()
    End If
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cnn
        .CommandType = adCmdStoredProc
        .CommandText = "spJob_Listing"
        .Parameters.Refresh
        .Parameters("@vCriteria") = strCriteria
        .Execute
    End With

'   create recordset
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    With rs
        .CursorLocation = adUseClient
        .Open cmd, , adOpenDynamic, adLockOptimistic
        Set Me.Recordset = rs
    End With
    Set rs = Nothing
    Set cmd = Nothing
   
    Me.UniqueTable = "Tbl_Job_Load"
   
Exit Sub

smsErrHandle:
    basPostError Err, Error(Err), , Me.Form.Name, "Form", "Open"
    Exit Sub
End Sub

******

and this is the content of the SQL stored procedure:

******

ALTER PROC [dbo].[spJob_Listing]
      @vCriteria varchar(1024)
AS
EXEC('      SELECT      Job_Customer_Code,
            Job_Date,
            Job_ID,
            Job_Load_Fleet,
            Job_Load_Driver,
            Job_Load_Division,
            Job_Load_Collection,
            Job_Load_Delivery,
            Job_Load_Ticket_Number,
            Job_Load_Invoice_Number,
            Job_Load_Invoice_Record,
            Job_Load_Invoice_Date,
            Job_Load_Time_Batch,
            Job_Load_Time_OnSite,
            Job_Load_Time_OffSite,
            Job_Load_Time_Waiting,
            Job_Load_Flag,
            Job_Load_Note,
            Job_Load_ID,
            Load_Charge_Load,
            Load_Charge_Meterage,
            Load_Charge_Radial,
            Load_Charge_Radial_Rate,
            Load_Charge_Calc_Meterage,
            Load_Charge_Calc_Fleet,
            Load_Charge_Calc_Difference,
            Load_Charge_Waiting,
            Load_Charge_Waiting_Rate,
            Load_Charge_Start_Type,
            Load_Charge_Start_Rate,
            Load_Charge_Day,
            Load_Charge_Calc_Shortfall,
            Load_Charge_ID,
            (SELECT VEKData2008.dbo.funcReturnLoadAdditions (Job_Load_ID)) As ''Load Additions'',
            (SELECT VEKData2008.dbo.funcReturnLoadTotal (Job_Load_ID)) As ''Ticket Total'',
            (SELECT VEKData2008.dbo.funcReturnLoadShortfall (Job_Load_ID)) As ''Ticket Shortfall''
            Tbl_Job_Load.Record_Updated As Job_Updated,
            Tbl_Job_Load.Record_User As Job_User,
            Tbl_Load_Charges.Record_Updated As Load_Updated,
            Tbl_Load_Charges.Record_User As Load_User
      FROM      Tbl_Job_Detail
      JOIN      Tbl_Job_Load ON Job_ID = Job_Load_Job
      LEFT JOIN Tbl_Load_Charges ON Job_Load_Id = Load_Charge_Load
      WHERE ' + @vCriteria + '
      ORDER BY Job_Date desc, Job_Customer_Code')
-- check for errors
IF (@@ERROR <> 0)
      RETURN -1

*******

The above code statements have not changed in 5 years, only the production environment.  

Any help much appreciated!

Liz
0
Comment
Question by:Gogscott
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41863709
I would suspect the date format. Try:

    dStart = Format(Date, "yyyy-mm-dd")
    dEnd = Format(Date, "yyyy-mm-dd")

/gustav
0
 

Author Comment

by:Gogscott
ID: 41866699
Hi Gustav, the actual recordset is returning data so the format of the date does not make any difference.  I noticed that any data entered BEFORE the upgrade to SQL 2012 is NOT affected, only records added since the upgrade appear affected.

Does that shed any more light?  Is there any fundamental difference to indexing / relationships under SQL 2012 compared to SQL 2008?
0
 

Author Comment

by:Gogscott
ID: 41866703
The general Microsoft "help" on this error message is as shown below:

"This error can occur if the data you are attempting to save is saving to a Microsoft SQL Server 7.0 or earlier database and the RecordSource for the subform is using an SQL select statement with fields pulled directly from the source table, and the table is using an Identity field set as its primary key.
To avoid this problem set the RecordSource property of the subform to a table or a query.
-Or-
Upgrade to a newer version of Microsoft SQL Server."

Well, I'm not running SQL 7.0 and it works fine under SQL 2008!  Do I have to "tweak" my recordset now to run under SQL 2012??
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41866724
I noticed that any data entered BEFORE the upgrade to SQL 2012 is NOT affected, only records added since the upgrade appear affected.

Then you'll have to study the subtle details between old and new. That should indicate what needs to be corrected.

/gustav
0
 

Accepted Solution

by:
Gogscott earned 0 total points
ID: 41872757
It turns out the problem was caused by a trigger not firing off and so some of the required data was missing from one of the tables in the recordset join!!

Have now re-instated the trigger and the problem has been resolved!

Many thanks for your feedback.
0
 

Author Closing Comment

by:Gogscott
ID: 41878473
It works!
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question