Edit Access Table Entry from Excel VBA

[kryptonian]

I'm doing an attendance database with Excel as the entry form and Access as the database. My fields are:

| SAP_ID | LAST_NAME | GIVEN_NAME | TIME_IN | TIME_OUT |

Entering a new record is no problem if TIME_IN is still blank. But if TIME_IN is not blank, I want my code to edit that particular entry to add into TIME_OUT using the SAP_ID and TIME_IN to specify what record to edit. My code below.

*******************************************************
Sub cmdEdit()

Dim cn As ADODB.Connection
Dim cm As Command

Set cn = New ADODB.Connection
Set cm = New ADODB.Command

'On Error GoTo errHandler
cn.ConnectionString = "Provider = Microsoft.Jet.OleDb.4.0;" _
& "Data Source=C:\Users\v-edcepe\Documents\VBA Training Files\Attendance.mdb"
'sets the path for the connection

cn.Open 'opens the connection

Sheet2.Cells(4, 16) = "=now()"

cm.CommandText = "Update tblLog Set TIME_OUT = '" & Sheet2.Cells(4, 16) & "' where SAP_ID = '" & Sheet2.Cells(25, 3) & "' And TIME_IN='" & Sheet2.Cells(25, 6) & "'"

Set cm.ActiveConnection = cn
cm.Execute
cn.Close

'errHandler:
' MsgBox ("Data not found")
End Sub

*******************************************************

I purposely exluded errHandler for now so I know if my code is wrong.

I am getting a type mismatch error on the cm.CommandText line or cm.Execute if I edit it a bit.

Fields are setup properly in Access.

I'm using Windows 7 running Office 2007.

 

[Ruddles]

Sheet2.Cells(4, 16) = "=now()"

This drew my attention. It will put a number in the region of 40,650 into the worksheet cell, that being the current Excel date/time serial. Does your Access database contain a numeric value in a text field as your SQL suggests (as you wrapped the value for TIME_OUT in single quotes)? Is TIME_OUT a Date type and is it expected to contain a date and time, or just a time?

Insert Debug.Print cm.CommandText immediately after you set up the SQL string and check that it contains exactly what you're expecting it to.

 

[kryptonian]

I have the "=now()" in excel so that this would be the value copied into the Access table for TIME_IN or TIME_OUT. Both are set as Date/Time in Access.

When you say "wrapped the value for TIME_OUT in single quotes", are you saying that I shouldn't do this if I want a Date/Time in that field? How would my CommandText line be like?

 

[Ruddles]

I have the "=now()" in excel so that this would be the value copied into the Access table for TIME_IN or TIME_OUT. Both are set as Date/Time in Access.

now() generates an Excel date/time serial which is a number like 40651.652778 (3.40pm on 18th April 2011) not a formatted date/time string which SQL expects.

Please try this: insert Debug.Print cm.CommandText immediately after you set up the SQL string and take a look at exactly what's actually in there.

I suspect you're generating something like Set TIME_OUT = '40651.652778' whereas it should be like Set TIME_OUT = #4/18/2011 15:40#.

You need to make sure that when you pass a date/time from Excel to SQL, you're converting it from a serial number to a formatted string, and vice versa.

When you say "wrapped the value for TIME_OUT in single quotes", are you saying that I shouldn't do this if I want a Date/Time in that field? How would my CommandText line be like?

You'd wrap a SQL date/time in # characters.