Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 79
  • Last Modified:

Recordset error after SQL2008 db restored to SQL2012 platform

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
Gogscott
Asked:
Gogscott
  • 4
  • 2
1 Solution
 
Gustav BrockCIOCommented:
I would suspect the date format. Try:

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

/gustav
0
 
GogscottAuthor Commented:
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
 
GogscottAuthor Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Gustav BrockCIOCommented:
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
 
GogscottAuthor Commented:
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
 
GogscottAuthor Commented:
It works!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now