SQL Connection string with VBA (1 Viewer)

[dougancil]

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)

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 form2 code

Private Sub Command2_Click()
Option Compare Database
Dim batchid As String


sConn = "Provider='SQLOLEDB';Data Source='xxxxx';" & _
             "Initial Catalog='xxxxx';"
             
Set sConn = New adodb.Connection
sConn.Open

On Error GoTo DateStampError 'Error reporting on DateStamp code
sqlStmt = "Insert into payroll (timestamp, batchid) values (date(), batchid)"
Set rs = CurrentDb().OpenRecordset(sqlStmt)

With rs
    If .RecordCount = 1 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
   
DateStampError:
 MsgBox "DateStamp code failed. " & Error$
 Resume Command2_Click_Exit
 DoCmd.Me.Close
End Sub

The main problem that I'm having at this point is writing the sql connection string. I've never written one in vba. Also passing the data from the access table to a record set and then to the sql is the other issue.

Can someone please assist?

Thank you

Doug

 

[MarkK]

Check out www.connectionstrings.com. They have a lot of, ..., well, ..., you know.

 

[dougancil]

Lagbolt,

here's what I found on another forum for my connection string:

Set cnn = New ADODB.Connection
 cnn.Open "Provider=sqloledb;" & _
          "Data Source=xxxxx;" & _
          "Initial Catalog=xxxxx;" & _
          "User Id=xxxxx;" & _
          "Password=xxxxx"

I'm assuming that will work as I haven't tried it yet, but all of the necessary components are there. My next question is, how do I move the data that's in my access database, put that in my recordset that is going to be posted to my sql database, and lastly how do I post that dataset (all information) to the database?

 

[MarkK]

I think Access has a built-in capability to Upsize to SQL Server. I'd look into that first. What version are you using? I have 2007 and there's a SQL Server button in the Move Data group on the Database Tools tab on the main Ribbon.
And I'm not sure a recordset is the way to go. I'd want to use update queries. But check out the upsizing business first.
Cheers,

 

[dougancil]

Lagbolt,

Sorry I realize now that I worded that question incorrectly. What I'm trying to do is have the data that's in the access table (in my case the table is called 6_1_execupay) stored as a recordset so that i can post it to my sql table and update with the other procedures that I need to do (in my original post.)

 

[MarkK]

OK, there are a lot of problems here. I've added some notes in green to get started.

Private Sub Command2_Click()
Option Compare Database [COLOR="Green"]'this should be at the top of the module, not in the procedure[/COLOR]
Dim batchid As String

[COLOR="Green"]'where is sConn declared?  Is it a string...[/COLOR]
sConn = "Provider='SQLOLEDB';Data Source='xxxxx';" & _
             "Initial Catalog='xxxxx';"
[COLOR="Green"]'...or a connection?[/COLOR]
Set sConn = New adodb.Connection
[COLOR="Green"]'you open this connection and never close it or use anywhere[/COLOR]
sConn.Open

On Error GoTo DateStampError 'Error reporting on DateStamp code
[COLOR="Green"]'this statement is an action query...[/COLOR]
sqlStmt = "Insert into payroll (timestamp, batchid) values (date(), batchid)"
[COLOR="Green"]'and can't be used to open a recordset[/COLOR]
Set rs = CurrentDb().OpenRecordset(sqlStmt)

there are a few things I noticed that'll need to be handled.
Cheers,

 

[dougancil]

Lagbolt,

I've changed my code to this:

Option Compare Database
Private Sub Command2_Click()
Dim batchid As String
Dim cnn As String

Set cnn = New ADODB.Connection
 cnn.Open "Provider=sqloledb;" & _
          "Data Source=xxxxx;" & _
          "Initial Catalog=xxxxx;" & _
          "User Id=xxxxx;" & _
          "Password=xxxxx"

SelectCase Me.Frame7.Value
 Case 1
   batchid = "='1'"
 Case 2
   batchid = "='2'"
 Case 3
   batchid = "='3'"
   End Select
   
Set rs = CurrentDb().OpenRecordset(sqlStmt)
sqlStmt = "Insert into payroll (timestamp, batchid) values (date(), batchid)"
On Error GoTo DateStampError 'Error reporting on DateStamp code

With rs
    If .RecordCount = 1 Then
        .AddNew 'For first time use before a record added
        .Fields("timestamp") = Date
        .Update
    Else
        .MoveFirst
        .Edit
        .Fields("timestamp") = Date
        .Update
    End If
End With
rs.Close
cnn.Close
Set rs = Nothing

   Command2_Click_Exit
   
DateStampError:
 MsgBox "DateStamp code failed. " & Error$
 Resume Command2_Click_Exit
 DoCmd.Me.Close
End Sub

Does that look any better?

 

[MarkK]

What happens when you run it?
A few problems:

• You open the recordset before you've assigned a value to SqlStmt.
• You open the connection but you never use it anywhere.
• I don't believe you can open a recordset on an insert query.

Cheers,

 

[dougancil]

I get an error on this line of code:

Dim rs As ADODB.Recordset

"User defined type not defined"

and can you give me a good example of how to open a recordset properly with a sql connection string?

 

[G37Sam]

You're missing a library then

Do you get a dropdown when you place the "." after ADODB?

 

[dougancil]

G37Sam,

I don't get a dropdown after I put the "." in ADODB. What do I have to do to add that library?

 

[G37Sam]

In VBA, go to Tools.. References.. And make sure Microsoft ADODB is checked

 

[dougancil]

G37Sam,

Seems that the best way to do what I'm needing is to link the two tables together (which I've never done before) and I found this code for doing so:

DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;Driver={SQL Server};Server=xxxxx;Database=MDR;" _
Uid=xxxxx;Pwd=xxxxx",acTable,"Payroll","6_1_Execupay"

and I wrote a small subroutine for that but I get an error stating that the "end expression is expected" and I'm not sure what I'm missing. Can you give me any insight into that?

Thank you

Doug