Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Recordset error after SQL2008 db restored to SQL2012 platform

Posted on 2016-10-28
6
Medium Priority
?
68 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 52

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 52

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

604 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