Error cannot find object when using SQL code to create a recordset 1

[tlstevens]

I am trying to create a record set so that I can pull how many records in a table meet a certain criteria. I must be coding something wrong because everything I put in the source area gives me an error. I can't seem to reference a table, or a query.. Here is the code:

Public Sub CheckOpenCalls()
Dim objDB As Database
Dim objRS As Recordset
Dim objprp As Property
Dim textsql As String
Dim NumOfRecs As Integer

MsgBox ("declarations done")


Set objDB = DBEngine.Workspaces(0).OpenDatabase("supportdesk_be.mdb")
Set objRS = objDB.OpenRecordset("SELECT * FROM [Call Log]" , dbOpenTable)
Set Forms![Tech Support Call Log]![Command111].Visible.Value = "No"

MsgBox ("Number of Recs = " & objRS.RecordCount)
If objRS.RecordCount > 0 Then objprp.Value = "Yes"
End Sub


What am i doing wrong? *pulling hair out* "Call Log" is the name of a linked table

 

[jebry]

Hi!

I'm not sure about ADO but a DAO recordset of a linked table can't be opened using dbOpenTable, try this:

Set objRS = objDB.OpenRecordset("SELECT * FROM [Call Log]" , dbOpenDynaset)

or dbOpenSnapShot?

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[tlstevens]

Thankyou!