query - strange problem when ran from VBA

[Davros]

hi
really strange problem i just can't get my head around.
i have a query called "que_02_delete"
to run this from VBA i have typed
DoCmd.OpenQuery "que_02_delete"

the code will not run! i just get an error message telling me that the object cannot be found
i just don't understand it?
any ideas anyone??

thanks in advance

 

[Galaxiom]

Some kind of corruption maybe.
Copy the SQL, make a new query and paste it to the sql view.

 

[Davros]

did that
still does not run

 

[vbaInet]

I believe you've mispelt the query name. I suspect that your query doesn't contain underscore or there's a trailling space.

Rename your query and try it again.

 

[Galaxiom]

It should work. Capital alpha O and zero confusion error?

 

[Davros]

no
none of the above. however i did compile the code in notepad to start with and then copied it across to VBA. the quotations marks were slightly different - this is the only difference i could see. i also exported everything to another database without any luck. strange thing is is that i can run the query by clicking on it.
this will have to do
anyway another problem i have which i can't figure out is that when i import the tables that need to be queried i also have code which deletes them when i've finished. this code won't work because it says relationships still exist. i don't understand this - the relationships exist in the database i've imported from - yes - but they don't exist in the database where the imported tables reside.
minor problems which i can't be bothered to spend time on solving. by the time i've done the above manually i've spent 5 times as much time trying to figure out why they are not working
still if anybody does have any suggestions.....

thanks

 

[Galaxiom]

Not a good idea to delete the tables. Access databases have a limited number of objects (~750) in their life and eventually you won't be able to create the new tables. Export the objects to a new database to reset the count.

"Temporary tables" should be held in a separate database and linked. The practice of including them in the front end is a very common design mistake. They should be emptied and reused or created in a disposable local database.

BTW. The word "compile" has a specific meaning and you certainly did not "compile" the code in Notepad.

 

[vbaInet]

no
none of the above. however i did compile the code in notepad to start with and then copied it across to VBA. the quotations marks were slightly different - this is the only difference i could see. i also exported everything to another database without any luck. strange thing is is that i can run the query by clicking on it.
this will have to do

Hmm... how did you compile the code in Notepad??

Maybe you can attach your db so we can try it from our end?

anyway another problem i have which i can't figure out is that when i import the tables that need to be queried i also have code which deletes them when i've finished. this code won't work because it says relationships still exist. i don't understand this - the relationships exist in the database i've imported from - yes - but they don't exist in the database where the imported tables reside.
minor problems which i can't be bothered to spend time on solving. by the time i've done the above manually i've spent 5 times as much time trying to figure out why they are not working
still if anybody does have any suggestions.....

thanks

In the Import dialog you forgot to untick Relationships which you will find when you click the Options >> button.

 

[Davros]

i didn't compile the code in notepad - i wrote it in notepad, exported it to VBA, copiled it - no problem. problem occured when i ran it.
I have now deleted the database after exporting all my objects to a new database. i was working on a database started by someone else but i have done 80% of the work. i noticed a few other strange things with the database. on reflection perhaps i should have exported all the objects to a new database before i started and deleted the database i inherited.
thanks for the tip about the relationships though

 

[spikepl]

#7 Limited number of tables during life time?

I tried to google this, and ran into http://blogs.msdn.com/b/access/archive/2006/06/05/access-2007-limits.aspx , where the limit of 754 seems to apply to "Number of controls and sections added over the lifetime of a form / report"

I am very interested in this issue, because I also make use of some temporary tables that I delete in every session. If there is a limit to how many times this can be done, I'll need a major rethink.

 

[boblarson]

Not a good idea to delete the tables. Access databases have a limited number of objects (~750) in their life and eventually you won't be able to create the new tables.

Hey G -

Tables aren't included in that object limitation. However, I totally agree that creating and deleting tables is a bad practice.

 

[boblarson]

I am very interested in this issue, because I also make use of some temporary tables that I delete in every session.

Can you not just create them once and then delete the DATA and repopulate them?

 

[spikepl]

#12 Perhaps I could ...

My single-user application starts with reading various sheets from an Excel-workbook, a table created on the fly per each, and then transposing the data into new temporary tables before further processing. The headers may vary from workbook to workbook, so I have taken the easy path and create all tables from scratch. Admittedly, further down the road, I need to compare the headers (now first column) with a data dictionary and reject what does not fit - I could in principle update exsisting tables.

But please do enlighten me: why is dropping tables in a single-user app such a nono?

 

[boblarson]

But please do enlighten me: why is dropping tables in a single-user app such a nono?

It contributes greatly to database BLOAT and the potential for database corruption the more that you do it. But, if you are fine with both of those, then ...

 

[spikepl]

No I am not fine with BLOAT and corruption - I sincerely meant the question "Why" !

 

[spikepl]

My DB is set to compact automatically on close ... so I did not think there would be an issue.. is there really?

 

[boblarson]

My DB is set to compact automatically on close ... so I did not think there would be an issue.. is there really?

Yes, there still can be an increased potential for corruption.

 

[spikepl]

OK thanks for your comments - I have to think about how to rearrange my input.

 

[spikepl]

#17 I have heeded your advice and revamped my code, so I just delete all rows in the respective tables. Also, importing the entire thing into a new DB shrunk it from 3.5 MB to 2.5 MB! Now I have one new problem - some code that persistently refuses to be deleted: http://www.access-programmers.co.uk/forums/showthread.php?t=201715

Apologies to OP for diverting the thread.