Can a popup form be used to enter SQL?

[deko]

Is there a "best practices" way to run ad hoc SQL queries?

Private cmdRun_Click()
strSql = Me!txtBox
DoCmd.RunSQL strSql
End Sub
???
Does access have a built-in way to bring up a window for this purpose?

[Trevor Best]

On Sun, 18 Jan 2004 16:08:11 GMT in comp.databases.ms-access, "deko"
<dj****@hotmail.com> wrote:

Is there a "best practices" way to run ad hoc SQL queries?

Private cmdRun_Click()
strSql = Me!txtBox
DoCmd.RunSQL strSql
End Sub
That's about what I have in mine along with the big red disclaimer
about users doing this at their own risk, etc. although I don't
usually let users have access to that.
Does access have a built-in way to bring up a window for this purpose?

Just the queries, SQL view.

--
A)bort, R)etry, I)nfluence with large hammer.

[DFS]

"deko" <dj****@hotmail.com> wrote in message
news:Lb******************@newssvr25.news.prodigy.c om...

Is there a "best practices" way to run ad hoc SQL queries?

Private cmdRun_Click()
strSql = Me!txtBox
DoCmd.RunSQL strSql
End Sub
???
Does access have a built-in way to bring up a window for this purpose?

Not necessarily "built-in" but easy to code.

Set qItem = db.CreateQueryDef("queryName", me.txtBox)
DoCmd.OpenQuery "queryName", , acReadOnly
Set qItem = Nothing
'LATER YOU'LL HAVE TO DELETE THE QUERYDEF

DoCmd.DeleteObject acQuery, "query name"

[Mike Storr]

Access dosen't have anything like that built in, but you can use the SQLview
of the query deisgn to essentially do the same thing, and it may be a bit
safer. Also, the RunSQL method only works with action queries, so if the
user just wants to gather a list, then this won't help.

Mike Storr
www.veraccess.com
"deko" <dj****@hotmail.com> wrote in message
news:Lb******************@newssvr25.news.prodigy.c om...

Is there a "best practices" way to run ad hoc SQL queries?

Private cmdRun_Click()
strSql = Me!txtBox
DoCmd.RunSQL strSql
End Sub
???
Does access have a built-in way to bring up a window for this purpose?

[deko]

> Not necessarily "built-in" but easy to code.

Set qItem = db.CreateQueryDef("queryName", me.txtBox)
DoCmd.OpenQuery "queryName", , acReadOnly
Set qItem = Nothing

'LATER YOU'LL HAVE TO DELETE THE QUERYDEF

DoCmd.DeleteObject acQuery, "query name"

Outstanding! That works great... thanks for the help!

The one question I have is where to put "db.TableDefs.Refresh"

Private Sub cmdRunQuery_Click()
On Error GoTo HandleErr
Dim db As DAO.Database
Dim qItem As QueryDef
DoCmd.DeleteObject acQuery, "qry_" & mdbName
Set db = CurrentDb
db.TableDefs.Refresh '<<==== should the be here, or after the next line?
Set qItem = db.CreateQueryDef("qry_" & mdbName, Me!SqlString)
DoCmd.OpenQuery "qry_" & mdbName, , acReadOnly
Exit_Here:
Set db = Nothing
Set qItem = Nothing
DoCmd.Close acForm, Me.Form.Name
Exit Sub
HandleErr:
Select Case Err.Number
Case 7874 'Microsoft Office Access can't find the object 'qry_...'
Resume Next
Case Else
modHandler.LogErr (Me.Form.Name & "(cmdRunQuery_Click)")
Resume Exit_Here
End Select
End Sub

[DFS]

"deko" <dj****@hotmail.com> wrote in message
news:Du******************@newssvr27.news.prodigy.c om...

Not necessarily "built-in" but easy to code.

Set qItem = db.CreateQueryDef("queryName", me.txtBox)
DoCmd.OpenQuery "queryName", , acReadOnly
Set qItem = Nothing

