OpenRecordset Method

Can anyone help me with this code, tell me where im goign wrong.

[vb]
Dim rec As Recordset, query As String, db As Object

query = "SELECT EmployeeID , FirstName , LastName " _
& "FROM Employee " _
& "WHERE DepartmentName = '" & deptComboBox.Text & "';"

Set db = Application.CurrentDb
Set rec = db.OpenRecordset(query, 1)
[/vb]

This gives the error :

"Run-time error '3011':

The Microsoft Jet engine could not find the object 'SELECT EmployeeID, FirstName, LastName FROM Employee WHERE DepartmentName = 'Finance';'. Make sure the object exists and that you spell its name and path name correctly"

This is for a form in Access. I have run the query in Access and it gives the correct results so the query is correct.

I am in desperate need of help.

Thanks in advance
Andy

You specified dbOpenTable, so the OpenRecordset method assumes your query variable contains a table name. Since the table does not exist it raises the error.

Change the Type parameter to one of the other possible RecordsetTypeEnum values.

Ive tried all the other arguments, text based ones like dbOpenSnapshot give an invalid argument error, and a different integer gives a type mismatch error. If I omit the argument I get a type mismatch error.

Andy

OK try this it works for me.

query= "select [EmployeeID] ,[FirstName] , [LastName] from Employee where departmentname = '"& deptcombobox &"'"
set db = currentdb
setrec = db.openrecordset(query)


For this to work you must have the following

A table or query named Employee

this table or query must have the field names employeeid,firstname,lastname and departmentname as a minimium. If it doesn't it will not work.

JFK

If you have somehow checked an ado reference, or both ado and dao, a type mismatch can occur.

Try This:

query = "SELECT EmployeeID , FirstName , LastName, DepartmentName " _
& "FROM Employee " _
& "WHERE DepartmentName = '" & deptComboBox.Text & "';"

DeparmentName is not part of your select query this might be causing the problem.

Hope this will help.:)

Thanks for the help. It apperas to be working this morning.
I tried putting the Openrecordset code in a seperate procedure, passing in an empty recordset and the query as parameters. I couldnt get this to work, but when I put it back into the same procedure it worked. I've got know idea why.

I have another problem though, In my employee table there is a column with an OLE object. The OLE object is an excel spreadsheet, with each employee having their own copy of the same sheet. My problem is I so far havent been able to find a way of inserting the spreadsheet as an ole object into the table when I create a new employee, creating a new table row.

Its easy enough to create the row, doing the RunSQL command and an INSERT..INTO query, to put the other details in (ie name etc.).

So if anyone can guide me as how to do this, either through VB or using SQL, I would be most grateful.

Thanks
Andy