Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans was able to point me in the right direction yesterday for part of what I'm trying to do with compiling several daily reports into a single table. I have two addtional parts that I need help on if anyone has any suggestions. The whole story of what I'm trying to do is this. I have several daily reports that need to have loaded into my database. The reports are pre-formatted using and excel macro to get the data into a table type of format. From there I want to find the best way to load this data into a single table.

    Hans helped me with the DoCmd.Transfer Spreadsheet option which seems to work good for getting the data into access from excel. Next I need to do two things. The first is I have a table I'm using to load this data to. Is it better to create a temporary table of some sort to load this data to or use the table I already have. The reason I ask this question is before I can transfer the excel data to the table. I first need to remove all the data from the table to be sure that when I append it to the primary table. I'm only appending one days (1 daily report) worth of data. So my first questions is either how can I create a temporary table that is removeable through VBA after I append the data to the primary table or how can I clear all data in table I already have so I can continue to reuse it for this purpose. I'm thinking the re-using option is probably best.

    The 2nd question is once I have the new data loaded into my temporary table. I need a way to validate it. The primary table and the data being loaded both have a column called Report_Date. This is a date I can use to say, if the data in the temporary table has a date of let's say 4/21/09, make sure before appending this data into the primary table that this date does not exist in the primary table's Report_Date column. If it does, abort the append and clear the data from the table that contains the temporary data being loaded to the primary table. Any help with these two questions is greatly appreciated. I'm not set in stone on how this all should work and certainly open to suggestions anybody has on how to best make it work. I could also use some help in the area of the best way to append the data. I know how to set up and append query but wasn't sure if there are other suggestions available.

    Again, anyhelp with these areas is greatly appreciated. Thanks so much!!

  2. #2
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'll answer the easy question first - the SQL for completely clearing a table is DELETE * FROM [tablename] - so from VBA you'd do:
    DoCmd.RunSQL("DELETE * FROM myTable")

    Regarding the second question, something like:

    SELECT * FROM tempTable JOIN primaryTable ON tempTable.ReportDate = primaryTable.ReportDate

    should return any records that have a report date that appears in both tables. You could either save this as a query in your database, or generate it on the fly using recordsets and the like - but the former is easier to get to grips with!

    Hope this helps
    Waggers
    If at first you do succeed, you've probably missed something.

  3. #3
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Waggers' post='771537' date='21-Apr-2009 08:56']I'll answer the easy question first - the SQL for completely clearing a table is DELETE * FROM [tablename] - so from VBA you'd do:
    DoCmd.RunSQL("DELETE * FROM myTable")

    Regarding the second question, something like:

    SELECT * FROM tempTable JOIN primaryTable ON tempTable.ReportDate = primaryTable.ReportDate

    should return any records that have a report date that appears in both tables. You could either save this as a query in your database, or generate it on the fly using recordsets and the like - but the former is easier to get to grips with!

    Hope this helps[/quote]


    Thanks for the information. The delete command seems to be what I'm looking for clearing the table. Is there a way to turn off the alert that I'm removing the data. I'm more familiar with doing this in excel and the coding I'm used to in excel doesn't seem to apply in access. I would have thought it would have been something like Application.DisplayAlerts = False, but does not seem to show in the list when I start typing it.

    On the 2nd part you mentioned. Since I'm not that familiar with writing in SQL yet. I think I'm a little unclear on what your 2nd SQL statement is saying. In case I wasn't clear on my intentions. Let me try to explain in a different way.

    1st I'll clear data from my temp table using the Delete SQL command you suggested.
    2nd I'll transfer the data from my excel sheet to the temp table called tbl_PCRI

    3rd I'll need to query my primary table called tbl_PC to see if the column Report_Date contains the date listed in the tbl_PCRI column Report_date.

    How can I use the select statement you used above to arrive at a true it does exist in the table or false it does not exist.

    Thanks again for your help with this.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'd create a delete query for the 1st step, an append query for the 2nd step and a select query for the 3rd step.

    You can run a query from VBA using DoCmd.OpenQuery.

    To suppress warnings, use

    DoCmd.SetWarnings False

    and to display them again, use

    DoCmd.SetWarnings True

    (These are the Access equivalents of Application.DisplayAlerts = False / True)

  5. #5
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='771579' date='21-Apr-2009 12:48']I'd create a delete query for the 1st step, an append query for the 2nd step and a select query for the 3rd step.

    You can run a query from VBA using DoCmd.OpenQuery.

    To suppress warnings, use

    DoCmd.SetWarnings False

    and to display them again, use

    DoCmd.SetWarnings True

    (These are the Access equivalents of Application.DisplayAlerts = False / True)[/quote]


    Thanks Hans. I appreciate your help!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •