Add new records to table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Code below works and shows results in the immediate window.
My question is how to add recordset to table and one other thing how
before adding recordset to the table would you go about editing recordset
before adding to table

Any help would be great

Private Sub cmdPreview_Click()
On Error GoTo Err_cmdPreview_Click
Dim objConn As ADODB.Connection
Dim objRST As ADODB.Recordset
Dim strSQL As String
Dim strConn As String
Dim startDate As String
Dim endDate As String
Dim StoreIPAddress As String
Dim statusMessage As String


If Me.cboStoreIP.Value & "" = "" Then
MsgBox "Must select a store to connect to", vbOKOnly + vbCritical,
"Payroll"
Exit Sub
End If

statusMessage = SysCmd(acSysCmdSetStatus, "Please wait downloading from
store " _
& Me!cboStoreIP.Column(0) & " IP Address " & Me!cboStoreIP.Column(1))

startDate1 = Me!StartDate.Value
endDate2 = Me!EndDate.Value

StoreIPAddress = Me!cboStoreIP.Value

Set objConn = CreateObject("ADODB.Connection")
Set objRST = CreateObject("ADODB.RecordSet")

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=\\" & StoreIPAddress & "\Ilsa\Data\" _
& "Ilsa.mdb;Persist Security Info=False"


strSQL = "SELECT Employee.NAME, Time_Clk.EMPLOYEE_ID, Time_Clk.IN_TIME, " _
& "Time_Clk.OUT_TIME, Hour([IN_TIME]-[OUT_TIME]) AS Expr1, " _
& "Minute([IN_TIME]-[OUT_TIME]) AS Expr2, [Expr2]/60 AS Expr3, " _
& "[Expr1]+[Expr3] AS Expr4, DateValue([IN_TIME]) AS Expr5, " _
& "TimeValue([IN_TIME]) AS Expr6, NOW() AS Expr7, NOW() AS Expr8,
Round([Expr4],2) AS Expr9 " _
& "FROM Employee RIGHT JOIN Time_Clk ON
Employee.EMPLOYEE_NUM=Time_Clk.EMPLOYEE_ID " _
& "WHERE (((Time_Clk.IN_TIME) Between #" & startDate & "# " _
& "And #" & endDate & "#+1)) " _
& "ORDER BY Time_Clk.EMPLOYEE_ID,Time_Clk.IN_TIME; "


objConn.Open (strConn)


objRST.Open strSQL, objConn, adOpenForwardOnly, adLockBatchOptimistic
objRST.MoveFirst

While Not objRST.EOF

Debug.Print objRST.Fields("Name")
Debug.Print objRST.Fields("EMPLOYEE_ID")
Debug.Print Format(objRST.Fields("Expr5"), "m/d/yyyy")
Debug.Print UCase(Format(objRST.Fields("Expr5"), "dddd"))
Debug.Print Format(objRST.Fields("Expr6"), "h:mm")
Debug.Print Format(objRST.Fields("OUT_TIME"), "h:mm")
Debug.Print objRST.Fields("Expr9")
Debug.Print Format(objRST.Fields("Expr7"), "m/d/yyyy")
Debug.Print Format(objRST.Fields("Expr8"), "m/d/yyyy")

objRST.MoveNext

Wend

objRST.Close
objConn.Close

Set objRST = Nothing
Set objConn = Nothing


statusMessage = SysCmd(acSysCmdSetStatus, " ")
MsgBox "Download successfull", vbOKOnly, "Payroll"



Exit_Err_cmdPreview_Click:
Exit Sub

Err_cmdPreview_Click:
statusMessage = SysCmd(acSysCmdSetStatus, " ")
MsgBox Err.Description, vbCritical, "Payroll"
Resume Exit_Err_cmdPreview_Click

End Sub
 
Code below works and shows results in the immediate window.
My question is how to add recordset to table and one other thing how
before adding recordset to the table would you go about editing recordset
before adding to table

Any help would be great

Private Sub cmdPreview_Click()
On Error GoTo Err_cmdPreview_Click
Dim objConn As ADODB.Connection
Dim objRST As ADODB.Recordset
Dim strSQL As String
Dim strConn As String
Dim startDate As String
Dim endDate As String
Dim StoreIPAddress As String
Dim statusMessage As String

If Me.cboStoreIP.Value & "" = "" Then
MsgBox "Must select a store to connect to", vbOKOnly + vbCritical,
"Payroll"
Exit Sub
End If

statusMessage = SysCmd(acSysCmdSetStatus, "Please wait downloading from
store " _
& Me!cboStoreIP.Column(0) & " IP Address " & Me!cboStoreIP.Column(1))

startDate1 = Me!StartDate.Value
endDate2 = Me!EndDate.Value

StoreIPAddress = Me!cboStoreIP.Value

Set objConn = CreateObject("ADODB.Connection")
Set objRST = CreateObject("ADODB.RecordSet")

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=\\" & StoreIPAddress & "\Ilsa\Data\" _
& "Ilsa.mdb;Persist Security Info=False"

strSQL = "SELECT Employee.NAME, Time_Clk.EMPLOYEE_ID, Time_Clk.IN_TIME, " _
& "Time_Clk.OUT_TIME, Hour([IN_TIME]-[OUT_TIME]) AS Expr1, " _
& "Minute([IN_TIME]-[OUT_TIME]) AS Expr2, [Expr2]/60 AS Expr3, " _
& "[Expr1]+[Expr3] AS Expr4, DateValue([IN_TIME]) AS Expr5, " _
& "TimeValue([IN_TIME]) AS Expr6, NOW() AS Expr7, NOW() AS Expr8,
Round([Expr4],2) AS Expr9 " _
& "FROM Employee RIGHT JOIN Time_Clk ON
Employee.EMPLOYEE_NUM=Time_Clk.EMPLOYEE_ID " _
& "WHERE (((Time_Clk.IN_TIME) Between #" & startDate & "# " _
& "And #" & endDate & "#+1)) " _
& "ORDER BY Time_Clk.EMPLOYEE_ID,Time_Clk.IN_TIME; "

objConn.Open (strConn)

objRST.Open strSQL, objConn, adOpenForwardOnly, adLockBatchOptimistic
objRST.MoveFirst

While Not objRST.EOF

Debug.Print objRST.Fields("Name")
Debug.Print objRST.Fields("EMPLOYEE_ID")
Debug.Print Format(objRST.Fields("Expr5"), "m/d/yyyy")
Debug.Print UCase(Format(objRST.Fields("Expr5"), "dddd"))
Debug.Print Format(objRST.Fields("Expr6"), "h:mm")
Debug.Print Format(objRST.Fields("OUT_TIME"), "h:mm")
Debug.Print objRST.Fields("Expr9")
Debug.Print Format(objRST.Fields("Expr7"), "m/d/yyyy")
Debug.Print Format(objRST.Fields("Expr8"), "m/d/yyyy")

objRST.MoveNext

Wend

objRST.Close
objConn.Close

Set objRST = Nothing
Set objConn = Nothing

statusMessage = SysCmd(acSysCmdSetStatus, " ")
MsgBox "Download successfull", vbOKOnly, "Payroll"

Exit_Err_cmdPreview_Click:
Exit Sub

Err_cmdPreview_Click:
statusMessage = SysCmd(acSysCmdSetStatus, " ")
MsgBox Err.Description, vbCritical, "Payroll"
Resume Exit_Err_cmdPreview_Click

End Sub
Click to expand...

Your question doesn't make sense. A recordset is a view of a table,
and if opened properly, will update the table too. Any changes you
make to the recordset will be reflected in the table unless you use a
read-only cursor.
If you use adLockOptimistic with adOpenForwardOnly you should get an
updateable cursor. Make changes to the recordset by using the rows
property and the Update( ) function.
 
I'm sorry Old Pro. Let me try again.
I would like to Insert the data that is pulled from store's database's
across the network to my local payroll database. I have 21 stores that I pull
timeclock reports from. I have a table with Store's IP Address and a combobox
on a form that allows me to connect to the store I want. Right now the
results from the SQL are in the immediate window. How can I get these results
into a table

Please Help


OldPro said:
Code below works and shows results in the immediate window.
My question is how to add recordset to table and one other thing how
before adding recordset to the table would you go about editing recordset
before adding to table

Any help would be great