'LATER YOU'LL HAVE TO DELETE THE QUERYDEF

DoCmd.DeleteObject acQuery, "query name"

Outstanding! That works great... thanks for the help!

The one question I have is where to put "db.TableDefs.Refresh"

Private Sub cmdRunQuery_Click()
On Error GoTo HandleErr
Dim db As DAO.Database
Dim qItem As QueryDef
DoCmd.DeleteObject acQuery, "qry_" & mdbName
Set db = CurrentDb
db.TableDefs.Refresh '<<==== should the be here, or after the next

line?

You probably meant 'db.QueryDefs.Refresh'

Add it after the next line

Set qItem = db.CreateQueryDef("qry_" & mdbName, Me!SqlString)
db.QueryDefs.Refresh
And if you're using this to run adhoc queries, you might want to let the
user enter and open multiple queries from a form, rather than just one at a
time. To do that, I once created a solution that kept track of how many
adhocs I had open by using a hidden field on a form, incrementing it by 1
for each new query, and appending the sequential number to the next new
query name.

qry_1
qry_2
....

When the form closed, I iterated through all the queries and deleted those
that began with qry_


DoCmd.OpenQuery "qry_" & mdbName, , acReadOnly
Exit_Here:
Set db = Nothing
Set qItem = Nothing
DoCmd.Close acForm, Me.Form.Name
Exit Sub
HandleErr:
Select Case Err.Number
Case 7874 'Microsoft Office Access can't find the object 'qry_...'
Resume Next
Case Else
modHandler.LogErr (Me.Form.Name & "(cmdRunQuery_Click)")
Resume Exit_Here
End Select
End Sub

[deko]

> And if you're using this to run adhoc queries, you might want to let the

user enter and open multiple queries from a form, rather than just one at a time. To do that, I once created a solution that kept track of how many
adhocs I had open by using a hidden field on a form, incrementing it by 1
for each new query, and appending the sequential number to the next new
query name.

not a bad idea....

Another, but similar, issue:

I was wondering if you could help me figure out why I'm getting "Type
Mismatch" and "Item not found in this collection" errors in this sub:

Private Sub cmdDeleteObj_Click()
Dim db As Database
'Dim dbe As DAO.Database
Dim tdf As DAO.TableDef
Dim strT As String
Dim intL As Integer
Dim intCt As Integer
Set db = CurrentDb
'Set tdf = db.TableDefs '<<== "Type Mismatch"
'Set dbe = DBEngine(0) '<<== should I be using this? what is
DBEngine(0), anyway?
db.TableDefs.Refresh
intCt = db.TableDefs.Count
Debug.Print intCt
For intL = intCt To 0 Step -1
strT = db.TableDefs(intL).Name '<<== "Item not found in this
collection"
Debug.Print strT
'DoCmd.DeleteObject acTable, strT
Next intL
End Sub

Thanks!

[Pieter Linden]

> Another, but similar, issue:

I was wondering if you could help me figure out why I'm getting "Type
Mismatch" and "Item not found in this collection" errors in this sub:

Private Sub cmdDeleteObj_Click()
Dim db As Database
'Dim dbe As DAO.Database
Dim tdf As DAO.TableDef
Dim strT As String
Dim intL As Integer
Dim intCt As Integer
Set db = CurrentDb
'Set tdf = db.TableDefs '<<== "Type Mismatch"
'Set dbe = DBEngine(0) '<<== should I be using this? what is
DBEngine(0), anyway?
db.TableDefs.Refresh
intCt = db.TableDefs.Count
Debug.Print intCt
For intL = intCt To 0 Step -1
strT = db.TableDefs(intL).Name '<<== "Item not found in this
collection"
Debug.Print strT
'DoCmd.DeleteObject acTable, strT
Next intL
End Sub

Thanks!

your statement
Set tdf = db.TableDefs
tdf is a table, and TableDefs is a collection (ALL the tables in the
database). NOT the same thing.
Why are you refreshing the tabledefs collection if you have not added
to or deleted from it yet?
Also, if you're deleting a tabledef, all you need to do is

