×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

SQL Connection String in VBA

SQL Connection String in VBA

SQL Connection String in VBA

(OP)
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. I'll go back through my code today and clean it up. I hope that that makes more sense now as to what I'm trying to achieve.

 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:

CODE

Option Compare Database
Dim batchid As String


Dim sConn As String
sConn = "Provider='SQLOLEDB';Data Source='xxxxx';" & _
             "Initial Catalog='xxxxx';"
             
Set sConn = New ADODB.Connection
.Open

On Error GoTo DateStampError 'Error reporting on DateStamp code
sqlStmt = "APPEND timestamp FROM [Payroll]"
Set rs = CurrentDb().OpenRecordset(sqlStmt)

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
   
DateStampError:
 MsgBox "DateStamp code failed. " & Error$
 Resume Command2_Click_Exit
End Sub
I'm not familiar with how to open a dataconnection string in VBA and also will this code fulfill my other requirements?

 Thank you

 Doug

RE: SQL Connection String in VBA

Check out the geeks at Tek-Tips.com a sister to this site...  They know their stuff.

RE: SQL Connection String in VBA

The SQL connection string depends on the database you are trying to connect to.  The connection string for Oracle is different from that of SQL Server or Access.  What type of database are you connecting to?

RE: SQL Connection String in VBA

(OP)
XWB,

I'm trying to connect to a sql server. I'm also using Access 2007 fyi.

RE: SQL Connection String in VBA

Hi dougancil,

Check out this site: http://www.connectionstrings.com/

chances are you'll find what you need in there - lot's of examples and how to's.

HTH
Todd

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources