Connecting to a SQL database or Linking

[dougancil]

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: (main form)

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 my second form:

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 = "Update 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
 

and I've even tried to link them together with this code:

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

but I get an error of "end of expression expected" but I'm not sure what I'm missing.

Can someone please assist?

Thank you

Doug

[patjones]

There's definitely a lot to absorb in your post, and as a result it's hard for me to see relevance of all of it to your ultimate question. But I DO appreciate that you've given such a comprehensive overview.

Let me start by first asking what you mean by "linking them together"?

Pat

[dougancil]

Well I know that you can create linked tables in access, and what I'd like to do is link the current access database (which will be a localized database) to a SQL database which will be networked.

[NeoPa]

You're still not making it clear which of the two you want as the Front End, and which the Back Doug.

[patjones]

Good morning,

I recently started connecting Access front ends to SQL Server. I'm not aware of the DoCmd.TransferDatabase method, though that's not to say that it isn't valid.

What I have done in two recent projects was to use ODBC. You can get started with this in Access 2007 by going External Data > More> ODBC> Link to the data source by creating a linked table and then clicking OK.

This method requires something called a DSN (Domain Server Name) file, which tells Access how to connect to your SQL Server database. Setting up the DSN is something I had to ask my IT division to do. Once I had the DSN though, it was a smooth process. Access connects to SQL, and you get a list of tables that you can link to. The end result is a set of linked tables that you can then reference in VBA just like any other table (for the most part, but there are some nuances of course).

It is possible to connect to SQL Server by using connection strings embedded within VBA, along lines similar to what you're trying to do. You would then manage data using pass-through queries and other constructs that I'm probably not aware of. The use of connection strings can get tricky and it often takes a bit of tweaking to get them to work properly. Also, maintaining the SQL connection from VBA will increase the amount of code that you have to manage. I've used both methods, but I do like ODBC.

Does this provide some insight for you?

Pat

[dougancil]

Neo,

The Main form is the front end and the second form is the backend. Pat, I'll try to set up the DSN for that server today, what about the rest of the code, once the tables are linked, am I able to save the access table to the SQL table, add the values from my frame and then timestamp the data?

Thank you

Doug

[patjones]

You need to create the tables on the SQL Server end, link them as I indicated previously, then INSERT the data from your current local tables into the SQL Server linked tables. For the rest of your question, this is the point where I need to understand better what you're trying to do with your code. There is a lot of clarification you need to do. In particular:

1. Where in the code for your first form is the sum of the data "posted to a SQL database..."? I see where the second form is opened (line 13), but where is the data being posted to SQL?

2. With the code in your second form, I am not clear at all on what you're trying to do. You're defining a SQL UPDATE query in the string and then trying to use methods like .AddNew and .Edit on it, which makes no sense. Instead, you should be SELECTing the records you want to work with in the string.

3. What are you doing with 'batchID'. You assign it then do nothing with it.

[dougancil]

Zepp,

The posting of the data to SQL is where I was lost. I'm new to writing in VBA and have no idea how to post that data to sql. As far as the SQL update part, that code was written for me and the requirements have since changed. That was written with the assumption that a record would not be present, but I realized that would only be true once. Lastly, with the batchId, that's supposed to be the frame value from that form, so if a user selects 1, 2 or 3, that's the batchid. I hope that clarifies things a bit more.

I think I'm going to use this method to post my data BUT a question comes up that says that I want to append this data every time and not simply overwrite it.

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

how would I go about doing that?

[patjones]

To be honest, I still can't make sense of this. What are your tables? What are your controls? What data are you trying to put in the tables?

For example, you need to say "I have text boxes named txt1, txt2, and txt3 whose values I need to add together and insert the result into a table named tblPay when the user hits command button cmdSum" and make it clear how your code is attempting to do those things.

The effectiveness of this forum is based upon people asking clear questions in very concrete terms. You're making vague references to various characteristics of your project, and I can't see how it fits together.

Like with batchID...my question still stands. What are you doing with it? You assign it a value and then end the subroutine without using it anywhere.

Please try and put together the pieces of your project in a way that will make it possible for us to answer the question.

[NeoPa]

DougAncil:
The Main form is the front end and the second form is the backend

The question was about which database type (Access or SQL Server) was which, but never mind. Pat seems to be dealing with it anyway and if he can understand what you're saying that's all that's important.

@Pat
PS I think we just cross-posted. Good luck with this. I think I've said all I can that might help.