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.

[NeoPa]

These threads are more often used by the general public to find answers for their own problems, than they even are by the OP. In light of that we like to encourage solutions be posted to threads, even if they are a little old and crusty ;)

In short, thanks for posting :)