?
Solved

Recordset error after SQL2008 db restored to SQL2012 platform

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

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 Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 51

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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

764 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