Results 1 to 13 of 13
  1. #1
    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

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    755
    Hi
    Try

    rs.Open "Select * FROM Hours", db

    (without the apostrophy at the frot!!)

    HTH

    MTB

  3. #3
    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.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    755
    Hi agian

    Did you put the FROM in the SQL

    rs.Open "Select * FROM Hours", db

    It worked for me !?

    MTB

  5. #5
    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

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    755
    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

  7. #7
    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....

  8. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    755
    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

  9. #9
    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....

  10. #10
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    755
    What is the error message?
    Is it on the same line?

    MTB

  11. #11
    Join Date
    Nov 2002
    Posts
    7
    same line runtime error '-2147217865 (80040e37)' automation error. Same line....

  12. #12
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    755
    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

  13. #13
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •