CreateQueryDef

  • Thread starter Thread starter JimP
  • Start date Start date
J

JimP

I've created A QueryDef and saved it using a name which I subsequently link
to in another QueryDef - no problem yet. I would like use an empty string
for the name in the first QueryDef, but don't know how to reference it.

Is this possible, or do I need to name it?
 
if you just need to get a recordset from it - you don't need to save it:

Set qryd = CurrentDb.CreateQueryDef("")
qryd.SQL = "Select * from ..."
qryd.ReturnsRecords = True
set rst = qryd.OpenRecordset()
qryd.Close
Set qryd = Nothing

but if you want to refer to it in other query - you have to save it

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
 
Hi Jim,

can you explain better please?

use an empty string for the name of the query? -- not allowed

for the name of a column? -- not allowed -- use "_" instead

use an empty string for comparing criteria? for display in the field cell?


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
I was hoping to use an empty string for the name of the first QueryDef to
make it temporary. The QueryDef is used as a parameter in a second QueryDef.

This is a multi-user app and I was concerned that using a saved query could
cause a potential conflict/error - could probably find a work around though.
 
MakeQuery
---

Hi Jim,

In a multi-user application, each user should have their own front-end
(FE) and they should all be linked to the same back-end (BE) tables
(Access 97 was more stable for multiple users in the same FE)

If you are concerned with creating a unique name for the temporary
query, do this:

assign the name to a temporary variable
check for the existance of that querydef
if it exists, use a counter to modify the name and then check again
when you get a name that is unique, use it

.... but
as I said, each person should have their own FE. Name the temporary
query starting with a tilde ~ and use the same name each time for that
action

To assign SQL to your query, you can call upon a general procedure.
This code goes into a general module:

'~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 6-29-06
'crystal
'strive4peace2006 at yahoo dot com

On Error GoTo Proc_Err

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
...thank you.

strive4peace said:
MakeQuery
---

Hi Jim,

In a multi-user application, each user should have their own front-end
(FE) and they should all be linked to the same back-end (BE) tables
(Access 97 was more stable for multiple users in the same FE)

If you are concerned with creating a unique name for the temporary query,
do this:

assign the name to a temporary variable
check for the existance of that querydef
if it exists, use a counter to modify the name and then check again
when you get a name that is unique, use it

... but
as I said, each person should have their own FE. Name the temporary query
starting with a tilde ~ and use the same name each time for that action

To assign SQL to your query, you can call upon a general procedure. This
code goes into a general module:

'~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 6-29-06
'crystal
'strive4peace2006 at yahoo dot com

On Error GoTo Proc_Err

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


I was hoping to use an empty string for the name of the first QueryDef
to make it temporary. The QueryDef is used as a parameter in a second
QueryDef.

This is a multi-user app and I was concerned that using a saved query
could cause a potential conflict/error - could probably find a work
around though.
Click to expand...
Click to expand...
 
you're welcome, Jim ;) happy to help

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


..thank you.

strive4peace said:
MakeQuery
---

Hi Jim,

In a multi-user application, each user should have their own front-end
(FE) and they should all be linked to the same back-end (BE) tables
(Access 97 was more stable for multiple users in the same FE)

If you are concerned with creating a unique name for the temporary query,
do this:

assign the name to a temporary variable
check for the existance of that querydef
if it exists, use a counter to modify the name and then check again
when you get a name that is unique, use it

... but
as I said, each person should have their own FE. Name the temporary query
starting with a tilde ~ and use the same name each time for that action

To assign SQL to your query, you can call upon a general procedure. This
code goes into a general module:

'~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 6-29-06
'crystal
'strive4peace2006 at yahoo dot com

On Error GoTo Proc_Err

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


I was hoping to use an empty string for the name of the first QueryDef
to make it temporary. The QueryDef is used as a parameter in a second
QueryDef.

This is a multi-user app and I was concerned that using a saved query
could cause a potential conflict/error - could probably find a work
around though.


Hi Jim,

can you explain better please?

use an empty string for the name of the query? -- not allowed

for the name of a column? -- not allowed -- use "_" instead

use an empty string for comparing criteria? for display in the field
cell?


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



JimP wrote:
I've created A QueryDef and saved it using a name which I subsequently
link to in another QueryDef - no problem yet. I would like use an empty
string for the name in the first QueryDef, but don't know how to
reference it.

Is this possible, or do I need to name it?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top