tdf.Delete

So back up a step and explain what you're trying to do.

[deko]

> Set tdf = db.TableDefs

tdf is a table, and TableDefs is a collection (ALL the tables in the
database). NOT the same thing.
Why are you refreshing the tabledefs collection if you have not added
to or deleted from it yet?
Also, if you're deleting a tabledef, all you need to do is
tdf.Delete

Hi and thanks for the reply...

I'm trying to find a way to loop through all items in tabledefs (and/or
querydefs) and delete those tables/queries of my choosing - all queries or
tables that have the suffix "_temp", for example.

I thought this would be a good way to go:

intCt = db.TableDefs.Count
For intL = intCt To 0 Step -1
strT = db.TableDefs(intL).Name
DoCmd.DeleteObject acTable, strT
Next intL

Where I'm confused is finding a way to walk the tabledefs collection, and
then matching the item I want to delete.

as for tdf.Delete - would that delete only one table? would I use:

Set tdf = strTableName
tdf.Delete

?

[Pieter Linden]

> I'm trying to find a way to loop through all items in tabledefs (and/or

querydefs) and delete those tables/queries of my choosing - all queries or
tables that have the suffix "_temp", for example.

I thought this would be a good way to go:

intCt = db.TableDefs.Count
For intL = intCt To 0 Step -1
strT = db.TableDefs(intL).Name
DoCmd.DeleteObject acTable, strT
Next intL

Where I'm confused is finding a way to walk the tabledefs collection, and
then matching the item I want to delete.

as for tdf.Delete - would that delete only one table? would I use:

You can loop through the tables collection...

Sub DeleteSomeTables()

dim dbs as dao.database
dim tdf as dao.tabledef

set db=currentdb
for each tdf in db.TableDefs
if Right$(tdf.Name,5)="_Temp" Then
tdf.Delete
end if
next tdf
db.tabledefs.refresh

set db=nothing

If you wanted to do the same for queries,
dim qdf as dao.querydef and then the QueryDefs collection instead of
the tabledefs collection.

[deko]

> That's about what I have in mine along with the big red disclaimer

about users doing this at their own risk, etc. although I don't
usually let users have access to that.

Now that I've got it working, I'm thinking about the consequences...

Is there a way to identify the type of query the user has created? Standard
select queries are okay, but I want to prevent any type of action query from
running.

I could do something like this:

If InStr(1, Me!SqlString, "delete", vbTextCompare) Then Exit Sub

But I was thinking something like this:

Set qItem = db.CreateQueryDef("qryTemp", Me!SqlString)
If qItem = ActionQuery Then Exit Sub

Can queries can be identified by type? Or is there a better way to prevent
bad things from happening?

thx...

[deko]

> for each tdf in db.TableDefs

if Right$(tdf.Name,5)="_Temp" Then
tdf.Delete '<<== error here
end if
next tdf

sounds good, but - I get a Compile error: Method or data member not found -
I've noticed that IntelliSence does not show "Delete" as an option for
"tdf."

perhaps I should get the name with tdf.Name and use:

strT = tdf.Name
DoCmd.DeleteObject acTable, strT ?

[Pachydermitis]

Here is a couple alternatives.
if you don't like RunSQL try

for DAO (ac 97)
strSql = Me!txtBox
currentdb.execute strSql

for ADO (ac 2000+)
currentproject.connection.execute strSql

if you don't like having to clean up your queries but like using Querydefs
leave the name blank
Set qItem = db.CreateQueryDef("", strSql)
qItem.execute

if you want the query to remain, but just want to change the guts of it.
e.g. query name is MyTestQuery.
currentdb.querydefs("MyTestQuery").sql=strSql
to execute it
currentdb.querydefs("MyTestQuery").execute
docmd.openquery "MyTestQuery"

HTH
Pachydermitis
"deko" <dj****@hotmail.com> wrote in message news:<Lb******************@newssvr25.news.prodigy. com>...

