Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2011
    Posts
    8

    Unanswered: SQL connection string in VBA

    I have an application that I'm trying to finish in VBA, and as I've never written in it, it's a bit challenging to me. I've written some in VB but the syntax isn't the same between the two for some things. Essentially, here's the logic (or lack of) that I'm trying to accomplish with this task. This is a payroll program that was written by someone who is no longer with our organization. I am trying to finalize this, and realized that when reviewing it for finalization, that it was missing some components. Currently there is no way to create an audit trail for this program (which is why the timestamp is now coming into play.) Also, there is a value (the option group) that wasn't in the original version of this app. As far as the other two buttons on the first form, those perform calculations that end with a result when a user presses button #3 on the main form. What I'd like that 3rd button to also do is to open the second form, post the data from the results on the first form, show the option group with a submit button, when the user presses the submit button on form2, it appends a timestamp and the options group selection to the same table that the results of form1 were posted to.

    Step by step ... this is how the program should work:

    1. User presses buttons 1, 2 and 3 on main form and gets a "sum" for the payroll for a specific time frame.
    2. When the user presses the last button on main form, the form closes, the sum of the data is posted to a SQL database and a second form is opened.
    3. The second form is a option group with 3 options, 1, 2, or 3 and a submit button.
    4. The user chooses an option and presses the submit button on the second form, which then posts the option group result AND the timestamp to the SQL table. (the same SQL table where the information from the main form is posted)
    5. Form 2 closes.

    here is my code thus far: (main form code)
    Code:
    Option Compare Database
    
    '------------------------------------------------------------
    ' Command0_Click
    '
    '------------------------------------------------------------
    Private Sub Command0_Click()
    On Error GoTo Command0_Click_Err
    
        DoCmd.OpenQuery "1_LogInScratchPad", acViewNormal, acEdit
        DoCmd.OpenTable "2_ScratchPad", acViewNormal, acEdit
    
    
    Command0_Click_Exit:
        Exit Sub
    
    Command0_Click_Err:
        MsgBox Error$
        Resume Command0_Click_Exit
    
    End Sub
    
    '------------------------------------------------------------
    ' Command1_Click
    '
    '------------------------------------------------------------
    Private Sub Command1_Click()
    On Error GoTo Command1_Click_Err
    
    
        DoCmd.OpenQuery "2_ExceptionsScratchPad", acViewNormal, acEdit
        DoCmd.OpenTable "3_ExcepScratchPad", acViewNormal, acEdit
    
    
    Command1_Click_Exit:
        Exit Sub
    
    Command1_Click_Err:
        MsgBox Error$
        Resume Command1_Click_Exit
    
    End Sub
    
    '------------------------------------------------------------
    ' Command2_Click
    '
    '------------------------------------------------------------
    Private Sub Command2_Click()
    
    Dim rs As DAO.Recordset
    Dim sqlStmt As String
    
    
    On Error GoTo Command2_Click_Err    'Error reporting on query code
    
        DoCmd.OpenQuery "DeleteExecupayTable", acViewNormal, acEdit
        DoCmd.OpenQuery "5_ExcepToExcupay", acViewNormal, acEdit
        DoCmd.OpenQuery "7_SumToExecupay", acViewNormal, acEdit
        DoCmd.OpenTable "6_1_Execupay", acViewNormal, acReadOnly
        DoCmd.OpenForm "Form2", acNormal
        
    Command2_Click_Exit:
        Exit Sub
    
    Command2_Click_Err:
     MsgBox "Open Query code failed. " & Error$
     Resume Command2_Click_Exit
    
    DateStampError:
     MsgBox "DateStamp code failed. " & Error$
     Resume Command2_Click_Exit
    
    
    End Sub
    and here's the code for form 2:

    Code:
    Private Sub Command2_Click()
    Option Compare Database
    Dim batchid As String
    Dim sqlStmt As String
    
    
    sConn = "Provider='SQLOLEDB';Data Source='10.2.1.41';" & _
                 "Initial Catalog='MDR';"
                 
    Set sConn = New ADODB.Connection
    sConn.Open
    
    On Error GoTo DateStampError 'Error reporting on DateStamp code
    sqlStmt = "Insert Into fldDate FROM [Payroll]"
    Set rs = db.OpenRecordset("Payroll", dbOpenDynaset)
    
    With rs
        If .RecordCount = 0 Then
            .AddNew 'For first time use before a record added
            .Fields("fldDate") = Date
            .Update
        Else
            .MoveFirst
            .Edit
            .Fields("fldDate") = Date
            .Update
        End If
    End With
    rs.Close
    Set rs = Nothing
    
     SelectCase Me.Frame7.Value
     Case 1
       batchid = "='1'"
     Case 2
       batchid = "='2'"
     Case 3
       batchid = "='3'"
       End Select
       Command2_Click_Exit
       Exit Sub
       
    DateStampError:
     MsgBox "DateStamp code failed. " & Error$
     Resume Command2_Click_Exit
    End Sub
    Can someone please assist.

    Thank you

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    What's the problem you're encountering?

    From first glance:

    Code:
    Set rs = db.OpenRecordset("Payroll", dbOpenDynaset)
    
    With rs
        If .RecordCount = 0 Then
    Will always evaluate to true, as the recordset will always open to the first record, being 0. The only time it won't is if the recordset is empty, in which case it'll throw an out of bounds exception.

    If you want to find if a recordset is empty, then you should use:

    Code:
    If ((rs.BOF) And (rs.EOF)) Then
        MsgBox "The Recordset is empty."
    Else
        MsgBox "The Recordset has some content."
    End If
    If you're looking to find how many records there are in the given recordset, then you can expand on the above:

    Code:
    If ((rs.BOF) And (rs.EOF)) Then
        MsgBox "The Recordset is empty."
    Else
        Dim aLng As Long
    
        rs.MoveLast
        aLng = rs.RecordCount
        rs.MoveFirst
    
        MsgBox "The Recordset has some content." & vbCrLf & _
                Str(aLng) & " to be precise."
    End If
    Without knowing what problem you're encountering with your code though, this is just a wild assumption.
    Looking for the perfect beer...

  3. #3
    Join Date
    Feb 2011
    Posts
    8
    Kez,

    The problem I'm encountering is that I don't know how to take what's in an access table, and post that information to a sql table. This application creates a table called 6_1_Execupay that I need to store as a dataset and then pass that along to my sql server, let a user choose either 1, 2 or 3 on the last form, and when the user presses submit, that it saves that choice as whatever they picked, and then updates my sql database with the current timestamp. I've never written in vba before so the syntax is a bit new to me. I have a bit more experience in vb.

    As far as this bit of code goes:
    Code:
    Set rs = db.OpenRecordset("Payroll", dbOpenDynaset)
    
    With rs
        If .RecordCount = 0 Then
    what I'm looking to do there (again this code was suggested to me) is to append a record with the current date and time. I won't need to check if there was a previous record, because after the first time this application is ran, there will always be a previous record. I hope that makes more sense.

    Thank you

    Doug

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •