Very lost

G

Guest

I'm very lost and confused to the suggestions that have been offered to me.
Let me try this one more time.

1.)The code below gets data from other databases.
2.)I have StoreIPAddress string to connect to different database locations
3.)I have a Select Query to query up payroll hours.
4.)I need to add these records to a database on my computer

Where / how do I reference the database / table to add the records to




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 payroll
hours from " _
& Me!cboStoreIP.Column(0) & " IPAddress " & Me!cboStoreIP.Column(1))

StartDate = Me!StartDate.Value
EndDate = 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 UCase(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
 
A

Alex Dybenko

Hi,
you can open one more recordset on destination table and add there records
instead of Debug.Print'ing them:

While Not objRST.EOF
rstNew.AddNew
rstNew("Name")= UCase(objRST.Fields("Name"))
...
rstNew.Update


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Mike said:
I'm very lost and confused to the suggestions that have been offered to
me.
Let me try this one more time.

1.)The code below gets data from other databases.
2.)I have StoreIPAddress string to connect to different database locations
3.)I have a Select Query to query up payroll hours.
4.)I need to add these records to a database on my computer

Where / how do I reference the database / table to add the records to




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 payroll
hours from " _
& Me!cboStoreIP.Column(0) & " IPAddress " & Me!cboStoreIP.Column(1))

StartDate = Me!StartDate.Value
EndDate = 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 UCase(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...
 
G

Guest

Alex
You make it sound easy. I tried and all I get are X Errors!!!
I have added these new var to code what else am I missing
I am use to working with Excel, Access seems to be a whole new ball game

Dim rstConn As ADODB.Connection
Dim newRST As ADODB.Recordset

Set rstConn = CreateObject("ADODB.Connection")
Set newRST = CreateObject("ADODB.RecordSet")

Alex Dybenko said:
Hi,
you can open one more recordset on destination table and add there records
instead of Debug.Print'ing them:

While Not objRST.EOF
rstNew.AddNew
rstNew("Name")= UCase(objRST.Fields("Name"))
...
rstNew.Update


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Mike said:
I'm very lost and confused to the suggestions that have been offered to
me.
Let me try this one more time.

1.)The code below gets data from other databases.
2.)I have StoreIPAddress string to connect to different database locations
3.)I have a Select Query to query up payroll hours.
4.)I need to add these records to a database on my computer

Where / how do I reference the database / table to add the records to




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 payroll
hours from " _
& Me!cboStoreIP.Column(0) & " IPAddress " & Me!cboStoreIP.Column(1))

StartDate = Me!StartDate.Value
EndDate = 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 UCase(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...
 
G

Guest

This is what I have tried but no luck
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 StoreIPAddress As String
Dim statusMessage As String
Dim StartDate As String
Dim EndDate As String

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset


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 payroll
hours from " _
& Me!cboStoreIP.Column(0) & " IPAddress " & Me!cboStoreIP.Column(1))

StartDate = Me!StartDate.Value
EndDate = 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"

Set rs = CreateObject("ADODB.RecordSet")
Set cn = CreateObject("ADODB.Connection")

cn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "C:\Documents and Settings\Mike Jones\Desktop\" _
& "Payroll_1.2.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)

rs.Open "tablePyrollHours", cn, adOpenKeyset, adLockOptimistic
objRST.Open strSQL, objConn, adOpenForwardOnly, adLockBatchOptimistic
objRST.MoveFirst

While Not objRST.EOF
rs.AddNew
'Debug.Print objRST.Fields("Name")
rs("EmployeeID") = 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")

rs.Update
objRST.MoveNext

Wend

Alex Dybenko said:
Hi,
you can open one more recordset on destination table and add there records
instead of Debug.Print'ing them:

While Not objRST.EOF
rstNew.AddNew
rstNew("Name")= UCase(objRST.Fields("Name"))
...
rstNew.Update


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Mike said:
I'm very lost and confused to the suggestions that have been offered to
me.
Let me try this one more time.

1.)The code below gets data from other databases.
2.)I have StoreIPAddress string to connect to different database locations
3.)I have a Select Query to query up payroll hours.
4.)I need to add these records to a database on my computer

Where / how do I reference the database / table to add the records to




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 payroll
hours from " _
& Me!cboStoreIP.Column(0) & " IPAddress " & Me!cboStoreIP.Column(1))

