CreateQueryDef

[Guest]

I have the following code :

Dim strRecSource as String
Dim dbsDatabase01 As Database
Dim qdfTemp As QueryDef

strRecSource = " I create the string here"

Set dbsDatabase01 = OpenDatabase("C:\Database01.mdb")
With dbsDatabase01
Set qdfTemp = .CreateQueryDef("QueryExport", strRecSource)
DoCmd.TransferSpreadsheet acExport, 8, qdfTemp.Name, "C:\My
Documents\QueryExported.xls", True, ""
.QueryDefs.Delete qdfTemp.Name
.Close
End With

But on the TransferSpreadsheet it many times will tell me QueryExport does
not exists. Why would it do that? When I look in the Query tab it is there.

Thank you for your help.

Steven

 

[strive4peace]

MakeQuery
---

Hi Steven,

instead of deleting the query right after you create it (perhaps Access
is not done with it), why not define a general function to create a
query or replace its SQL if it already exists? Then do your
TransferSpreadsheet

'~~~~~~~~~~~~~~~~~~~~~~~~~~

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

'modified 6-29-06
'crystal
'strive4peace2007 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
*

 

[Guest]

Crystal,

You are super duper wonderful. Thank you.

Steven

 

[strive4peace]

you're welcome, Steven happy to help

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