Results 1 to 4 of 4
  1. #1
    trebor1962 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2012
    Location
    Ireland
    Posts
    2

    Pulling data from wrong table in call to DoCmd.OpenQuery: table dependencies???

    Hello,
    Could someone please help with this problem i am having with SELECT query in VBA.
    I am sure it is obvious to the trained eye but i am going mad with it. :-)

    1. I am creating a copy of a current table (TableA) for backup purposes (TableA-"date")
    on FORM A.
    <code>:
    newTable = strTable & "-" & Format(Date, "yyyy") '-- new table name = 'TableA-2012'
    DoCmd.CopyObject , newTable, acTable, strTable
    DoCmd.Close acTable, newTable, acSaveYes


    </code>

    Result = The backup table is created and populated perfectly.

    2. I then run another event procedure from same form (different button) to reset all money values to NULL in the original table ('TableA')
    <code>
    CurrentDb.Execute (UpdateQuery) '-- updateQuery is stored in DB and just sets values to NULL
    </code>

    Result = This works fine. Values are set to NULL.
    So at this point I have the backup table with correct info in it and the original table with values reset to NULL. All ok.

    3. Then in another form I make a call to select fields from the backup table (newTable = 'TableA-2012') as follows.
    <code>
    Set qdef = CurrentDb.CreateQueryDef("mySQL")
    qdef.SQL = "SELECT ID, Name, PG, BO " & _
    " FROM " & newTable & _
    " WHERE ((([PG]) = Yes) And (([BO]) = Yes)) " & _
    " ORDER BY [Name]"
    DoCmd.OpenQuery "mySQL", acViewNormal, acEdit
    </code>

    Result: It seems to be pulling data from the original table i.e returning the NULL values.
    If i create an SQL statement in the DB and store and then hard code a call to it in step 3 it works fine. If i go into the backup table the values are all there.
    ??????
    Has this got something to do with table dependencies or the way the copy object was done?
    I am no expert in this so any help would be greatly appreciated!!!
    Is there a mapping issue in system tables ???

    I have been driven crazy with this one.
    Many thanks in advance !!!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Have to ask - why?

    Why separate tables for each year of data? Why not just continue with one table and filter records as needed?

    If you want to 'backup' data then create backup copy of the entire database.

    Review: http://www.blueclaw-db.com/access_createquerydef.htm
    Dim qdef As DAO.QueryDef
    Set qdef = CurrentDb.CreateQueryDef("mySQL", _
    "SELECT ID, [Name], PG, BO " & _
    "FROM " & newTable & _
    "WHERE [PG]=Yes And [BO]=Yes " & _
    "ORDER BY [Name];"
    DoCmd.OpenQuery "mySQL", acViewNormal, acEdit


    BTW, code tags use [] not <>.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    trebor1962 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2012
    Location
    Ireland
    Posts
    2
    Thanks June7.
    I have to separate because all the fields need to be reset and at end of each year but a copy of last year's data is required and the awkward thing is that historical data needs to be edited up to about early march.
    Its quite complicated.
    But anyway.... i found the problem.
    It was in the naming of the table in VBA for the select query.
    The OpenQuery command does not like a "-" character in its table name.
    It works ok everywhere else.
    I just took this character out and all worked fine.
    Thanks for your comments.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Yes, advise not to use spaces, special characters, punctuation (underscore is exception) in names nor reserved words as names. If do, must enclose in []. Sorry I didn't catch that. I don't allow those by force of habit now so I sometimes just don't see them.

    This should work:
    "FROM [" & newTable & "]" & _
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. DoCmd.OpenQuery with Parameters?
    By mwhcrew in forum Queries
    Replies: 5
    Last Post: 10-10-2014, 01:49 AM
  2. Help pulling data from one table to another
    By bigderon88 in forum Access
    Replies: 2
    Last Post: 03-19-2012, 08:33 AM
  3. Wrong data in table
    By bespra in forum Forms
    Replies: 10
    Last Post: 10-21-2011, 12:26 PM
  4. Listview pulling data from a second table
    By Yesideez in forum Access
    Replies: 8
    Last Post: 06-29-2011, 05:08 AM
  5. Call Excel Data into Access table
    By jiguvaidya in forum Import/Export Data
    Replies: 0
    Last Post: 09-15-2008, 04:58 AM

Tags for this Thread

Posting Permissions

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