StartDate = Me!StartDate.Value
EndDate = 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 UCase(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...
 
A

Alex Dybenko

Hi,
so do what errors do you get?
in general code looks ok, the only thing that you need to copy other field
like EmployeeID

rs("EmployeeID") = objRST.Fields("EMPLOYEE_ID")
rs("Name") = objRST.Fields("Name")

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com



(e-mail address removed)...
This is what I have tried but no luck
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 StoreIPAddress As String
Dim statusMessage As String
Dim StartDate As String
Dim EndDate As String

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset


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 payroll
hours from " _
& Me!cboStoreIP.Column(0) & " IPAddress " & Me!cboStoreIP.Column(1))

StartDate = Me!StartDate.Value
EndDate = 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"

Set rs = CreateObject("ADODB.RecordSet")
Set cn = CreateObject("ADODB.Connection")

cn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "C:\Documents and Settings\Mike Jones\Desktop\" _
& "Payroll_1.2.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)

rs.Open "tablePyrollHours", cn, adOpenKeyset, adLockOptimistic
objRST.Open strSQL, objConn, adOpenForwardOnly, adLockBatchOptimistic
objRST.MoveFirst

While Not objRST.EOF
rs.AddNew
'Debug.Print objRST.Fields("Name")
rs("EmployeeID") = 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")

rs.Update
objRST.MoveNext

Wend

Alex Dybenko said:
Hi,
you can open one more recordset on destination table and add there
records
instead of Debug.Print'ing them:

While Not objRST.EOF
rstNew.AddNew
rstNew("Name")= UCase(objRST.Fields("Name"))
...
rstNew.Update


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Mike said:
I'm very lost and confused to the suggestions that have been offered to
me.
Let me try this one more time.

1.)The code below gets data from other databases.
2.)I have StoreIPAddress string to connect to different database
locations
3.)I have a Select Query to query up payroll hours.
4.)I need to add these records to a database on my computer

Where / how do I reference the database / table to add the records to




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
payroll
hours from " _
& Me!cboStoreIP.Column(0) & " IPAddress " & Me!cboStoreIP.Column(1))

StartDate = Me!StartDate.Value
EndDate = 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 UCase(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...
Click to expand...
 
G

Guest

Thanks for the reply Alex I finally got it last niht about 2am

Alex Dybenko said:
Hi,
so do what errors do you get?
in general code looks ok, the only thing that you need to copy other field
like EmployeeID

rs("EmployeeID") = objRST.Fields("EMPLOYEE_ID")
rs("Name") = objRST.Fields("Name")

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com



(e-mail address removed)...
This is what I have tried but no luck
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 StoreIPAddress As String
Dim statusMessage As String
Dim StartDate As String
Dim EndDate As String

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset


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 payroll
hours from " _
& Me!cboStoreIP.Column(0) & " IPAddress " & Me!cboStoreIP.Column(1))

StartDate = Me!StartDate.Value
EndDate = 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"

Set rs = CreateObject("ADODB.RecordSet")
Set cn = CreateObject("ADODB.Connection")

cn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "C:\Documents and Settings\Mike Jones\Desktop\" _
& "Payroll_1.2.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)

rs.Open "tablePyrollHours", cn, adOpenKeyset, adLockOptimistic
objRST.Open strSQL, objConn, adOpenForwardOnly, adLockBatchOptimistic
objRST.MoveFirst

While Not objRST.EOF
rs.AddNew
'Debug.Print objRST.Fields("Name")
rs("EmployeeID") = 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")

rs.Update
objRST.MoveNext

Wend

Alex Dybenko said:
Hi,
you can open one more recordset on destination table and add there
records
instead of Debug.Print'ing them:

While Not objRST.EOF
rstNew.AddNew
rstNew("Name")= UCase(objRST.Fields("Name"))
...
rstNew.Update


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

I'm very lost and confused to the suggestions that have been offered to
me.
Let me try this one more time.

1.)The code below gets data from other databases.
2.)I have StoreIPAddress string to connect to different database
locations
3.)I have a Select Query to query up payroll hours.
4.)I need to add these records to a database on my computer

Where / how do I reference the database / table to add the records to




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
payroll
hours from " _
& Me!cboStoreIP.Column(0) & " IPAddress " & Me!cboStoreIP.Column(1))

StartDate = Me!StartDate.Value
EndDate = 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 UCase(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...
Click to expand...
 

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

ADODB HELP 7
Add new records to table 3
Add Records to a Table 2
ADO OpenDatabaseConnection 2
Run-time error 3061 7

Top