locked
DOA TO ADO RRS feed

  • Question

  • I have been getting an error 3151 when I run this code and the problem seems to be with the underlined section below, I am using Office 2016, this particular code runs perfectly when Im online but for some reason the ODBC connection fails when im offline calling a local table.

    Private Sub Form_Open(Cancel As Integer)
        'Call numon ' Set Numlock on
         
        'Reset Current vAgentID
        vAgentID = 0
    
        If Not IsNull(Me.OpenArgs) Then
            Me.intAgentID = vAgentID
        End If
        If DLookup("IsOnline", "Settings") = 1 Then
            Dim mydb As DAO.Database
            Dim rs As DAO.Recordset
    
            
            Set mydb = CurrentDb
            Set rs = mydb.OpenRecordset("Select Top 1 HubName FROM tblhubs WHERE HubID = " & vHubID, dbOpenSnapshot)
            
            If rs.RecordCount = 1 Then
            vHubName = rs.Fields("HubName").Value
            End If
     
            rs.Close
            Set rs = Nothing
            Set mydb = Nothing
            
            Me.btnCheckStats.Enabled = True
            Me.btnAgentDetails.Enabled = True
            Me.btnOnOffline.Caption = "Online"
        Else
        If DLookup("IsOnline", "Settings") = 0 Then
          Set mydb = CurrentDb
          Set rs = mydb.OpenRecordset("Select Top 1 HubName FROM tblhubs_local WHERE HubID = " & vHubID, dbOpenSnapshot)
          If rs.RecordCount = 1 Then
            vHubName = rs.Fields("HubName").Value
            End If
     
            rs.Close
            Set rs = Nothing
            Set mydb = Nothing
           Me.btnCheckStats.Enabled = False
            Me.btnAgentDetails.Enabled = False
            Me.btnOnOffline.Caption = "Offline"
        End If
        End If
        
    End Sub

    Tuesday, October 10, 2017 1:12 PM

All replies

  •   Set rs = mydb.OpenRecordset("Select Top 1 HubName FROM tblhubs_local WHERE HubID = " & vHubID, dbOpenSnapshot)

    Hi Kuhle,

    Does table "tblhubs_local" exist, with a field "HubName"?

    Imb.

    Tuesday, October 10, 2017 1:56 PM
  • What happens if you set a breakpoint on the next line, print the complete query SQL statement in the immediate window and try to run it as a new query?

    I also think Imb-hb are very pertinent in this case.


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, October 10, 2017 5:14 PM
  • Never forget to include error handling in your procedures.

    Here's an example of how it could be simplified and error handling added

    Private Sub Form_Open(Cancel As Integer)
    10        On Error GoTo Error_Handler
              Dim mydb                  As DAO.Database
              Dim rs                    As DAO.Recordset
              'Call numon ' Set Numlock on
    
              'Reset Current vAgentID
    20        vAgentID = 0
    
    30        If Not IsNull(Me.OpenArgs) Then
    40            Me.intAgentID = vAgentID
    50        End If
    
    60        Set mydb = CurrentDb
    70        Select Case DLookup("IsOnline", "Settings")
                  Case 1 'Working Online
    80                Set rs = mydb.OpenRecordset("Select Top 1 HubName FROM tblhubs WHERE HubID = " & vHubID, dbOpenSnapshot)
    90                If rs.RecordCount = 1 Then
    100                   vHubName = rs.Fields("HubName").Value
    110               End If
    
    120               Me.btnCheckStats.Enabled = True
    130               Me.btnAgentDetails.Enabled = True
    140               Me.btnOnOffline.Caption = "Online"
    150           Case 0 'Working Locally
    160               Set rs = mydb.OpenRecordset("Select Top 1 HubName FROM tblhubs_local WHERE HubID = " & vHubID, dbOpenSnapshot)
    170               If rs.RecordCount = 1 Then
    180                   vHubName = rs.Fields("HubName").Value
    190               End If
    
    200               Me.btnCheckStats.Enabled = False
    210               Me.btnAgentDetails.Enabled = False
    220               Me.btnOnOffline.Caption = "Offline"
    230       End Select
    
    Error_Handler_Exit:
    240       On Error Resume Next
    250       If Not rs Is Nothing Then
    260           rs.Close
    270           Set rs = Nothing
    280       End If
    290       If Not mydb Is Nothing Then mydb = Nothing
    300       Exit Sub
    
    Error_Handler:
    310       MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
                     "Error Number: " & Err.Number & vbCrLf & _
                     "Error Source: Form_Open" & vbCrLf & _
                     "Error Description: " & Err.Description & _
                     Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                     , vbOKOnly + vbCritical, "An Error has Occured!"
    320       Resume Error_Handler_Exit
    End Sub


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, October 10, 2017 5:20 PM
  • If you are getting an ODBC error it sounds to me like the "local" table in your query is linked to an external table in another database that is not on your system. I would open the table and then check the properties to see if this is the case.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, October 10, 2017 5:47 PM
  • Hi Kuhle B,

    you had mentioned that it is working when you are online but it's not working when you are offline.

    if the database stored on local machine then what makes the difference when you are online or offline.

    are you using any other database which stored at some other location?

    what is the value of "vHubID"?

    did you try to run the query directly in Access when you are offline?

    if you did not try that then you can try to make a test and let us know about the result.

    I try to find the information regarding this error.

    I find that error 3151 occurs when Connection get failed.

    which can be caused by networking issue or due to any external applications.

    if you tell us the difference in the situation when you are offline / online then we can try to provide further suggestions that may help you.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 11, 2017 5:37 AM
  • Hi Kuhle B,

    is your issue solved now?

    if yes, try to post the solution and mark it as an answer.

    it will help us to close this thread and in future it will help other users who have same kind of issue like yours.

    if your issue is still exist then try to refer my last suggestions. it may help you.

    if then also you have any further questions regarding this issue then let me know about that.

    I will try to provide you further suggestions.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 18, 2017 6:02 AM