I am trying to pull in data from an access query. I thought it was the same as pulling back data from a table but I cannot make it work.... the query is names hours... any ideas? Thanks in advance..

Function accessdata()
Dim db, rs
Set db = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=map.mdb;Persist Security Info=False"

'rs.Open "Select * Hours", db
rs.MoveFirst
Worksheets("z").Cells(1, 1).Value = rs("pin").Value

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Function

Hi
Try

rs.Open "Select * FROM Hours", db

(without the apostrophy at the frot!!)

HTH

MTB

Sorry, I cut and pasted the code with that line commented out. It still doesnt work with the apostrophy out. Thanks though.

Hi agian

Did you put the FROM in the SQL

rs.Open "Select * FROM Hours", db

It worked for me !?

MTB

Thats wierd... Hours is a query in my access db not a table.... when I subsitute a table for the query name it works fine so I am not sure what the deal is. Here is the exact code that I am using.... It is vba behind an excel sheet, pointing to an mdb...

Function accessdata()
Dim db, rs
Set db = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=p:\trd apps\taas\newmaputl.mdb;Persist Security Info=False"

rs.Open "Select * from JAN-CONTHOURS1", db
rs.MoveFirst
Worksheets("z").Cells(1, 1).Value = rs("pin").Value

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Function

Hi Again

I copies your code again, pasted into a excel VBA module, changed th database path/name and cells ref, and then ran it. It works OK!!!

What is the error message and where does the code break ??

One other question Why is it a function??

MTB

Runtime Error 80040e14 - Automation error

The function part is just laziness...I deleted an old function and started coded in the area....

The code breaks on the rs.open line....

Hi

It would seem that it does not like hyphens in the SQL statment.

I copied a query an inserted a hyphen it the failed with the same error (syntax error in FROM clause). Remove hyphen it works again ! (you learn something new every day – I hope).

Try it on q query without a hyphen and see if it works.

MTB

I was really hoping it was something silly like that... unfortunately I renamed the query ContractorHours and it now gives me error 40080e37. I really appreciate your help....

What is the error message?
Is it on the same line?

MTB

same line runtime error '-2147217865 (80040e37)' automation error. Same line....

Did you say it worked with a table name but not a query name?

Are the tables linked or are they in the database with the query?

I do not know if it has any bearing but I'm using Excel/Access 97

But it still works on Excel 2000 and the Access 97 Database

I feel sure that using a System DSN (or File DSN if shared used by others on the same network) setup in then Control Panel to connect to the DB might solve the problem. If it does not then it beats me for now (more time required must get some work done!).

MTB

The tables are linked and I have an odbc connection setup so I am not sure what else to try. I am using excel/access 2003.... well thanks for the help...