How to clean up my VB code?

[porsesh]

Hi friends,
I am new in this forum and I am posting my first question I want to clean up my code and try not to repeat some code that are exactly the same...
For example:
If you take a look on my code you will see several place that I have same code like:

strSql = "this part is different"
rst.Open strSql, Conn, adOpenKeyset, adLockOptimistic
(this part Is different) = rst(0)
rst.Close


So how can I clean up the code and basically make it more look like object oriented style?

Please let me know if still is not clear.

Many thanks,
Porsesh

My whole code
==============
Option Compare Database
Option Explicit

Public Function Frequency( _
ByVal strColumnName As String, _
ByVal strTableName As String, _
ByVal nBin_Number As Integer, _
ByRef strRetOutputTbl As String _
) As Boolean

Dim dblAverage As Double
Dim dblStdDeviation As Double
Dim DblDx As Double
Dim dblN As Double
Dim DblXlow As Double
Dim DblXhigh As Double
Dim Dblx1 As Double
Dim Dblx2 As Double
Dim nI As Integer
Dim strMsg As String
Dim strSql As String
Dim strValue As String
Dim sngValue As Single

Dim rst As ADODB.Recordset
Dim Conn As ADODB.Connection

Set Conn = New ADODB.Connection
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\TOF\DrDalton\Database\MassCalibration.mdb;" & _
"User Id=admin;" & _
"Password="


'Generate the table name and check the existence with AcceessObject
Dim strOutputTbl As String
strOutputTbl = strTableName & "_hist_" & strColumnName
'also return the name of this output table thru the ByRef argument
strRetOutputTbl = strOutputTbl
Dim tbl As AccessObject
Dim blnTblFound As Boolean
blnTblFound = False
For Each tbl In CurrentData.AllTables
If UCase(tbl.Name) = UCase(strOutputTbl) Then
blnTblFound = True
Exit For
End If
Next

If blnTblFound = True Then
'Means table exists. so, delete all data in table
strSql = "DELETE * FROM " & strOutputTbl
Conn.Execute strSql
Else
'Table does not exist. create new table
Dim strCreateSQL As String
strCreateSQL = "CREATE TABLE " & strOutputTbl & " (" & _
"binNo integer CONSTRAINT pKey Primary Key, " & _
"Xc Text(50), " & _
"[cnt] INTEGER)"
Conn.Execute strCreateSQL
End If

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient

'Get the Average from StrColumnName
strSql = "SELECT AVG(" & strColumnName & &quot AS dblAverage FROM " & strTableName & ";"
rst.Open strSql, Conn, adOpenKeyset, adLockOptimistic
dblAverage = rst(0)
rst.Close

'Get the Standard Deviation from StrColumnName
strSql = "SELECT StDev(" & strColumnName & &quot AS dblStdDeviation FROM " & strTableName & ";"
rst.Open strSql, Conn, adOpenKeyset, adLockOptimistic
dblStdDeviation = rst(0)
rst.Close

'Determine DblXlow
DblXlow = dblAverage - 3 * dblStdDeviation
'Determine DblXhigh
DblXhigh = dblAverage + 3 * dblStdDeviation
'Determine DblDx
DblDx = (DblXhigh - DblXlow) / nBin_Number

'Run the query for UNDER Region
nI = 0
strSql = &quot;SELECT count(&quot; & strColumnName & &quot AS dblN FROM &quot; & strTableName & &quot; where &quot; & strColumnName & &quot; < &quot; & DblXlow
rst.Open strSql, Conn, adOpenKeyset, adLockOptimistic, adCmdText

If Not rst.EOF Then
dblN = rst(0)
End If

rst.Close

'populate strOutputTbl table with data for nI= 0
strSql = &quot;INSERT INTO &quot; & strOutputTbl & &quot; VALUES (&quot; & nI & &quot;, 'Under' , &quot; & dblN & &quot&quot;
Conn.Execute strSql

For nI = 1 To nBin_Number - 1

'Determine Dblx1 and Dblx2
Dblx1 = DblXlow + nI * DblDx
Dblx2 = DblXlow + (nI + 1) * DblDx

'Run the query for the Region between UNDER and OVER
strSql = &quot;SELECT count(&quot; & strColumnName & &quot AS dblN FROM &quot; & strTableName & &quot; where &quot; & strColumnName & &quot; >= &quot; & Dblx1 & &quot; And &quot; & strColumnName & &quot; < &quot; & Dblx2

rst.Open strSql, Conn, adOpenKeyset, adLockOptimistic, adCmdText

If Not rst.EOF Then
dblN = rst(0)
End If

rst.Close

sngValue = DblXlow + (nI + 0.5) * DblDx
strValue = MyRound(sngValue, 2)

'populate strOutputTbl table with data for nI= 1 to 4
strSql = &quot;INSERT INTO &quot; & strOutputTbl & &quot; VALUES (&quot; & nI & &quot;, '&quot; & strValue & &quot;', &quot; & dblN & &quot&quot;
Conn.Execute strSql

Next nI

'Run the query for OVER Region
nI = nBin_Number
strSql = &quot;SELECT count(&quot; & strColumnName & &quot AS dblN FROM &quot; & strTableName & &quot; where &quot; & strColumnName & &quot; >= &quot; & DblXhigh
rst.Open strSql, Conn, adOpenKeyset, adLockOptimistic, adCmdText

If Not rst.EOF Then
dblN = rst(0)
End If

rst.Close

'populate strOutputTbl table with data for nI= 5
strSql = &quot;INSERT INTO &quot; & strOutputTbl & &quot; VALUES (&quot; & nI & &quot;, 'Over' , &quot; & dblN & &quot&quot;
Conn.Execute strSql

Set rst = Nothing
Conn.Close
Set Conn = Nothing

End Function

 

[jonybd]

I think u asked !!
Press: Ctrl + F
in Replace dialog box!

Find &quot;strSql = &quot;this part is different&quot;
Replace &quot; 'strSql = &quot;this part is different&quot;

!!

 

[Suraklyn]

Just off hand, you should be able to make Subs and Functions for your Select and Insert statements.

For example...

'Get the Average from StrColumnName
dblAverage = queryDB("SELECT AVG(" & strColumnName & ") AS dblAverage FROM " & strTableName & ";")

'Get the Standard Deviation from StrColumnName
dblStdDeviation = queryDB("SELECT StDev(" & strColumnName & ") AS dblStdDeviation FROM " & strTableName & ";")

Private Function queryDB (strQuery As String) As Double

   rst.Open strQuery, Conn, adOpenKeyset, adLockOptimistic

   queryDB = rst(0)

   rst.Close

End Function

--
Jonathan