By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,604 Members | 858 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,604 IT Pros & Developers. It's quick & easy.

Runtime Error 3010...table Already Exists

P: 98
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!!!

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmbSelectionDone_Click()
  2.     Dim db As DAO.Database
  3.     Dim qdf As DAO.QueryDef
  4.     Dim varItem As Variant
  5.     Dim strCriteria As String
  6.     Dim strSQL As String
  7.  
  8.     Set db = CurrentDb()
  9.     Set qdf = db.QueryDefs("qryMultiSelect")
  10.     If Me!cboxRangeName = -1 Then
  11.         myPath = txtFilePath & txtFileName
  12.         myFileName = Me!txtFileName
  13.         DoCmd.SetWarnings (False)
  14.         DoCmd.RunSQL "DELETE * FROM TempSymbol"
  15.         DoCmd.SetWarnings (True)
  16.         DoCmd.TransferSpreadsheet acImport, , "TempSymbol", "" & myPath & "", True, "" & txtRangeName & ""
  17.         MsgBox "The symbols have been successfully imported."
  18.         strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice, DailyPrice.PriceFlag " & _
  19.                 "FROM TempSymbol, DailyPrice " & _
  20.                 "WHERE (((DailyPrice.Symbol) = [TempSymbol]![Symbol])) " & _
  21.                 "ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate;"
  22.         qdf.SQL = strSQL
  23.         'check this out...maybe delete it later
  24.         qdf.Close
  25.         DoCmd.TransferSpreadsheet acExport, , "qryMultiSelect", "" & myPath & ""
  26.         MsgBox "The table have been successfully exported to " & myFileName & "."
  27.         Set db = Nothing
  28.         Set qdf = Nothing
  29.         Exit Sub
  30.     End If
  31.         'DoCmd.TransferSpreadsheet acExport, , "qryMultiSelect", "" & myPath & ""
  32.  
  33.     If IsNull(txtGetList) Then
  34.         For Each varItem In Me!lstSelectSymbol.ItemsSelected
  35.             strCriteria = strCriteria & "'" & Me!lstSelectSymbol.ItemData(varItem) & "',"
  36.         Next varItem
  37.  
  38.         If Len(strCriteria) = 0 Then
  39.             MsgBox "You did not select anything from the list", vbExclamation, "Nothing to find!"
  40.             Set db = Nothing
  41.             Set qdf = Nothing
  42.             Me!lstSelectSymbol.SetFocus
  43.             Exit Sub
  44.         End If
  45.  
  46.         strCriteria = Left(strCriteria, Len(strCriteria) - 1)
  47.         myPath = txtFilePath & txtFileName
  48.         myFileName = Me!txtFileName
  49.  
  50.         strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice, DailyPrice.PriceFlag FROM DailyPrice " & _
  51.                  "WHERE DailyPrice.Symbol IN (" & strCriteria & ") " & _
  52.                  "ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate;"
  53.  
  54.             qdf.SQL = strSQL
  55.             DoCmd.TransferSpreadsheet acExport, , "qryMultiSelect", "" & myPath & ""
  56.             MsgBox "The table have been successfully exported to " & myFileName & "."
  57.             qdf.Close
  58.             Set db = Nothing
  59.             Set qdf = Nothing
  60.             Exit Sub
  61.     End If
  62.     strCriteria = Me!txtGetList
  63.     myPath = txtFilePath & txtFileName
  64.     myFileName = Me!txtFileName
  65.  
  66.    ' On Error GoTo Do_Nothing
  67.  
  68.     strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice, DailyPrice.PriceFlag FROM DailyPrice " & _
  69.                  "WHERE DailyPrice.Symbol IN (" & strCriteria & ") " & _
  70.                  "ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate;"
  71.  
  72.     qdf.SQL = strSQL
  73.     DoCmd.OpenQuery "qryMultiSelect"
  74.     DoCmd.TransferSpreadsheet acExport, , "qryMultiSelect", "" & myPath & ""
  75.     MsgBox "The table have been successfully exported to " & myFileName & "."
  76.     qdf.Close
  77.     Set db = Nothing
  78.     Set qdf = Nothing
  79.  
  80. End Sub
Oct 23 '06 #1
Share this Question
Share on Google+
10 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
What line exactly is the code stopping at?
Oct 23 '06 #2

P: 98
The error is occuring at the first instance of:

DoCmd.TransferSpreadsheet acExport, , "qryMultiSelect", "" & myPath & ""
Oct 23 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
The error is occuring at the first instance of:

DoCmd.TransferSpreadsheet acExport, , "qryMultiSelect", "" & myPath & ""
Where are you exporting to?
Are you sending to another database?
Oct 23 '06 #4

P: 98
I am exporting to excel.
Oct 24 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Oct 24 '06 #6

NeoPa
Expert Mod 15k+
P: 31,419
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.
Oct 24 '06 #7

P: 1
Funny - I researched the help file because at my company I wrote a bunch of code and ran into the same thing. That damn exportspreadsheet, or TransferSpreadsheet 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.
Apr 16 '07 #8

Denburt
Expert 100+
P: 1,356
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:
Expand|Select|Wrap|Line Numbers
  1. qdf.SQL = strSQL
  2. 'check this out...maybe delete it later
  3. qdf.Close
  4. DoCmd.TransferSpreadsheet acExport, , "qryMultiSelect", "" & myPath & ""
  5.  
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.
Expand|Select|Wrap|Line Numbers
  1.  
  2. qdf.SQL = strSQL
  3. DoCmd.TransferSpreadsheet acExport, , "qryMultiSelect", "" & myPath & ""
  4. MsgBox "The table have been successfully exported to " & myFileName & "."
  5. qdf.Close
  6.  
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.
Apr 16 '07 #9

P: 1
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.
Sep 8 '09 #10

NeoPa
Expert Mod 15k+
P: 31,419
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 :)
Sep 9 '09 #11

Post your reply

Sign in to post your reply or Sign up for a free account.