none
Old ADP To ACCDB - Input Needed, SQL Server 2000 RRS feed

  • Question

  • Hi, all. I just inherited the task of resurrecting an old Access 2010 based app that connects to SQL Server 2000 (yes, 2000). It works now just fine, but the company is pushing out Access 2016 in two weeks, thus need to get it working in an ACCDB. This just needs to work for another seven months, so a re-write is not happening. I have two main questions initially:

    First, what's the best way to link the SQL tables and queries/views? The ADP used to handle this inherently. We'll be using SQL authentication, and I want to make sure SQL permissions are imposed on all objects. All inserts and updates are handled via stored procedures, so users mostly have read-only access to data anyway. Whatever method, if I update a SQL Table or View, how will the change propagate to Access? Users are all over the place and will run the app from their desktops, some via a VPN connection. Again, the current ADP is working fine.

    Second, can I still use "CurrentProject.Connection"? There are hundreds of such calls throughout the app. If not, what is a good alternative. Finding a way to keep this working is of utmost importance, time-wise. 

    Any input is very much appreciated.

    Here's how the app currently establishes the all-important initial connection:

    Private Sub cmdLogin_Click()
    On Error GoTo Err_cmdLogin_Click
    
        Dim conn As New ADODB.Connection
        Set conn = CurrentProject.Connection
        
        Dim stDocName As String
        Dim stLinkCriteria As String
        Dim strConnection As String
        
        Call GetServerName
        
        strModuleName = "cmdLogin_Click"
        
            strConnection = "Provider=SQLOLEDB.1;" & _
                "Persist Security Info=True;" & _
                "Data Source=" & Me.txtServerName & ",1433;" & _
                "User ID=" & Me.txtLoginName & ";" & _
                "Password=" & Me.txtPassword & ";" & _
                "Initial Catalog=CRP;" & _
                "Data Provider=SQLOLEDB.1"
        
        Application.CurrentProject.OpenConnection strConnection


    Here is a common call to a stored procedure. I'm really hoping to get this working in the ACCDB with as little modification as possible, given the sheer number of occurrences of these:

    Private Sub GettblLogin()
    On Error GoTo Err_GettblLogin
    
        strModuleName = "GettblLogin"
    
        Dim conn As ADODB.Connection
        Dim adotblLoginRS As New ADODB.Recordset
        Dim cmdChecktblLogin As New ADODB.Command
    
        Set conn = CurrentProject.Connection
        Set cmdChecktblLogin = New ADODB.Command
    
        cmdChecktblLogin.ActiveConnection = conn
        cmdChecktblLogin.CommandText = "usp_tblLogin_s"
        cmdChecktblLogin.CommandType = adCmdStoredProc
        cmdChecktblLogin.Parameters.Refresh
    
        cmdChecktblLogin.Parameters("@UniqueID") = Me.txtLoginName
    
        Set adotblLoginRS = cmdChecktblLogin.Execute
    
        '********************
        '* Check for Errors *
        '********************
        If TSQLErrorCheck(cmdChecktblLogin("@RetCode"), cmdChecktblLogin("@RetMsg"), _
            Me.Name & "," & strModuleName) Then
                MsgBox "Unable to get Login Info.", , "Error - Unable to Get Login Info."
                Me.txtServerName = Null
                GoTo Err_GettblLogin
        End If
    
        If adotblLoginRS.RecordCount = 0 Then
            blntblLoginFound = False
        Else
            blntblLoginFound = True
            Me.txtLoginID = adotblLoginRS("LoginID")
            Me.txtUniqueID = adotblLoginRS("UniqueID")
            Me.txtRoleID = adotblLoginRS("RoleID")
            Me.txtSecurityCode = adotblLoginRS("SecurityCodeID")
        End If
    
        adotblLoginRS.Close
        Set adotblLoginRS = Nothing
        Set cmdChecktblLogin = Nothing
    
    Exit_GettblLogin:
        Exit Sub
    
    Err_GettblLogin:
        ErrorHandler 5, Err.Number, Err.Description, Me.Name & "," & strModuleName, False
        Resume Exit_GettblLogin
    
    End Sub

                                           

    Wednesday, May 30, 2018 6:44 PM

