Query SQL keeps getting erased

  • Thread starter Thread starter Jeremy Ames
  • Start date Start date
J

Jeremy Ames

I have several queries that I run multiple times from some VBA code. I run
the same query multiple times. On the majority of my queries, they will run
the first time and every time after that, I receive a message that says
"Query must have at least one destination field." When I look at the SQL in
the query, it is all gone. I have searched all over Technet and I haven't
been able to find anything on this problem. Can someone please help?
 
Please someone look at this. I need an answer to this pretty quickly.

I have several queries that I run multiple times from some VBA code. I run
the same query multiple times. On the majority of my queries, they will run
the first time and every time after that, I receive a message that says
"Query must have at least one destination field." When I look at the SQL in
the query, it is all gone. I have searched all over Technet and I haven't
been able to find anything on this problem. Can someone please help?
 
I have several queries that I run multiple times from some VBA code. I run
the same query multiple times. On the majority of my queries, they will run
the first time and every time after that, I receive a message that says
"Query must have at least one destination field." When I look at the SQL in
the query, it is all gone. I have searched all over Technet and I haven't
been able to find anything on this problem. Can someone please help?
Click to expand...

Care to post your code? Are you in fact using a CreateQuerydef with a
named query?
 
I am not using CreateQuerydef. The query already exists, I am running
through a list of users and exporting the query using the OutputTo method to
put the query into a spreadsheet. Here is the code:

Public Sub AdminExport(sPath As String, sQueryName As String, bEmail As
Boolean)
Dim cnConn As ADODB.Connection
Dim rsUsers As ADODB.Recordset
Dim sSql As String, sFile As String
Dim lPrgCnt As Long

Set cnConn = CurrentProject.Connection
Set rsUsers = New ADODB.Recordset

sSql = "SELECT AgentId FROM tblUser ORDER BY AgentId"

rsUsers.Open sSql, cnConn, adOpenStatic, adLockOptimistic

DoCmd.OpenForm "frmExporting"
Form_frmExporting.prgExport.Max = rsUsers.RecordCount

On Error GoTo ErrorHandler

Do Until rsUsers.EOF
' handle exporting functions
msAgentId = rsUsers(0)
Form_frmAdmin.txtAgentId = msAgentId
sFile = msAgentId & " - " & sQueryName & ".xls"

ExportFile sPath, sFile, sQueryName

' handle email functions
If bEmail Then
If Not IsNull(DLookup("EmailAddress", "tblUser", "AgentId = '" &
msAgentId & "'")) Then
EmailReport DLookup("EmailAddress", "tblUser", "AgentId = '"
& msAgentId & "'"), _
sPath & sFile, _
Form_frmAdmin.lstReports.Column(1)
Else
InsertExportError "User does not have email.", msAgentId, _
Form_frmAdmin.lstReports.Column(1)
mlErrCnt = mlErrCnt + 1
End If
End If
ResumePoint:
lPrgCnt = lPrgCnt + 1
Form_frmExporting.prgExport = lPrgCnt

rsUsers.MoveNext
Loop

rsUsers.Close
cnConn.Close

Set rsUsers = Nothing
Set cnConn = Nothing

If mlErrCnt > 0 Then Form_frmAdmin.cmdViewErrors.Visible = True

DoCmd.Close

Exit Sub

ErrorHandler:
mlErrCnt = mlErrCnt + 1
' report error to error table
InsertExportError Err.Description, msAgentId,
Form_frmAdmin.lstReports.Column(1)
GoTo ResumePoint
End Sub


I have several queries that I run multiple times from some VBA code. I run
the same query multiple times. On the majority of my queries, they will run
the first time and every time after that, I receive a message that says
"Query must have at least one destination field." When I look at the SQL in
the query, it is all gone. I have searched all over Technet and I haven't
been able to find anything on this problem. Can someone please help?
Click to expand...

Care to post your code? Are you in fact using a CreateQuerydef with a
named query?
 
Never seen anything like this, but it would probably help if you could post the
VBA code.

If the vba is straightforward, I would suspect that you have some corruption in
your database. In that case, MAKE a copy of your database RIGHT NOW to minimize
any further damage.

Once you have done that go to Tony Toews site
http://www.granite.ab.ca/access/corruptmdbs.htm
 
