Runtime Error 3010...table Already Exists

[ineedahelp]

Can anyone help me figure out why I continue to get a runtime error '3010' "TABLE qryMultiSelect ALREADY EXISTS'? Here is my code. thank you in advance for any help!!!

Private Sub cmbSelectionDone_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
 
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryMultiSelect")
    If Me!cboxRangeName = -1 Then
        myPath = txtFilePath & txtFileName
        myFileName = Me!txtFileName
        DoCmd.SetWarnings (False)
        DoCmd.RunSQL "DELETE * FROM TempSymbol"
        DoCmd.SetWarnings (True)
        DoCmd.TransferSpreadsheet acImport, , "TempSymbol", "" & myPath & "", True, "" & txtRangeName & ""
        MsgBox "The symbols have been successfully imported."
        strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice, DailyPrice.PriceFlag " & _
                "FROM TempSymbol, DailyPrice " & _
                "WHERE (((DailyPrice.Symbol) = [TempSymbol]![Symbol])) " & _
                "ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate;"
        qdf.SQL = strSQL
        'check this out...maybe delete it later
        qdf.Close
        DoCmd.TransferSpreadsheet acExport, , "qryMultiSelect", "" & myPath & ""
        MsgBox "The table have been successfully exported to " & myFileName & "."
        Set db = Nothing
        Set qdf = Nothing
        Exit Sub
    End If
        'DoCmd.TransferSpreadsheet acExport, , "qryMultiSelect", "" & myPath & ""
 
    If IsNull(txtGetList) Then
        For Each varItem In Me!lstSelectSymbol.ItemsSelected
            strCriteria = strCriteria & "'" & Me!lstSelectSymbol.ItemData(varItem) & "',"
        Next varItem
 
        If Len(strCriteria) = 0 Then
            MsgBox "You did not select anything from the list", vbExclamation, "Nothing to find!"
            Set db = Nothing
            Set qdf = Nothing
            Me!lstSelectSymbol.SetFocus
            Exit Sub
        End If
 
        strCriteria = Left(strCriteria, Len(strCriteria) - 1)
        myPath = txtFilePath & txtFileName
        myFileName = Me!txtFileName
 
        strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice, DailyPrice.PriceFlag FROM DailyPrice " & _
                 "WHERE DailyPrice.Symbol IN (" & strCriteria & ") " & _
                 "ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate;"
 
            qdf.SQL = strSQL
            DoCmd.TransferSpreadsheet acExport, , "qryMultiSelect", "" & myPath & ""
            MsgBox "The table have been successfully exported to " & myFileName & "."
            qdf.Close
            Set db = Nothing
            Set qdf = Nothing
            Exit Sub
    End If
    strCriteria = Me!txtGetList
    myPath = txtFilePath & txtFileName
    myFileName = Me!txtFileName
 
   ' On Error GoTo Do_Nothing
 
    strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice, DailyPrice.PriceFlag FROM DailyPrice " & _
                 "WHERE DailyPrice.Symbol IN (" & strCriteria & ") " & _
                 "ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate;"
 
    qdf.SQL = strSQL
    DoCmd.OpenQuery "qryMultiSelect"
    DoCmd.TransferSpreadsheet acExport, , "qryMultiSelect", "" & myPath & ""
    MsgBox "The table have been successfully exported to " & myFileName & "."
    qdf.Close
    Set db = Nothing
    Set qdf = Nothing
 
End Sub

[MMcCarthy]

What line exactly is the code stopping at?

[ineedahelp]

The error is occuring at the first instance of:

DoCmd.TransferS preadsheet acExport, , "qryMultiSelect ", "" & myPath & ""

[MMcCarthy]

The error is occuring at the first instance of:

DoCmd.TransferS preadsheet acExport, , "qryMultiSelect ", "" & myPath & ""

Where are you exporting to?
Are you sending to another database?

[ineedahelp]

I am exporting to excel.

[MMcCarthy]

Although Access says Table already exists, it may be talking about the excel spreadsheet. If this is on a network you may be having problems overwriting it.

To test this theory

Change the path each time ( small change to the name would be enourgh) and see if you still get the error

[NeoPa]

Good idea.

I make it a policy always to attempt deletion of any resulting file before writing it, using On Error & all that stuff where required. That way I can see if it's a file problem rather than code, and also I can re-run the code as many times as I need without worrying that it will only work the first time.

[Universalmon]

Funny - I researched the help file because at my company I wrote a bunch of code and ran into the same thing. That damn exportspreadshe et, or TransferSpreads heet commands - or actually the one I amworking with, TransferText - all say that if the file exists it will over-write the file. NOT TRUE! If the file exists, you get a trappable error.

My solution is if you get the error, trap for the bitch on a if Err.Number = 3010 then **** delete the fuc**ng file using 'Kill <path>' ****. HELP FILE IS WRONG - those command do NOT OVER-WRITE FILE IF IF EXISTS. They get an error.

Cheers.

[Denburt]

O.K. First she asked which line of code and you posted a line of code that is used three or more times in your routine.

I did notice this in your first if statement you appear to have as so and it looks fine:

qdf.SQL = strSQL
'check this out...maybe delete it later
qdf.Close
DoCmd.TransferSpreadsheet acExport, , "qryMultiSelect", "" & myPath & ""
 

Then the following statements have it a little different which could and is probably why you are having this issue. I Transfer export spreadsheets and HTML all day long and it always overwrites the older one with no issues.

 
qdf.SQL = strSQL
DoCmd.TransferSpreadsheet acExport, , "qryMultiSelect", "" & myPath & ""
MsgBox "The table have been successfully exported to " & myFileName & "."
qdf.Close
 

I would close that QDF before you try to transfer it or you will not get the results you are looking for since the new qdf has not been completed.

[dberkheimer]

Since the original post is three years old, I'm going to go out on a limb and guess that he/she has moved on. Then again, they may work for the government...

Anyway, hopefully this will help somebody: The most likely explanation is that the output spreadsheet is open. If so, the easy fix is to simply close the spreadhseet, and re-run the code. If it's not such an obvious problem, it's probably similar; perhaps the sheet is in a read-only state for some reason, such as network permissions, e.g.