Private Sub cmdPreview_Click()
On Error GoTo Err_cmdPreview_Click
Dim objConn As ADODB.Connection
Dim objRST As ADODB.Recordset
Dim strSQL As String
Dim strConn As String
Dim startDate As String
Dim endDate As String
Dim StoreIPAddress As String
Dim statusMessage As String

If Me.cboStoreIP.Value & "" = "" Then
MsgBox "Must select a store to connect to", vbOKOnly + vbCritical,
"Payroll"
Exit Sub
End If

statusMessage = SysCmd(acSysCmdSetStatus, "Please wait downloading from
store " _
& Me!cboStoreIP.Column(0) & " IP Address " & Me!cboStoreIP.Column(1))

startDate1 = Me!StartDate.Value
endDate2 = Me!EndDate.Value

StoreIPAddress = Me!cboStoreIP.Value

Set objConn = CreateObject("ADODB.Connection")
Set objRST = CreateObject("ADODB.RecordSet")

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=\\" & StoreIPAddress & "\Ilsa\Data\" _
& "Ilsa.mdb;Persist Security Info=False"

strSQL = "SELECT Employee.NAME, Time_Clk.EMPLOYEE_ID, Time_Clk.IN_TIME, " _
& "Time_Clk.OUT_TIME, Hour([IN_TIME]-[OUT_TIME]) AS Expr1, " _
& "Minute([IN_TIME]-[OUT_TIME]) AS Expr2, [Expr2]/60 AS Expr3, " _
& "[Expr1]+[Expr3] AS Expr4, DateValue([IN_TIME]) AS Expr5, " _
& "TimeValue([IN_TIME]) AS Expr6, NOW() AS Expr7, NOW() AS Expr8,
Round([Expr4],2) AS Expr9 " _
& "FROM Employee RIGHT JOIN Time_Clk ON
Employee.EMPLOYEE_NUM=Time_Clk.EMPLOYEE_ID " _
& "WHERE (((Time_Clk.IN_TIME) Between #" & startDate & "# " _
& "And #" & endDate & "#+1)) " _
& "ORDER BY Time_Clk.EMPLOYEE_ID,Time_Clk.IN_TIME; "

objConn.Open (strConn)

objRST.Open strSQL, objConn, adOpenForwardOnly, adLockBatchOptimistic
objRST.MoveFirst

While Not objRST.EOF

Debug.Print objRST.Fields("Name")
Debug.Print objRST.Fields("EMPLOYEE_ID")
Debug.Print Format(objRST.Fields("Expr5"), "m/d/yyyy")
Debug.Print UCase(Format(objRST.Fields("Expr5"), "dddd"))
Debug.Print Format(objRST.Fields("Expr6"), "h:mm")
Debug.Print Format(objRST.Fields("OUT_TIME"), "h:mm")
Debug.Print objRST.Fields("Expr9")
Debug.Print Format(objRST.Fields("Expr7"), "m/d/yyyy")
Debug.Print Format(objRST.Fields("Expr8"), "m/d/yyyy")

objRST.MoveNext

Wend

objRST.Close
objConn.Close

Set objRST = Nothing
Set objConn = Nothing

statusMessage = SysCmd(acSysCmdSetStatus, " ")
MsgBox "Download successfull", vbOKOnly, "Payroll"

Exit_Err_cmdPreview_Click:
Exit Sub

Err_cmdPreview_Click:
statusMessage = SysCmd(acSysCmdSetStatus, " ")
MsgBox Err.Description, vbCritical, "Payroll"
Resume Exit_Err_cmdPreview_Click

End Sub
Click to expand...

Your question doesn't make sense. A recordset is a view of a table,
and if opened properly, will update the table too. Any changes you
make to the recordset will be reflected in the table unless you use a
read-only cursor.
If you use adLockOptimistic with adOpenForwardOnly you should get an
updateable cursor. Make changes to the recordset by using the rows
property and the Update( ) function.
Click to expand...
 
I'm sorry Old Pro. Let me try again.
I would like to Insert the data that is pulled from store's database's
across the network to my local payroll database. I have 21 stores that I pull
timeclock reports from. I have a table with Store's IP Address and a combobox
on a form that allows me to connect to the store I want. Right now the
results from the SQL are in the immediate window. How can I get these results
into a table

Please Help