This is a brand new database. I actually started getting the problem during
development. I have posted the code previously in another reply.
 
I just realized that the code I listed here does not actually show the
import function, but a call to it. It is a really simple function so I will
just change it below to match.

I am not using CreateQuerydef. The query already exists, I am running
through a list of users and exporting the query using the OutputTo method to
put the query into a spreadsheet. Here is the code:

Public Sub AdminExport(sPath As String, sQueryName As String, bEmail As
Boolean)
Dim cnConn As ADODB.Connection
Dim rsUsers As ADODB.Recordset
Dim sSql As String, sFile As String
Dim lPrgCnt As Long

Set cnConn = CurrentProject.Connection
Set rsUsers = New ADODB.Recordset

sSql = "SELECT AgentId FROM tblUser ORDER BY AgentId"

rsUsers.Open sSql, cnConn, adOpenStatic, adLockOptimistic

DoCmd.OpenForm "frmExporting"
Form_frmExporting.prgExport.Max = rsUsers.RecordCount

On Error GoTo ErrorHandler

Do Until rsUsers.EOF
' handle exporting functions
msAgentId = rsUsers(0)
Form_frmAdmin.txtAgentId = msAgentId
sFile = msAgentId & " - " & sQueryName & ".xls"

'***************************************
'original function call
'***************************************
'ExportFile sPath, sFile, sQueryName

'***************************************
' Code from the ExportFile function
'***************************************
DoCmd.OutputTo AcOutputQuery, _
sQueryName, _
"MicrosoftExcel 97-2002(*.xls)", _
sPath & sFile

' handle email functions
If bEmail Then
If Not IsNull(DLookup("EmailAddress", "tblUser", "AgentId = '" &
msAgentId & "'")) Then
EmailReport DLookup("EmailAddress", "tblUser", "AgentId = '"
& msAgentId & "'"), _
sPath & sFile, _
Form_frmAdmin.lstReports.Column(1)
Else
InsertExportError "User does not have email.", msAgentId, _
Form_frmAdmin.lstReports.Column(1)
mlErrCnt = mlErrCnt + 1
End If
End If
ResumePoint:
lPrgCnt = lPrgCnt + 1
Form_frmExporting.prgExport = lPrgCnt

rsUsers.MoveNext
Loop

rsUsers.Close
cnConn.Close

Set rsUsers = Nothing
Set cnConn = Nothing

If mlErrCnt > 0 Then Form_frmAdmin.cmdViewErrors.Visible = True

DoCmd.Close

Exit Sub

ErrorHandler:
mlErrCnt = mlErrCnt + 1
' report error to error table
InsertExportError Err.Description, msAgentId,
Form_frmAdmin.lstReports.Column(1)
GoTo ResumePoint
End Sub


I have several queries that I run multiple times from some VBA code. I run
the same query multiple times. On the majority of my queries, they will run
the first time and every time after that, I receive a message that says
"Query must have at least one destination field." When I look at the SQL in
the query, it is all gone. I have searched all over Technet and I haven't
been able to find anything on this problem. Can someone please help?
Click to expand...

Care to post your code? Are you in fact using a CreateQuerydef with a
named query?
 
So, are you saying that the following code works properly the first time,
then fails with a runtime error: "Query must have at least one destination
field" on each subsequent call?
DoCmd.OutputTo AcOutputQuery, _
sQueryName, _
"MicrosoftExcel 97-2002(*.xls)", _
sPath & sFile
Click to expand...

If so, here's what I'd do.

- Check the OutputTo parameters (I don't have Access here to check);

- Debug.print sQueryName, to make certain that you are running the query you
think you are running;

- Put the following code immediately before, and after, the DoCmd statement,
to see if it is changing the query SQL:
dim db as database
set db = currentdb()
debug.print db.querydefs(sQueryName).sql
set db = nothing

That should give you more info. to go on.

HTH,
TC
 
I am not using CreateQuerydef. The query already exists, I am running
through a list of users and exporting the query using the OutputTo method to
put the query into a spreadsheet. Here is the code:
Click to expand...

What query is getting deleted? You're using the variable sQuery but I
didn't see where you defined it - what is its value?
 
Back
Top