Run multiple queries using VBA (1 Viewer)

[Summer123]

Hello, is there an easier way to run multiple append queries using VBA? i know that i can put the docmd.openquery "query name" is one option..bt i have about 200 queries that I would like to run.. all the names of the query start with "qryedit..." so is there a way where i can say if the query name starts with "qryedit..." then execute and loop through each one??? please help..
thanks!
Summer

 

[MarkK]

Yeah, there's a hidden system table in Access called MSysObjects. If you show system and hidden objects you can take a look at it. So you can open a recordset on that table and get a list of names of queries that satisfy certain criteria, something like ...

set rst = currentdb.openrecordset( _
  "SELECT [Name] FROM MSysObjects " & _
  "WHERE [Type] = 5 " & _
    "AND [Name] LIKE 'qedit*'")
with rst
  do while not .eof
    currentdb.querydefs(.Fields(0)).execute
    .movenext
  loop
  .close
end with

Cheers,
Mark

 

[Summer123]

thanks Mark!

 

[MarkK]

you bet summer

 

[sivani_007]

hi there,

I was wondering if you can help me. this work great for me but is there anyway I can output all my queries result to text file. all my queries end with Export.

Hope you can help. thanks

 

[MarkK]

Check out the DoCmd.TransferText method.

 

[sivani_007]

sorry I am new to vba.. can you please provide an example of how I can export 72 files query result in text files..


thanks

 

[Mile-O]

Your Access's in-built help will describe and provide examples for how DoCmd.TransferText works.

 

[sivani_007]

I have found the example of
DoCmd.TransferText acExportDelim, "Standard Output", _
"External Report", "C:\Txtfiles\April.doc"
but how can I export multiple query using that method , I have 72 files to export to text files..


this code works perfect to excute append query set rst = currentdb.openrecordset( _
"SELECT [Name] FROM MSysObjects " & _
"WHERE [Type] = 5 " & _
"AND [Name] LIKE 'qedit*'")
with rst
do while not .eof
currentdb.querydefs(.Fields(0)).execute
.movenext
loop
.close
end with


but how do I amend to transfrer method...