Results 1 to 5 of 5
  1. #1
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    77

    OpenRecordSet - Why does one of these work and the other not?

    I'm perplexed, and would like to understand what the issue is. Sample code I've found shows that you can use OpenRecordSet to open a table just by naming the table. But when I try it, I get an error message. It will work, however, if turn it into a query. Why?



    Code:
    Dim oRS As DAO.Recordset
    Dim recordings_table As String
    
    recordings_table = "[Recordings]"      ' name of the table 
    
    Set oRS = CurrentDb.OpenRecordset(recordings_table) ' Throws an error message: "cannot find the input table or query '[Recordings]'.
    
    
    Set oRS = CurrentDb.OpenRecordset("SELECT * FROM " & recordings_table) ' opens without an error

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,003
    its looking for sql ,
    always use 'select from table'

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,237
    Lose the brackets
    Recordings_table = "Recordings"

  4. #4
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    77
    Then why does a lot of sample code show just the table name? Here's an example from Microsoft:
    Code:
    Dim dbs As DAO.Database    Dim rsTable As DAO.Recordset    Dim rsQuery As DAO.Recordset        Set dbs = CurrentDb        'Open a table-type RecordsetSet rsTable = dbs.OpenRecordset("Table1", dbOpenTable)

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,237
    Quote Originally Posted by cherold View Post
    Then why does a lot of sample code show just the table name? Here's an example from Microsoft:
    Code:
    Dim dbs As DAO.Database    Dim rsTable As DAO.Recordset    Dim rsQuery As DAO.Recordset        Set dbs = CurrentDb        'Open a table-type RecordsetSet rsTable = dbs.OpenRecordset("Table1", dbOpenTable)
    Well, there's really no reason to set the table name to a variable, and then use the variable name in the OpenRecordset.
    Your problem was that Access was trying to open a table with the name [Recordings], which is wrong. The table name is Recordings. That's why I said to lose the brackets.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error with OpenRecordset
    By snsmith in forum Modules
    Replies: 15
    Last Post: 06-04-2019, 01:43 PM
  2. Set rst = qdf.OpenRecordset(dbOpenSnapshot)
    By malin7232 in forum Programming
    Replies: 2
    Last Post: 06-25-2017, 10:04 PM
  3. Too Few Parameters on .OpenRecordset()
    By Voodeux2014 in forum Forms
    Replies: 9
    Last Post: 01-28-2016, 04:45 PM
  4. db.OpenRecordset error
    By jscriptor09 in forum Programming
    Replies: 4
    Last Post: 01-27-2012, 12:17 AM
  5. OpenRecordset vs. Query
    By crispy-bacon in forum Programming
    Replies: 7
    Last Post: 07-04-2011, 09:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums