Create or modify queries using a macro

[italiak]

Hello everyone-

I have these 500 queries in access. They all do the same thing (append
data from big database to a table) and have similar condition. I was
wondering if there is a way I can create a macro to modify the content
of these queries.
If the modify macro is not possible then I can delete the queries and
try to create a macro which can create queries from scratch.
Any help is appreciated.

Thanks,
Italia

[pietlinden]

[e-mail] wrote:

Hello everyone-

I have these 500 queries in access. They all do the same thing (append data from big database to a table) and have similar condition. I was
wondering if there is a way I can create a macro to modify the content of these queries.
If the modify macro is not possible then I can delete the queries and
try to create a macro which can create queries from scratch.
Any help is appreciated.

Thanks,
Italia

Hmm... sounds like they should be parameter queries that get data from
somewhere. No, you can't modify queries with a macro. You can do it
using DAO by playing with the query definition's SQL statement. No...
can't create queries with macros either. Time to learn to code... then
you can do pretty much whatever you want....

[italia]

DAO? You mean VB coding right? Whats the difference between DAO and VB
modules available in Access 2000?
Can you please help me start the coding and I can play around with the
query definition's SQL statement. I have learnt some VB coding from
Google groups.

Thanks for your help !!!

[pietlinden]

DAO is the older (A97 and previous) method of accessing the database's
objects (reports, forms, etc) through code.

DAO is a VB library that has functions/constants etc you can just use
(like someone built them for you).

if you wanted, you could list the SQLs of your queries... are they all
going to change the same way? I would probably pass the old SQL
statement to a function, process it there, and pass the result back to
some looping code...

Here's a stupid example....

Public Sub ListQuerySQLs()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
For Each qdf In db.QueryDefs
If Left$(qdf.Name, 4) <> "MSys" And Left$(qdf.Name, 4) <>
"~sq_" Then
Debug.Print "Name: ", qdf.Name
Debug.Print
"----------------------------------------------"
Debug.Print qdf.SQL
Debug.Print "put your code to modify the SQL statement
here..."
qdf.SQL = modifysql(qdf.SQL)
End If
Next qdf

Set qdf = Nothing
Set db = Nothing

End Sub

Private Function modifysql(strSQL)
'you would do your modifications here...
'like grab the WHERE statement and strip it out....
Dim strTemp As String

strTemp = Left$(strSQL, 10)

modifysql = strTemp
End Function

Okay, all this begs the question - How exactly did you want to modify
the queries in question? Could you post the SQL to one of them?

[italia]

Sorry for the late posting. I was engaged in other stuff.
Listed below is the SQL for one of them. Basically this query will
append records to a table from a master database depending on a
condition.
In other queries all the "1002's" will change to a different number. I
have a total of around 500 queries. It would really help if I use a VBA
code which I think would be faster and less space consuming.
INSERT INTO [1002 COO form] ( Status, [Part No], Description, [Part
Release Date], [(1) Multi source (Y/N)], [(2) Source%], [(3) FTC Type],
[(4) FTC COO], [(5) Subst Transf COO], [(6) NAFTA Qualify (Y/N)], [(7)
NAFTA COO marking], [(8) Country 1 name], [(9) Country 1 %], [(10)
Country 2 name], [(11) Country 2 %], [(12) Country 3 name], [(13)
Country 3 %], [(14) Country 4 name], [(15) Country 4 %], [(16) Country
5 name], [(17) Country 5 %], [Correction required], [Date sent], [Date
returned], [Date Ok], [Date Signed form recvd], [Vendor id] )
SELECT [Total COO required].Status, [Total COO required].Part_No,
[Total COO required].Part_Desc, [Total COO required].Part_Rel_Date,
[Total COO required].[Multi source], [Total COO required].[Source %],
[Total COO required].FTC_Type_New, [Total COO required].FTC_COO_New,
[Total COO required].Subst_COO_New, [Total COO
required].NAFTA_Qualify_New, [Total COO required].NAFTA_COO_New, [Total
COO required].Country1_Name, [Total COO required].Country1_Percent,
[Total COO required].Country2_Name, [Total COO
required].Country2_Percent, [Total COO required].Country3_Name, [Total
COO required].Country3_Percent, [Total COO required].Country4_Name,
[Total COO required].Country4_Percent, [Total COO
required].Country5_Name, [Total COO required].Country5_Percent, [Total
COO required].[Correction explanation], [Total COO required].[Date
Sent], [Total COO required].[Date Returned], [Total COO required].[Date
ok], [Total COO required].[Date signed form recvd], [Total COO
required].Vendor_Id
FROM [Total COO required]
WHERE ((([Total COO required].Status)="1. Correction" Or ([Total COO
required].Status)="2. Pending" Or ([Total COO required].Status)="3.
Sign&Mail" Or ([Total COO required].Status)="4. Disputed") AND (([Total
COO required].Vendor_Id)="1002") AND (([Total COO required].[Part Mod
Code])="M" Or ([Total COO required].[Part Mod Code])="B" Or ([Total COO
required].[Part Mod Code]) Is Null))
ORDER BY [Total COO required].Status, [Total COO
required].Part_Rel_Date, [Total COO required].[Date Sent], [Total COO
required].[Date Returned], [Total COO required].Part_No;
Thanks a ton!!!
-Italia