transform query into sub

[niki]

Hello,
I have created a set of queries and macros which "transform" a mother database into different tables. My problem is that I want to know how I can change a query into a module which will be commanded by a macro.
I need to do this because my database treatment will be automatical, and when I put my query inside a macro, access asks me If I want to add the lines to the tables and so on.
I want the user to be able to populate a table on a single click, therefore I need to point the use of a button from the form on a macro based on a module and not on queries.

The queries I use are quite simple, they use small function based on Instr function, or the count function itself...
Can someone tell me how to write a code line which would perform the same task as the query? After that I'll bind the action of the code and the macro.

Thanks
nico

 

[AncientOne]

Please post a sample query and the table structure you want to achieve

 

[evilman]

Salut!

You can use docmd.runsql("YOUR SQL QUERY HERE") to run your queries. this will only work with non-SELECT queries (INSERT INTO, UPDATE, DELETE, and so on...)
You can run your stored queries with docmd.openquery if you dont feel like pasting all the SQL code in VBA (it might be tedious since quotes have to be transformed into double quotes... i've made a litte subroutine to process SQL code into a regular VBA string though, if you're interested).
Anyway, the problem with OpenQuery is that Access will pop up an annoying message each time.
The solution is simple however:
DoCmd.SetWarnings False
DoCmd.OpenQuery "MyQuery1"
DoCmd.OpenQuery "MyQuery2"
etc
DoCmd.SetWarnings True

SetWarnings False will disable all notices from access!

as a side note, I dont like this SetWarnings method at all. It feels like the access code is all wrong and patched up to the bone. They should have put an extra parameter in the methods of DoCmd instead of a global modifier.