Posting a dataset to a SQL table using VBA

Joined
Apr 16, 2009
Messages
4
Reaction score
0
I have an application that I'm trying to do the following 3 things on.

When a user presses a button, another form is opened with a Union Group giving the user 3 options (1, 2, or 3) and when the user submits their choice, the form is closed and:

1. A SQL table is appended with the dataset from the application
2. A timestamp is inserted into the table
3. the values of the Union group are added to a field in the table.

I'm new to VBA and don't know how to do this. My code for form1 is as follows:
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()
[B]DoCmd.OpenForm "Form_Form1", acNormal, acEdit[/B]
On Error GoTo Command2_Click_Err


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
    
Command2_Click_Exit:
    Exit Sub

Command2_Click_Err:
 MsgBox "Open Query code failed. " & Error$
 Resume Command2_Click_Exit



End Sub
as you can see the only thing that's done here is to open my other form (bold text.)

Here's my code for form 2:

Code:
Option Compare Database
Dim batchid As String
Private Sub Submit_Click()
Dim cnt As ADODB.Connection

Dim rs As DAO.Recordset
Dim sqlStmt As String

 Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
    "Persist Security Info=False;" & _
    "Initial Catalog=Payroll;" & _
    "Data Source=10.2.1.41"
     'where Payroll is SQL Database & 10.2.1.41 is SQL Server
     
     On Error GoTo DateStampError       'Error reporting on DateStamp code
sqlStmt = "SELECT fldDate FROM [tblDateStamp]"
Set rs = CurrentDb().OpenRecordset(sqlStmt)

Set cnt = New ADODB.Connection
     
With cnt
        .Open stADO
        .CommandTimeout = 0
        Set rst = .Execute(stSQL)
    End With
    
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

DateStampError:
 MsgBox "DateStamp code failed. " & Error$
 Resume Submit_Click_Exit
 
End Sub
Private Sub Frame7_AfterUpdate()
 SelectCase Me.Frame7.Value
 Case 1
   batchid = "='1'"
 Case 2
   batchid = "='2'"
 Case 3
   batchid = "='3'"
   End Select
   
End Sub

This is what I'm not sure of. I just started using VBA two weeks ago and would like to be able to do those 3 things mentioned at the start of this post. Can anyone offer assistance?

Thank you

Doug
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Module 15
Customise Error Message 1
VBA Code 1
Action Query Message 8
Frozen Form after a procedure call 1
1st VBA sequence 6
Getting update query to work 1
exporting to a pre-formulated excel sheet 4

Top