Is it possible ...

[karina]

to open a recordset from a query from the Access instead of from a table. Right now I'm opening recordset from table like this:

Set rstEmployees = dbs.OpenRecordset("my_table", dbOpenTable).

But if I substitute my_table with my_query, it doesnt work.

By the way: This code is inside Excel macro.

Is it possible?

Thanks.

 

[Joe4]

My guess is if you working with a query, then you do not want to use "dbOpenTable" in your statement. Is their a similar "dbOpenQuery" statement?

You also may have better luck posting on the Access section of Mr. Excel, since, though the code may be in Excel, it is really an Access code question.

 

[Von Pookie]

Moved to the Access forum.

 

[Corticus]

Hi karina,

Try:
DoCmd.openquery "MyQuery", acViewNormal, acEdit

replace "MyQuery" with the name of your query.

HTH,

 

[karina]

Corticus,

first of all: thanx for your help,
but how do I set my recordset ?
I tried
Set rstEmployees = DoCmd.openquery("bad_company", acViewNormal, acEdit) but I get runtime error 424 "Object required".

Thank you

 

[Corticus]

I misunderstood your question,

I though you were trying to open a query, the code I provided will open a query in edit mode if you run it by itself.

To set your recordset as you want,

dim sql as string
dim db as database
dim rs as object

sql ="your SQL;"
Set rs = db.OpenRecordset(sql)

To get your SQL, go to the query, view it in SQL view (as opposed to design grid or data sheet), and cut and paste it into where I have "your SQL". Be careful to use line breaks carefully, if you can't get it right, just put the whole expression on one line like "SELECT xxx FROM yyy WHERE zzz;."

Oh I should mention, if you want to run this in an Excel macro, you will most likely need to add the Access VBA library to your references by selecting Tools|References and checking it, from the VBA editer. In fact, I don't really know if what your trying to do will work, its hard to tell.

HTH,