Access 2010 - Table Keeps Disappearing

Question

I have an Access 2010 database where one table keeps disappearing. I have a module which deletes the prior days detail from three tables then uploads the detail onto two tables, finally the detail is merged and pasted onto a table with all of the detail. Based on what I've Googled, most people suggest a disappearing table is due to an object being hidden, but I updated my options to show hiddent objects and the table still disappeared. Is there something in my module which might explain why the "Daily_GalaxyDisc" table keeps disappearing? The other two tables have never disappeared. PS when I re-create the missing table, I get an error message that indicates the "wizard" is missing and tells me to check my add-ins. I check my add-ins and everything seems fine. Is there a particular add-in I should look for?

Function UpdateAndReport()
   
    CurrentDb.Execute "Delete * From Daily_GalaxyDisc"<<<<<<<<<<table that disappears
    CurrentDb.Execute "Delete * From Daily_GalaxyTrans"
    CurrentDb.Execute "Delete * From Daily_GalaxyAll"
    CurrentDb.Execute ("drop table Daily_GalaxyDisc")
    DoCmd.TransferSpreadsheet acImport, 9, "Daily_GalaxyDisc", "\\path\PATH-Mercury\ACS Mgmt Reports\End of Day Reports\BD EOD\End of Day - " & Format(Date - 1, """Y""YYYY ""M""MM ""D""DD") & " SRs.xlsb", True, "Discrepancy!"
    CurrentDb.Execute ("drop table Daily_GalaxyTrans")
    DoCmd.TransferSpreadsheet acImport, 9, "Daily_GalaxyTrans", "\\path\PATH-Mercury\ACS Mgmt Reports\End of Day Reports\BD EOD\End of Day - " & Format(Date - 1, """Y""YYYY ""M""MM ""D""DD") & " SRs.xlsb", True, "Transaction!"
         
    x = 0
    On Error GoTo ErrorHandler
  
    CurrentDb.Execute "Append_GalaxyDisc"
    CurrentDb.Execute "Append_GalaxyTrans"
    DoCmd.RunSavedImportExport "Export-Daily_GalaxyAll"
    Name "C:\Auto Reports\BD_EOD\Daily_GalaxyAll.xlsx" As "C:\Auto Reports\BD_EOD\Daily_Galaxy " & Format(Now(), "yyyy-mm-dd") & ".xlsx"
   
     
    Send_DailyGalaxy_Email
   
    Exit Function
ErrorHandler:
    x = x + 1
    If x > 10 Then On Error GoTo 0
    Resume

---

Lorac1969

Answer 1

Function UpdateAndReport()
   
    CurrentDb.Execute "Delete * From Daily_GalaxyDisc"<<<<<<<<<<table that dissapears
    CurrentDb.Execute "Delete * From Daily_GalaxyTrans"
    CurrentDb.Execute "Delete * From Daily_GalaxyAll"
    CurrentDb.Execute ("drop table Daily_GalaxyDisc")
    ....

Hi Lorac,

Is the 4th line:  "drop table Daily_GalaxyDisc"  the one that is bothering you?

Imb.

Answer 2

Hi Imb,

I'm not sure if that's the line, if you look down two lines, the same code is used for the Daily_GalaxyTrans. This is what puzzles me, I’m using identical code to pull over Transaction data, but it’s only the Discrepancy table I’m having an issue with.

---

Lorac1969

Answer 3

In each case you are deleting the table ("drop table") and recreating it via the following import ("DoCmd.TransferSpreadsheet").  I'd guess that the import is failing for some reason, and that is what you ought to be investigating.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 4

Thanks for the tip. I don't mean to be lazy but can you suggest a better method of copying daily files? I would want all of the prior days detail to be deleted from the DB tables (to avoid duplication). I've looked online, but you get so many results, most of which don't work.

---

Lorac1969

Answer 5

Thanks for the tip. I don't mean to be lazy but can you suggest a better method of copying daily files? I would want all of the prior days detail to be deleted from the DB tables (to avoid duplication). I've looked online, but you get so many results, most of which don't work.

I wouldn't drop the target table every time.  Instead, I'd first verify that the spreadsheet file to be imported exists, then delete all records from the target table -- which your code is already doing -- then import the spreadsheet into the table.  That is, assuming the source spreadsheet for a given target table always has the same fields.  Optionally, one could link to the spreadsheet instead, and first examine it to validate the contents, and then (if all checks out) execute an append query to place the data into the target table.  That would allow you to check whether you've already imported this data.

I gather that you follow this daily import by appending the daily data into a cumulative table that has multiple days' data in it.  You may also want to verify, before beginning a daily import or before appending the daily data to the cumulative table, whether that day's data has already been appended to the cumulative table.  If it has, you might offer the user a choice to either abort the import, or delete the existing data for that day and reimport it.

Since you're doing this for several different tables, I'd put all the relevant code into a Sub or Function, and then call that procedure each time with the appropriate arguments.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html