473,382 Members | 1,132 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

Create or modify queries using a macro

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

Nov 13 '05 #1
4 9802

ital...@gmail.com 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....

Nov 13 '05 #2
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 !!!

Nov 13 '05 #3
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?

Nov 13 '05 #4
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

Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Patrick Fisher | last post by:
Hi I have a table which Contains entries with RefCode field containing INVP or INVPD Common fields in each entry would be InvoiceNo, Total and PurTyp for example. You could have ...
3
by: jallegue | last post by:
I am would like to get the list of queries that make a macro in MS-Access using vb 6.0.
0
by: MHenry | last post by:
Hi, I know virtually nothing about creating Macros in Access. I would appreciate some help in creating a Macro or Macros that automatically run(s) 14 Queries (three Make Table Queries, and 11...
5
by: HS Hartkamp | last post by:
Hi all, I am working with fairly large databases (> 500 Mb / < 2,000,000 rexcords), and often need to do action queries on these. I have the feeling that much of the computing power is going...
0
by: doninsbca1 | last post by:
I want to start at the macro level (or query level) and document the queries, sub-queries, and tables that macro or primary query uses. I don't want to have a documenter that goes down one level...
9
by: John Brock | last post by:
I am trying to create a workbook where one of the worksheets contains an AutoFiltered table. It looks like it should be simple -- what I am doing is this: rng = ws.Range("MyTableRange")...
4
by: mimasci | last post by:
Hi, I have tried to write a code that allowed me to modify the value of the cells selected in a table, with excel has been easy. How one approaches selected cells? (To modify the value) How...
3
by: wsox66 | last post by:
I am new to Access and need some help building a report. I have looked through previous posts on reports but none of them seem to answer my question completely. I am using Access 2003 and I have...
3
by: swb76 | last post by:
Hi, I have 6 queries in Access that run great. They need to be run in sequence with the first 5 queries writing to tables and the sixth one pops up the final results in datasheet view. Now, how...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.