none
MS Access 97 - execution SQL in vba failing ODBC when using local table.... RRS feed

  • Question

  • I am not a coder but I have been asked to update an old Access 97 application database to use our new management system replacing our old one.  There is a form which searches for people using variables entered onto the form.  When the search button is clicked vba in behind inserts the variable into SQL which then searches a table.  The old system was accessed using a connect string in the vba.  I now want the SQL to use a local object, table or view.  I have adapted the SQL and commented out the connect string but I keep getting an error message (#3146) saying that the ODBC failed.  I can find no other connect string in any of the other code.  Assistance is resolving this would be appreciated.

    The sub routine is;

    '===========================================================================
    '   Method:             btnOracareSearch_Click
    '   Purpose:            Search Ora'care for the given NHI and populate the
    '                       OracareSearchResults table with the results..
    '

    '===========================================================================
    Private Sub btnOracareSearch_Click()

        On Error GoTo btnOracareSearch_Click_Error

    Dim strSQL As String
    Dim InsertAnd As Integer
    Dim dbs As Database
    Dim qdf As QueryDef
    Dim rs As Recordset
    'mjb26022004
    Dim rsResults As Recordset

    Dim strName As String
    Dim NewGivenNames As String
    Dim NewAddress1 As String
    Dim NewAddress4 As String



    'mjb 05/09/2014 Not needed. Have on error above.
    'On Error Resume Next

    DoCmd.Hourglass True

    txtOracareFlag.Value = "Y"
    InsertAnd = 0
     
        DoCmd.SetWarnings False
        strSQL = "SELECT * FROM TESTpatients WHERE "
           
     '   DoCmd.SetWarnings False
    '    strSQL = "SELECT * FROM WebpasPatients WHERE "
             
                      
        If Me![Area2] = True Then
            If InsertAnd = 1 Then
              strSQL = strSQL & "AND "
            End If
            InsertAnd = 1
            strSQL = strSQL & "NHI = '" & [Forms]![Advanced Search]![Area 2 Data] & "' "
        End If
        
        If Me![Area3] = True Then
            If InsertAnd = 1 Then
              strSQL = strSQL & "AND "
            End If
            InsertAnd = 1
            strSQL = strSQL & "SURNAME Like '" & [Forms]![Advanced Search]![Area 3 Data] & "%' "
        End If
        
        
        If Me![Area4] = True Then
            If InsertAnd = 1 Then
              strSQL = strSQL & "AND "
            End If
            InsertAnd = 1
            strSQL = strSQL & "GIVENNAMES Like '" & [Forms]![Advanced Search]![Area 4 Data] & "%' "
        End If
        
        strSQL = strSQL & "ORDER BY SURNAME, GIVENNAMES"

        
        
    Me![btnOracareSearch].Caption = "Now Searching..."
    DoCmd.Hourglass True
    Set qdf = CurrentDb.CreateQueryDef("")
    Set dbs = CurrentDb      'Added to try and get it to use local database
    With qdf
      
    '.Connect = "ODBC;DSN=Wwww;Description=Wwwww prod;UID=rrrr;PWD=rrrr;DATABASE=XXXX_PRD_A_YYYY"

      .sql = strSQL
        
      Set rs = .OpenRecordset()
      .Close
    End With

    If rs.RecordCount <> 0 Then
    'mjb26022004
       Set rsResults = CurrentDb.OpenRecordset("OracareSearchResults")

        While Not rs.EOF
            With rsResults
                .AddNew
                !NHI = rs!NHI
                !Title = rs!Title
                !Surname = rs!Surname
                !GivenNames = rs!GivenNames
                !DOB = rs!DOB
                !Gender = rs!Gender
                !HomePhone = rs!HomePhone
                !WorkPhone = rs!WorkPhone
                !ResAddress1 = rs!ResAddress1
                !ResAddress4 = rs!ResAddress4
                .update
                
                
            End With
            rs.MoveNext
        Wend
        rsResults.Close
    End If


    Monday, March 5, 2018 12:22 AM

All replies

  • When you debug the error which line is highlighted as problematic?

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

    Monday, March 5, 2018 1:59 AM
  • I'd be looking over TESTpatients and OracareSearchResults to see if they are the source of the problem.

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

    Monday, March 5, 2018 2:00 AM
  • Did you re-link ODBC database tables in the query back to local Access tables?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, March 5, 2018 1:19 PM
  • Hello warrjack,

    I would suggest you check if the sql is valid. Print out the final sql string and then run it in SQL.

    Besides, according to ODBC - call failed. (Error 3146), please make sure the network is available, and then try the operation again.

    By the way, debugging the error to check which line caused the error will be helpful for us to analyse your issue.

    Best Regards,

    Terry


    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.

    Tuesday, March 6, 2018 3:12 AM