Old ADP To ACCDB - Input Needed, SQL Server 2000

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

                                       

Answer 1

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!

Answer 2

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

Answer 3

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 [e-mail].

Answer 4

This was *very* helpful, thank you.