problem with DoCmd.OpenQuery: not returning values taht are in the table

[trebor1962]

Hello,
Could someone please help with this problem i am having with SELECT query in VBA.

I created a copy of table A = copyA. Table copyA has all correct data in it.
I then reset some fields in original TableA to NULL. All OK.
Now when i use the DoCmd.OpenQuery(tmpSQL) in VBA (looking at the copyA table) it returns the values in the original TableA.
tmpSQL is definitely looking up the CopyA table !
And when i check both tables in the DB they have the correct data that i want.
Any help would be greatly appreciated !!
I have added more detail below if you need it.
thanks in advance !




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 !!!

 

[JHB]

Do you delete the QueryDef "mySQL"?
I can't see if you do or not, but you have to delete it before you can create it again, else you'll get an error. (No deletion and no error, then I think you have some error handling.)
One thing confusing me, is "tmpSQL" = "mySQL"?

 

[trebor1962]

Thanks JHB for your reply.
I just solved it yesterday. The problem was with a "-" character in the table name used in the OpenQuery call.
It was acting very weird but when i took the "-" character out it works fine.
Thanks again.