Solved

delete queries in access

Posted on 2014-10-07
6
169 Views
Last Modified: 2014-10-12
I want to delete queries in my db that have any of the names but not the word "column", will this work?

For Each Qry In Db.QueryDefs


Select Case Qry.Name
 
Case InStr(Qry.Name, "abi") <> 0 And InStr(Qry.Name, "column") > 0
DoCmd.DeleteObject acQuery, Qry.Name

Case InStr(Qry.Name, "cap") <> 0 And InStr(Qry.Name, "column") > 0
DoCmd.DeleteObject acQuery, Qry.Name

Case InStr(Qry.Name, "glass") <> 0 And InStr(Qry.Name, "column") > 0
DoCmd.DeleteObject acQuery, Qry.Name

Case InStr(Qry.Name, "kee") <> 0 And InStr(Qry.Name, "column") > 0
DoCmd.DeleteObject acQuery, Qry.Name

Case InStr(Qry.Name, "smmt") <> 0 And InStr(Qry.Name, "column") > 0
DoCmd.DeleteObject acQuery, Qry.Name



End Select

 
 
 
 
 
Next

Open in new window


if not how please
0
Comment
Question by:PeterBaileyUk
  • 4
  • 2
6 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40366619
do you mean, if the name of the query is "qry_whatever_column" which have the word "column" in the name, delete the query?


For Each Qry In Db.QueryDefs
    if InStr(Qry.Name, "column") > 0 then
          DoCmd.DeleteObject acQuery, Qry.Name
   end if
next
0
 

Author Comment

by:PeterBaileyUk
ID: 40366633
the queries are like this which can be deleted
QryGlass3Series

Qry[clientname][model]

BUT

I must preserve queries like so "columnout" is keyword
QryABIColumnOut

the five client names are
abi
glass
kee
cap
smmt
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40366646
clear as  mud!

What is  "columnout" is keyword?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:PeterBaileyUk
ID: 40366666
its a word in the queries I dont want to delete only five of them

QryAbiColumnOut
QryCapColumnOut
QryKeeColumnOut
QrySMMTColumnOut
QryGlassColumnOut

The rest I want to delete are of the form

Qry[clientname]xxx ie abi cap etc as above 5 client names butthe xxx part does not have columnout
0
 

Accepted Solution

by:
PeterBaileyUk earned 0 total points
ID: 40366685
ok did it myself

For Each Qry In Db.QueryDefs



 
If InStr(Qry.Name, "abi") <> 0 And InStr(Qry.Name, "column") = 0 Then
DoCmd.DeleteObject acQuery, Qry.Name

End If

If InStr(Qry.Name, "cap") <> 0 And InStr(Qry.Name, "column") = 0 Then
DoCmd.DeleteObject acQuery, Qry.Name
End If
If InStr(Qry.Name, "glass") <> 0 And InStr(Qry.Name, "column") = 0 Then
DoCmd.DeleteObject acQuery, Qry.Name
End If
If InStr(Qry.Name, "kee") <> 0 And InStr(Qry.Name, "column") = 0 Then
DoCmd.DeleteObject acQuery, Qry.Name
End If
If InStr(Qry.Name, "smmt") <> 0 And InStr(Qry.Name, "column") = 0 Then
DoCmd.DeleteObject acQuery, Qry.Name

End If
0
 

Author Closing Comment

by:PeterBaileyUk
ID: 40375444
because I found the solution myself
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now