MS Access FE to SQL Server BE, can't add new Record to a table via ADODB

I'm in the process of migrating to SQL Server for my BE away from MS Access.

Before we start I have checked and double checked the PK is ok. There is 2 x 'Bit' fields but both are set to ((0)) with NO NULL set. I believe this is a 'SSMA_timeStamp issue'.

I have a table in SQL Server called tbl_WorkOrders and I want to add a new record to it (this has worked for almost 15 years). User presses 'Add New' on a form and following code code runs then a new form opens to the new record and user enters details.

Function funCreateNewWO() As Long

Dim adoRst As New ADODB.Recordset
Dim adoConn As ADODB.Connection
Dim sqlString As String

    Set adoConn = CurrentProject.Connection
    sqlString = " SELECT [WO_ID_PK] FROM tbl_WorkOrders"
   
    With adoRst
        .Open sqlString, adoConn, adOpenStatic, adLockOptimistic
        If .State = adStateOpen Then
            .AddNew
            .Update
            funCreateNewWO = !WO_ID_PK
        End If
        .Close
    End With
End Function

Now when I run with SQL BE and execute the .Update line I get

"ODBC-- call fail"

As a test I

Created a view sv_WorkOrders in SQL on tbl_WorkOrders and removed the 'SSMA_timeStamp' field
Created a linked table in MS Access to sv_WorkOrders
Ran above code substituting tbl_WorkOrders for sv_WorkOrders

It all worked fine. So I thought 'HaHa I've found you'. So I read up more on TimeStamps and I was convinced to leave them in and work with them.

So I ran above code with a few changes allowing for SSMA_timeStamp

If .State = adStateOpen Then
   .AddNew
   !SSMA_timeStamp = Now()
   .Update
   funCreateNewWO = !WO_ID_PK
End If

Now when I execute !SSMA_timeStamp = Now() I get the following error.

"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

What can I try next? I've been reading about the above error and there are suggestions that it may be text field errors but at this stage text fields have not been altered.

Update

I have tested using DAO and I'm still getting errors but the errors codes are more interesting.

Function funTestDAO()

    Dim myRST As DAO.Recordset
    Dim sqlString As String
    Dim lTest As Long

    sqlString = "tbl_WorkOrders"
    Set myRST = CurrentDb.OpenRecordset(sqlString, dbOpenDynaset, dbSeeChanges)
    With myRST
         If .EOF = False Then
            .AddNew
            .Update
            lTest = !WO_ID_PK
        End If
    End With
End Function

Then I get the following errors when I execute the .Update line I get the ODBC--call failed

544 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot insert explicit value for identity column in table 'tbl_WorkOrders' when IDENTITY_INSERT is set to OFF. ODBC.Recordset

Ok, first up:

The so called "timestamp" column has NOTHING but I repeat NOTHING to do with "time". That column is what we call a "row version". If you are wondering, behind the scenes that "row version" column is actually a double int column, and it simply increments by +1 each time that row is updated.

To be fair, calling that column type "timestamp" is the world's worst name in the history of databases, since such row version columns are not a time-based column. Such row version columns are managed by the database system, and you NEVER I repeat NEVER will touch, modify, look at, or care about this column. Most systems such as .net or MS-Access? They can and will use this "row version" technology to determine if the record been updated. This eliminates the need for the client software to do a column by column compare to the loaded record against the server record to detect if changes have been made.

You the developer don't care, nor never will attempt to look at, use or bother with these so-called row version columns. To be fair, in the last few years, you will see on Microsoft documentation they now use the term “row version” and not the term “timestamp”. However, there is “years” of documentation that has their older term “timestamp”.

While in the SSMS table designer, the option still appears as “timestamp”, it is still what we call a row version data type of a column, and as noted, it actually is simple long integer value that increment by +1 each time the row is updated. Such a column is not time based, and does not even store a date/time value.

This row version column as noted is used by client software to detect if the record been changed.

Next up:

If you using a SQL insert statement, then all columns will automatically take on their assigned default value specified in that SQL server table.

However, if you using a DAO, or ADO record insert command? Then those columns DO NOT take on the default column settings. So, if you add a new record by a linked table (in a form), then the SQL server default column values are used.

If you use DAO/ADO and a SQL insert statement, then again, the SQL server default values are automatic set when the new row is created.

HOWEVER! if you use the .new row method of a reocrdset? Then the SQL server default values are NOT used.

So, if I have this simple table in SQL server:

Now how in above, I don't allow nulls for my 2 true/false (bit) columns in above.

Thus, if adding by using a form, or SQL insert statements? Then those 2 example bit columns will take on the default value setting I have (of 0 in this case).

However, if I use ado/dao and new row? Then you have to in code set the default value(s). You could I suppose remove the default values in the SQL table, but either way, the simple issue is that default values are not set by DAO/ADO when using new row method of a reocrdset object.

So, the required code for above then becomes this:

Sub TestADOAddRow()

   Dim rst          As New ADODB.Recordset
   Dim adoCon       As ADODB.Connection
   Dim sqlString    As String
   
   Dim lngNewPK     As Long
   
   
   Set adoCon = CurrentProject.Connection
   
   sqlString = "SELECT * FROM tblHotelsD"
   
   With rst
      .Open sqlString, adoCon, adOpenDynamic, adLockOptimistic
      .AddNew
      !Active = True
      !HasBalcony = True
      .Update
       lngNewPK = rst!ID
      .Close
   End With
   
   Debug.Print "New pk = " & lngNewPK
   
End Sub

So, note how I had/have to set the 2 columns default. I could of course eliminate the need to set "Active" and "HasBalcony" to false if I remove the restriction in the table to not allow nulls. But, as pointed out, often null bits will confuse Access. In most cases, you can get away with null bits, but it is MUCH better to set the defaults to 0 (or 1) at row create time.

I will note and point out this same issue exists when using .net and datatable(s), since they again also when using the .new row method of the data table object don't obtain nor get/grab the defined defaults you setup in SQL server.

So, as noted, there are 2 work arounds:

Use a real SQL server insert statement (but, this for reocrdsets of course is painfull).

Or, upon .AddNew, then set the defaults for ANY column that is not allowed nulls, and thus requires a default value.

MS Access FE to SQL Server BE, can't add new Record to a table via ADODB

Update

1条答案

相关问题

热门标签

最新问答