function help needed

[JohnE]

Hi. Below is all the code that is used to trace the use of controls in a
rather large Access app that has been around for 10+ years. It is so large
that the backend is in SQL2005 (4+gigs). The code below should be in a
global type function but I am not familiar with how I would construct it with
what there is here.

The Call line is another Access function that another developer did prior to
me working on this and it is used throughout the app. And it works well.
The sql line is from a stored procedure (IntranetUsageAdd) that another
developer did some time ago as well.

Dim sql As String
Dim ButtonName As String
Dim Location As String
ButtonName = "ChangeDatabasePassword"
Location = "Switchboard"

sql = "IntranetUsageAdd 'button' , " & gUserID & ", '" & Location & "', '" &
ButtonName & "', Null, Null, Null, 0, 1"

Call RunSQLServerStoredProcedure(sql)

The stored proc could change anytime. And if that occurred, I would need to
hunt down all the locations this code has been placed and change each one. I
would like to be ahead of that when it occurs.

So, I am seeking help on making this into a function that can be called and
one place to make any changes.
Thanks.
John

 

[Stefan Hoffmann]

hi JohnE,

sql = "IntranetUsageAdd 'button' , " & gUserID & ", '" & Location & "', '" &
ButtonName & "', Null, Null, Null, 0, 1"

Call RunSQLServerStoredProcedure(sql)

The stored proc could change anytime. And if that occurred, I would need to
hunt down all the locations this code has been placed and change each one. I
would like to be ahead of that when it occurs.

The key to this situation is early binding.

So, I am seeking help on making this into a function that can be called and
one place to make any changes.

This is the right way. First of all, locate your
RunSQLServerStoredProcedure() method and rename it.

After that create for each compile error (method not found) a new
method, e.g. in your case above

Public Sub IntranetUsageAdd(AControlType As String, _
AUserID As Long, _
ALocation As String, _
AButtonName As String)

Dim Sql As String

Sql = "IntranetUsageAdd '" & AType & "' , " & _
AUserID & ", '" & _
ALocation & "', '" & _
AButtonName & "', " & _
"Null, Null, Null, 0, 1"
RunSQLServerStoredProcedure Sql

End Sub

Replace the fixed parameters only when other calls to the same SP need them.

This is in the beginning an annoying task, but will save time later.

After that you have a single point where you can decide to remap the old
calling syntax to the new one and/or if necessary introduce a new
wrapper with the new syntax.

mfG
--> stefan <--

 

[Klatuu]

Brilliant Idea, Stefan!