OldPro said:
Code below works and shows results in the immediate window.
My question is how to add recordset to table and one other thing how
before adding recordset to the table would you go about editing recordset
before adding to table
Any help would be great
Private Sub cmdPreview_Click()
On Error GoTo Err_cmdPreview_Click
Dim objConn As ADODB.Connection
Dim objRST As ADODB.Recordset
Dim strSQL As String
Dim strConn As String
Dim startDate As String
Dim endDate As String
Dim StoreIPAddress As String
Dim statusMessage As String
If Me.cboStoreIP.Value & "" = "" Then
MsgBox "Must select a store to connect to", vbOKOnly + vbCritical,
"Payroll"
Exit Sub
End If
statusMessage = SysCmd(acSysCmdSetStatus, "Please wait downloading from
store " _
& Me!cboStoreIP.Column(0) & " IP Address " & Me!cboStoreIP.Column(1))
startDate1 = Me!StartDate.Value
endDate2 = Me!EndDate.Value
StoreIPAddress = Me!cboStoreIP.Value
Set objConn = CreateObject("ADODB.Connection")
Set objRST = CreateObject("ADODB.RecordSet")
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=\\" & StoreIPAddress & "\Ilsa\Data\" _
& "Ilsa.mdb;Persist Security Info=False"
strSQL = "SELECT Employee.NAME, Time_Clk.EMPLOYEE_ID, Time_Clk.IN_TIME, " _
& "Time_Clk.OUT_TIME, Hour([IN_TIME]-[OUT_TIME]) AS Expr1, " _
& "Minute([IN_TIME]-[OUT_TIME]) AS Expr2, [Expr2]/60 AS Expr3, " _
& "[Expr1]+[Expr3] AS Expr4, DateValue([IN_TIME]) AS Expr5, " _
& "TimeValue([IN_TIME]) AS Expr6, NOW() AS Expr7, NOW() AS Expr8,
Round([Expr4],2) AS Expr9 " _
& "FROM Employee RIGHT JOIN Time_Clk ON
Employee.EMPLOYEE_NUM=Time_Clk.EMPLOYEE_ID " _
& "WHERE (((Time_Clk.IN_TIME) Between #" & startDate & "# " _
& "And #" & endDate & "#+1)) " _
& "ORDER BY Time_Clk.EMPLOYEE_ID,Time_Clk.IN_TIME; "
objConn.Open (strConn)
objRST.Open strSQL, objConn, adOpenForwardOnly, adLockBatchOptimistic
objRST.MoveFirst
While Not objRST.EOF
Debug.Print objRST.Fields("Name")
Debug.Print objRST.Fields("EMPLOYEE_ID")
Debug.Print Format(objRST.Fields("Expr5"), "m/d/yyyy")
Debug.Print UCase(Format(objRST.Fields("Expr5"), "dddd"))
Debug.Print Format(objRST.Fields("Expr6"), "h:mm")
Debug.Print Format(objRST.Fields("OUT_TIME"), "h:mm")
Debug.Print objRST.Fields("Expr9")
Debug.Print Format(objRST.Fields("Expr7"), "m/d/yyyy")
Debug.Print Format(objRST.Fields("Expr8"), "m/d/yyyy")
objRST.MoveNext
Wend
objRST.Close
objConn.Close
Set objRST = Nothing
Set objConn = Nothing
statusMessage = SysCmd(acSysCmdSetStatus, " ")
MsgBox "Download successfull", vbOKOnly, "Payroll"
Exit_Err_cmdPreview_Click:
Exit Sub
Err_cmdPreview_Click:
statusMessage = SysCmd(acSysCmdSetStatus, " ")
MsgBox Err.Description, vbCritical, "Payroll"
Resume Exit_Err_cmdPreview_Click
End Sub
Click to expand...
Click to expand...
Your question doesn't make sense. A recordset is a view of a table,
and if opened properly, will update the table too. Any changes you
make to the recordset will be reflected in the table unless you use a
read-only cursor.
If you use adLockOptimistic with adOpenForwardOnly you should get an
updateable cursor. Make changes to the recordset by using the rows
property and the Update( ) function.
Click to expand...
Click to expand...

just turn the select query into an append query. If you really want
to do it in code, use a Command Object and point to a stored procedure
that does the append, or just execute the sql statement. No
recordsets required.
 
Back
Top