VB works on 2007 not on 2003

[CB_DFW]

Anyone know why this would work fine in Access 2007 but not with Access 2003?

I get a Compile Error: Constant Expression required

I get it on this line

Set qdf = dbs.CreateQueryDef(strQName, strSQL)

It highlights "strQName"

Any help would be greatly appreciated.

Option Compare Database
Option Explicit
Public Sub sExportQuery()
 
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
strSQL = "SELECT DISTINCT DistrictID FROM Combined_EE;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
If rstMgr.EOF = False And rstMgr.BOF = False Then
      rstMgr.MoveFirst
      Do While rstMgr.EOF = False

            strMgr = DLookup("DistrictName", "DistrictTable", _
                  "DistrictID = " & rstMgr!DistrictID.Value)

            strSQL = "SELECT * FROM Combined_EE WHERE " & _
                  "DistrictID = " & rstMgr!DistrictID.Value & ";"
            Set qdf = dbs.QueryDefs(strTemp)
            qdf.Name = "q_" & strMgr
            strTemp = qdf.Name
            qdf.SQL = strSQL
            qdf.Close
            Set qdf = Nothing

            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
                  strTemp, "C:\" & strMgr & ".xls"
            rstMgr.MoveNext
      Loop
End If
rstMgr.Close
Set rstMgr = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
End Sub

 

[Guus2005]

Works fine in Acc2003.
Check the references.

or

Reboot.

 

[CB_DFW]

Not sure what you mean by references.

I have had my boss try it and it fails for him too. He is on 2003.

My coworker tried it and it worked fine.

I can't figure it out.

 

[boblarson]

Not sure what you mean by references.

See if this helps:
http://www.btabdevelopment.com/main...eckformissingreferences/tabid/71/Default.aspx

 

[CB_DFW]

Good instructions, thanks.

I followed them but it did not say I had any missing references.

What now?

 

[CB_DFW]

I have attached my DB. Maybe that will help.

 

[boblarson]

Okay, change this:

Const strQName As String = "zExportQuery"

to this:

Dim strQName As String
strQName = "zExportQuery"

 

[CB_DFW]

It works, it works. Awesome.

Thanks so much.