Answers

  • A few things:

    Yes, you can continue to use currentproject.connection.

    This will assume you hitting a table that is a linked table, and you are filling a ADO recordset.

    However in the case of the store proc?

    You can use this code:

    Sub GettblLogin()
    
       Dim rsttblLoginRS As dao.Recordset
       
        With CurrentDb.QueryDefs("qryPassR")
           .SQL = "usp_tblLogin_s '" & Me.txtLoginName & "'"
          Set rsttblLoginRS = .OpenRecordset
       End With
           
       If rsttblLoginRS.RecordCount = 0 Then
          blntblLoginFound = False
       Else
          blntblLoginFound = True
          Me.txtLoginID = rsttblLoginRS("LoginID")
          Me.txtUniqueID = rsttblLoginRS("UniqueID")
          Me.txtRoleID = rsttblLoginRS("RoleID")
          Me.txtSecurityCode = rsttblLoginRS("SecurityCodeID")
        End If
        rsttblLoginRS.Close
           
    End Sub
    

    The only new assumption here is that you created a pass-though query called “qryPassR”. You can create the “one” query called qryPassR and use it over and over in code, or create a separate query for this logon routine. You note how you really don't need (or want) to mess with connection strings in code. And you can see that calling the store proc this way is not a lot of code. Do note that the above example is DAO code - it kind of a "larger" decision on your part if you want to introduce DAO code into your project since I betting "most" code is ADO, and can remain as such. If you going to keep the ADO code "as is", then you will have to change the use of currentproject.connection when calling store procs (note how I said  you can use current project.Connection, but ONLY for linked tables, not when you call a store proc).

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by SmackMule Tuesday, June 5, 2018 12:49 PM
    Thursday, May 31, 2018 4:06 AM

All replies

  • Hi,

    Like I said earlier in your other post, I think your code should transfer fine into ACCDB but doing a small test first should show any potential problems.

    Good luck!

    Wednesday, May 30, 2018 7:08 PM
  • A few things:

    Yes, you can continue to use currentproject.connection.

    This will assume you hitting a table that is a linked table, and you are filling a ADO recordset.

    However in the case of the store proc?

    You can use this code:

    Sub GettblLogin()
    
       Dim rsttblLoginRS As dao.Recordset
       
        With CurrentDb.QueryDefs("qryPassR")
           .SQL = "usp_tblLogin_s '" & Me.txtLoginName & "'"
          Set rsttblLoginRS = .OpenRecordset
       End With
           
       If rsttblLoginRS.RecordCount = 0 Then
          blntblLoginFound = False
       Else
          blntblLoginFound = True
          Me.txtLoginID = rsttblLoginRS("LoginID")
          Me.txtUniqueID = rsttblLoginRS("UniqueID")
          Me.txtRoleID = rsttblLoginRS("RoleID")
          Me.txtSecurityCode = rsttblLoginRS("SecurityCodeID")
        End If
        rsttblLoginRS.Close
           
    End Sub
    

    The only new assumption here is that you created a pass-though query called “qryPassR”. You can create the “one” query called qryPassR and use it over and over in code, or create a separate query for this logon routine. You note how you really don't need (or want) to mess with connection strings in code. And you can see that calling the store proc this way is not a lot of code. Do note that the above example is DAO code - it kind of a "larger" decision on your part if you want to introduce DAO code into your project since I betting "most" code is ADO, and can remain as such. If you going to keep the ADO code "as is", then you will have to change the use of currentproject.connection when calling store procs (note how I said  you can use current project.Connection, but ONLY for linked tables, not when you call a store proc).

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by SmackMule Tuesday, June 5, 2018 12:49 PM
    Thursday, May 31, 2018 4:06 AM
  • Hello SmackMule,

    What's the current state of the thread? Does anybody's suggestion works for you? If there is, please mark the helpful reply to close the thread. If not, please feel free to let know the current 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, June 5, 2018 7:51 AM
  • This was *very* helpful, thank you. 
    Tuesday, June 5, 2018 12:50 PM