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

LVL 1
simonwaitAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ken ButtersCommented:
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

0
leonstrykerCommented:
Try this:

sql2 = "SELECT COUNT(*) AS recordcount1 FROM Hours WHERE TimesheetDate='" & searchDate & "' AND EmployeeID=" & searchID

and

MsgBox bconn(0).Value


Leon
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
leonstrykerCommented:
You need to move your cursor to the first record in the recordset.

True with DAO, but not with ADO.
0
Patrick MatthewsCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.