none
Access 2010 - Table Keeps Disappearing RRS feed

  • 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


    • Edited by Lorac1969 Wednesday, April 12, 2017 12:53 PM Spell check
    Wednesday, April 12, 2017 11:55 AM

All replies

  • 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.

    Wednesday, April 12, 2017 12:37 PM
  • 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

    Wednesday, April 12, 2017 12:51 PM
  • 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

    Wednesday, April 12, 2017 6:48 PM
  • 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

    Tuesday, April 18, 2017 7:55 PM
  • 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

    Tuesday, April 18, 2017 8:41 PM