If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > VBA Recordset for Access Query??

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Posts: 7
VBA Recordset for Access Query??

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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Derbyshire, UK
Posts: 745
Hi
Try

rs.Open "Select * FROM Hours", db

(without the apostrophy at the frot!!)

HTH

MTB
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Posts: 7
Sorry, I cut and pasted the code with that line commented out. It still doesnt work with the apostrophy out. Thanks though.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Derbyshire, UK
Posts: 745
Hi agian

Did you put the FROM in the SQL

rs.Open "Select * FROM Hours", db

It worked for me !?

MTB
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Posts: 7
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Derbyshire, UK
Posts: 745
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Posts: 7
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....
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Derbyshire, UK
Posts: 745
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
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Posts: 7
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....
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Derbyshire, UK
Posts: 745
What is the error message?
Is it on the same line?

MTB
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Posts: 7
same line runtime error '-2147217865 (80040e37)' automation error. Same line....
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Derbyshire, UK
Posts: 745
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
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Posts: 7
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...
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On