Is there a "best practices" way to run ad hoc SQL queries?

Private cmdRun_Click()
strSql = Me!txtBox
DoCmd.RunSQL strSql
End Sub
???
Does access have a built-in way to bring up a window for this purpose?

[Mike Storr]

If you are creating a QueryDef from the entered string, then after doing so
(and before running it) you can check it's Type property. You could get
these possible results...

dbQAction = Action
dbQAppend = Append
dbQCompound = Compound
dbQCrosstab = Crosstab
dbQDDL = Data-definition
dbQDelete = Delete
dbQMakeTable = Make-table
dbQProcedure = Procedure (ODBCDirect workspaces only)
dbQSelect = Select
dbQSetOperation = Union
dbQSPTBulk = Used with dbQSQLPassThrough to specify a query that doesn't
return records (Microsoft Jet workspaces only).
dbQSQLPassThrough = Pass-through (Microsoft Jet workspaces only)
dbQUpdate

Use a Select Case to pick and choose wich types you want to allow...

Select Case qItem.Type
Case dbQSelect, dbQSQLPassThrough
..do this...
case else
...do this...
End Select

Also not, that if you do not give the QueryDef a name, it will only be
created as a temporary one, and will not be saved (show on Querys tab).

Mike Storr
www.veraccess.com

"deko" <dj****@hotmail.com> wrote in message
news:gB****************@newssvr29.news.prodigy.com ...

That's about what I have in mine along with the big red disclaimer
about users doing this at their own risk, etc. although I don't
usually let users have access to that.

Now that I've got it working, I'm thinking about the consequences...

Is there a way to identify the type of query the user has created?

Standard select queries are okay, but I want to prevent any type of action query from running.

I could do something like this:

If InStr(1, Me!SqlString, "delete", vbTextCompare) Then Exit Sub

But I was thinking something like this:

Set qItem = db.CreateQueryDef("qryTemp", Me!SqlString)
If qItem = ActionQuery Then Exit Sub

Can queries can be identified by type? Or is there a better way to prevent bad things from happening?

thx...

[deko]

> Select Case qItem.Type

Case dbQSelect, dbQSQLPassThrough
..do this...
case else
...do this...
End Select

cool!

I was trying this, but apparently I'm not referencing the Function
correctly...

Private Sub cmdRunQuery_Click()
[code omitted]

Set qItem = db.CreateQueryDef("qryTemp", Me!SqlString)
db.QueryDefs.Refresh

SafeQry qItem
If (SafeQry) = False Then Exit Sub '<<== error here - "argument
required"

[code omitted]
End Sub
'allow only qItem.Types that I define as safe

Private Function SafeQry (qItem As QueryDefs) As Boolean
Select Case qItem.Type
Case dbQAction, _
dbQAppend, _
dbQCompound, _
dbQCrosstab, _
dbQDDL, _
dbQDelete, _
dbQMakeTable, _
dbQProcedure, _
'dbQSelect, _
dbQSetOperation, _
dbQSPTBulk, _
dbQSQLPassThrough, _
dbQUpdate
Safe Qry = False
Case Else
SafeQry = True
End Select
End Function

[deko]

Got it figured out. But I'm wondering what kind of havoc can be caused with
Update queries. If all that can be done is changing records in tables,
that's okay... unless there are any hidden system tables I need to worry
about.

SafeQry qItem
If SafeQry(qItem) = False Then
MsgBox "Only Select and Update queries allowed.", vbInformation, " Query
Not Allowed"
Exit Sub
End If

Private Function SafeQry(qItem As QueryDef) As Boolean
Select Case qItem.Type
Case dbQAction, _
dbQAppend, _
dbQCompound, _
dbQCrosstab, _
dbQDDL, _
dbQDelete, _
dbQMakeTable, _
dbQProcedure, _
dbQSetOperation, _
dbQSPTBulk, _
dbQSQLPassThrough
SafeQry = False
Case dbQSelect, dbQUpdate
SafeQry = True
End Select
End Function