asked on

Excel SQL count incorrect

I have written some code which will write the values of a timesheet to an access database. If the record already exists (based on the fact that the employeeID and TimesheetDate values match the values) it updates the record otherwise it inserts a new one. I am at the stage of checking for an existing record. I am doing this with a COUNT statement. If I put this into Access's SQL viewer it returns a couple of records which is correct. If I do it in excel it returns 0. (The input box is so that I can copy and paste the generated string to check it works)

Cheers

Sub send()

Dim aconn As New ADODB.Recordset
Dim bconn As New ADODB.Recordset

Dim conn1 As New ADODB.Connection
Dim c As Range
Dim newcol As String
Dim recordcount1 As Long


stDB = "C:\Users\Operator\Documents\Timesheets\Timesheets.mdb"
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & stDB & ";"

With conn1
    .Open (stConn) 'Open the connection.
searchDate = Sheets("Datasheet").Range("E5").Value
searchID = Sheets("Datasheet").Range("O2").Value

sql2 = "SELECT COUNT(*) AS recordcount1 FROM Hours WHERE TimesheetDate=""" & searchDate & """ AND EmployeeID=" & searchID
a = InputBox(a, , sql2)
With bconn
If .State = adStateOpen Then .Close
.Open sql2, conn1, adOpenStatic, adLockReadOnly
MsgBox (recordcount1)
End With

If recordcount1 > 0 Then
MsgBox ("Record Found")
Else
MsgBox ("Record NOT Found")
End If

sqlheads = "EmployeeID, TimesheetDate, EmployeeName, "
sqlvalues = Sheets("Datasheet").Range("O2").Value & ", " & Sheets("Datasheet").Range("E5").Value & ", '" & Sheets("Datasheet").Range("E3").Value & "', "


For Each c In Sheets("Datasheet").Range("MainBlock")
Sheets("Datasheet").Range(c.Address).Select

If Selection.MergeCells Or Range(c.Address).Value = 0 Then
GoTo skip
End If

rangename = c.Name.Name
sqlheads = sqlheads & rangename & ", "
sqlvalues = sqlvalues & Range(rangename).Value & ", "
skip:
Next c
On Error GoTo 0
sql1 = "INSERT INTO Hours (" & Left(sqlheads, Len(sqlheads) - 2) & ") VALUES (" & Left(sqlvalues, Len(sqlvalues) - 2) & ")"
With aconn
If .State = adStateOpen Then .Close
.Open sql1, conn1, adOpenStatic, adLockReadOnly
End With

End With
End Sub

Open in new window

Ken Butters

I think you need to add something like the following code
You are returning an ADODB recordset as a result of the query.

You need to move your cursor to the first record in the recordset.

In your case, since you are returning a count(*)... you only have 1 record... further... you only have 1 field...

So then you can reference the first field ".fields(0) in that row of your resultset.

With bconn
If .State = adStateOpen Then .Close
.Open sql2, conn1, adOpenStatic, adLockReadOnly

.moveFirst
recordCount1 = .fields(0).value

MsgBox (recordcount1)
End With

Open in new window

ASKER CERTIFIED SOLUTION

leonstryker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

leonstryker

You need to move your cursor to the first record in the recordset.

True with DAO, but not with ADO.

Patrick Matthews

simonwait,

If Leon's first suggestion does not work...

1) What data type is the TimesheetDate column in Access? Date? Text? Long? Double?

2) Exactly what SQL statement gets executed? You can get it from the default value of your InputBox.

Patrick