Beyond Excel: VBA and Database Manipulation

May 6 2010   8:34PM GMT

Building a Library of Routines for Updating – #4

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

We are just about finished with adding support routines for updating, inserting, and deleting records in a database.  As a reminder, these routines should never need modification. 

The first routine should go in modSQL.  It is used to create a connection object that can be reused.  This can greatly speed processing be eliminating constently opening and closing database connections for each insert, update, or delete.

The second routine finds the last position of a character in a string.  This is used to strip table/file names from path strings.  It can have other applications and thus, should be place in modGeneral.

Function SQLConnection(cn As ADODB.Connection, _
                       sConnect As String) As Boolean
'   Description:Create a connection to the database
'               By externalizing this, a connection can be reused
'   Parameters: cn          an ADODB connection object
'               sConnect    Connection string
'   Example:    bResult = _
                    SQLConnection(cn, _
'                       "Driver={Microsoft Access Driver (*.mdb)};" & _
'                       "DBQ=c:\Northwind.mdb")
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    On Error GoTo ErrHandler
    SQLConnection = Failure    'Assume Something went wrong
   
    If cn Is Nothing Then
        Set cn = New ADODB.Connection
        cn.Properties("Prompt") = adPromptComplete
        cn.Open sConnect, "", ""
    End If
   
    SQLConnection = Success
   
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "SQLConnection - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function
 
Function Chars_Last_Position(sCharacter As String, _
                             sString As String) As Integer
'   Description:Returns position of last occurance of a character
'               in a string
'   Parameters: sCharacter  Character to find
'               sString     String to search for character
'   Example:    sTable = Right(sPath, len(sPath) - _
'                           Chars_Last_Position("/", sPath))
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    On Error GoTo ErrHandler
    Dim i As Integer
   
    Chars_Last_Position = 0         'Assume not found
   
    i = 0
    Do
        i = InStr(i + 1, sString, sCharacter, vbTextCompare)
        If i > 0 Then Chars_Last_Position = i
    Loop Until i = 0
   
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Settings - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function
 

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: