none
ODBC and ADO connections RRS feed

  • Question

  • Hi All,

    I am developing an audit trail for a table in an Access FE application with SQL server DB BE linked via ODBC connectivity.<o:p></o:p>

    I followed this process http://www.fontstuff.com/access/acctut21.htm#audit1.<o:p></o:p>

    To start with I created the tblAuditTrail in the FE files while the rest of the tables in the application are linked, this worked fine. I then upscale the tblAuditTrail to SQL and replaced the name with the linked table name in the code:<o:p></o:p>

    Sub AuditChanges(IDField As String)
        On Error GoTo AuditChanges_Err
        Dim cnn As ADODB.Connection
        Dim rst As ADODB.Recordset
        Dim ctl As Control
        Dim datTimeCheck As Date
        Dim strUserID As String    
      
        Set cnn = CurrentProject.Connection        
        Set rst = New ADODB.Recordset
    
        rst.Open "SELECT * FROM dbo_tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic  
        datTimeCheck = Now()
        strUserID = Environ("USERNAME")
        For Each ctl In Screen.ActiveForm.Controls
            If ctl.Tag = "Audit" Then
                If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                    With rst
                        .AddNew
                        ![FormName] = Screen.ActiveForm.Name                   
                        ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                        ![FieldName] = ctl.ControlSource
                        ![OldValue] = ctl.OldValue
                        ![NewValue] = ctl.Value
                        ![UserID] = strUserID
                        ![DateTime] = datTimeCheck
                        .Update
                    End With
                End If
            End If
        Next ctl
    AuditChanges_Exit:
        On Error Resume Next
        rst.Close
        cnn.Close
        Set rst = Nothing
        Set cnn = Nothing
        Exit Sub
    AuditChanges_Err:
        MsgBox Err.Description, vbCritical, "ERROR!"
        Resume AuditChanges_Exit
    End Sub

    I now get: "ERROR! ODBC--- Call failed"  

    It’s being a long time since I did anything like this and I am a bit confused with ADO and ODBC connections.  Can anyone please explain how this is supposed to work? Or point me in the right direction please?<o:p></o:p>

    Thank you<o:p></o:p>

    Aku<o:p></o:p>


    Akuyali

    Wednesday, February 17, 2016 11:24 AM

Answers

  • Dear All,<o:p></o:p>

    I had to drop the sql table and recreate it by exporting from Access via the same ODBC connectivity.  The linked table was automatically created and happy days.<o:p></o:p>

    Thanks to all who had a look.<o:p></o:p>

    Aku<o:p></o:p>


    Akuyali

    • Marked as answer by AkuYali Thursday, February 18, 2016 11:06 AM
    Thursday, February 18, 2016 11:05 AM

All replies

  • >>>I then upscale the tblAuditTrail to SQL and replaced the name with the linked table name in the code

    Do you mean that you have changed structure of tblAuditTrail in SQL server after creating a link, if so, when you link to a remote table, Access stores metadata about that table. When you later change the table structure, the metadata doesn't get updated to capture the change.

    Delete the link. Then recreate the link. That way the metadata will be consistent with the current version of the table.

    In addition I suggest that you could get More Information on the ODBC Call Failed Error, that will help us resolve your issue.

    For more information, click here to refer about How To Get More Information on the ODBC Call Failed Error

    Thanks for your understanding.

    Thursday, February 18, 2016 6:09 AM
  • Good morning David,

    thank you for the response.  

    I didn't change the structure of the table just imported on to the SQL Server DB.

    I changed the error potion of the code like : 

    AuditChanges_Err:
        'MsgBox Err.Description, vbCritical, "ERROR!"
         MsgBox Errors.Count
          For Each MyError In DBEngine.Errors
            With MyError
              MsgBox .Number & " " & .Description
            End With
          Next MyError
        Resume AuditChanges_Exit

    and it returned the number "1"  and "3251 Operation is not supported for this type of object"

    I also deleted and recreated the link with no joy!

    thank you

    Aku


    Akuyali

    Thursday, February 18, 2016 9:28 AM
  • Dear All,<o:p></o:p>

    I had to drop the sql table and recreate it by exporting from Access via the same ODBC connectivity.  The linked table was automatically created and happy days.<o:p></o:p>

    Thanks to all who had a look.<o:p></o:p>

    Aku<o:p></o:p>


    Akuyali

    • Marked as answer by AkuYali Thursday, February 18, 2016 11:06 AM
    Thursday, February 18, 2016 11:05 AM