Updating a table

[reycons]

Table 1

EmpID Logindate Undertime_From UnderTime_To
1 8/27/08 8:00 AM 10:00 AM
2 8/28/08 10:00 AM 12:00 PM


Table2

EmpId Logindate Undertime_From Undertime_From
1 8/27/08
2 8/28/08

Good day. I cant update table 2. The record should update in table2 that matches all the empid and logindate from table1.

 

[SBerthold]

>I cant update table 2

Without just feeding you a
solution to your question, show us what sql statement you have written to update with, and tell us what, if any, problems you have encountered.

Or are you just looking for someone to show you some basics?

You can use the Help files to see how a bulk update is performed using the connection's or a command Object's .Execute method and an UPDATE sql statement with an INNER JOIN.

 

[reycons]

cn.connectionstring = "Provider=MSDASQL.1;" & "Persist Security Info = False;" & "Data Source=Timekeeper;"
cn.Open

rsview.Open "SELECT DTR.PK, DTR.EmpPK, DTR.LoginDate, EmployeeSchedule.starttime, DTR.AMTIMEIN, DTR.PMTIMEIN, UTOS.fromdate FROM (DTR INNER JOIN UTOS ON DTR.PK=UTOS.pk) INNER JOIN EmployeeSchedule ON (DTR.Logindate=EmployeeSchedule.StartDate) AND (DTR.Emppk=EmployeeSchedule.Emppk) ;", cn

Do Until rsview.EOF
If Not (rsview.BOF And rsview.EOF) Then
dstarttime = rsview.Fields!starttime
dLogindate = rsview.Fields!LoginDAte
dfromdate = rsview.Fields!fromdate
cEmppk = rsview.Fields!emppk
dstarttime = rsview!starttime
MsgBox dstarttime
End If
rsview.MoveNext
Loop




rstemp.Open "Select * From TempDTR_Total WHERE Emppk like '" & cEmppk & "' AND LoginDate like '" & dLogindate & "' ", cn, 1, 2

'Update the TempDtr_Total Table..............................................................................................................
Do Until rstemp.EOF
If Not (rstemp.BOF And rstemp.EOF) Then
rstemp.Fields!fromdate = dfromdate
rstemp.Fields!starttime = dstarttime
rstemp.Update
End If
rstemp.MoveNext
Loop

rstemp.Close
cn.Close

This is my sql code which i put on my program.. It Doesnt update the Table which is the TempDtr_Total. All the data is coming from the query which is the rsview.

 

[keenanbr]

Not sure what you're tring to do but i think


rstemp.Open "Select * From TempDTR_Total WHERE Emppk like '" & cEmppk & "' AND LoginDate like '" & dLogindate & "' ", cn, 1, 2


should read


rstemp.Open "Select * From TempDTR_Total WHERE Emppk = '" & cEmppk & "' AND LoginDate = '" & dLogindate & "' ", cn, 1, 2


or maybe


rstemp.Open "Select * From TempDTR_Total WHERE Emppk like '%" & cEmppk & "%' AND LoginDate like '%" & dLogindate & "%' ", cn, 1, 2


Is the second SQL statement returning anything?

 

[SBerthold]

Well, if you are going to update the DB using a recordset with a keyset cursor, then make sure you have the CursorLocation set to

rstemp.CursorLocation = adUseServer '(2)

If you are using your rstemp for nothing but updating the DB, then I suggest to use the connection's or Command Objects .Execute method with a bulk UPDATE sql statement, as I mentioned above.
Using a recordset to just do an Update with is by far way less effecient.

 

[reycons]

Dim objconnection As New ADODB.Connection
Dim objcommand As New ADODB.Command
Dim cn2 As String
Dim cSQL As String

objconnection.Open cn
objcommand.CommandType = adCmdText

cn2 = "Provider=MSDASQL.1;" & "Persist Security Info = False;" & "Data Source=Timekeeper;"

cSQL = "UPDATE TempDtr_Total SET Starttime = '" & dstarttime & "',Fromdate = '" & dfromdate & "' WHERE Emppk like '%" & cEmppk & "%' AND LoginDate like '%" & dLogindate & "%' "

objcommand.CommandText = cSQL
objcommand.ActiveConnection = cn2
objcommand.Execute

Set objcommand = Nothing
Set objconnection = Nothing

I've tried updating the records using the object.execute but it doesn't work correctly, it saves but only one record. I can't save all the record from the query that i've been using...
Is there anyone can suggest me the code of updating the records by bulk...?
Any help would much be appreciated....

 

[SBerthold]

Why are you using LIKE on the 'LoginDate' Date field?

Is Date and Time stored in the field and you want to set criteria based only on a Date Value (no Time)? If so, you will need to do a conversion of the field to a date, or set the criteria using:

WHERE (LoginDate >= TheDateCriteria AND LoginDate<(TheDateCriteria +1Day))

Also, I am assumming the variable 'dLogindate' is a variable declared AS DATE. If your system date format is not set as mm/dd/yyyy or yyyy-mm-dd then you will need to format the Date as such using

& Format$(dLogindate,"yyyy-mm-dd") &