Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    DuggyLou is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    8

    Old Table Keeps Haunting Me

    I'm importing commitment of traders commodity data which is a full set all commodities and their weekly trader commitment data since 2006 all in one file. The data is updated weekly at the website. Also I'm importing daily price data. So my data plan is simply to import the data weekly. Run a query to combine and extract to the desired output data I want for my export. Then the next week I want to delete the original tables and import the new data from the website creating "fresh" tables from scratch from which I will then re run my query to get my output data. It worked perfectly the first week. But the next week I deleted all my tables, imported all new fresh data and re ran my query. For some reason the query only outputs the original data. It's as if it ran the query on my old data and not the new. I looked at all new tables and they do indeed have the updated fresh data. I'm just learning Access so any help would be appreciated.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Then the next week I want to delete the original tables and import the new data from the website creating "fresh" tables from scratch
    I have had issues with this sort of thing in the past, as it is tough to get the fields and indexes set-up exactly the way you want each time.
    I have had much greater success simply deleting all the data from the existing table with a line of SQL code like this:
    Code:
    DELETE [Table1].* FROM [Table1]
    and then using an Append Query to add all the new records to the table.

    That way the structure of your table never changes and you do not need to worry about how it is being set-up (and you only have one Table to worry about).

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    DuggyLou is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    8
    Ok, I tested it out and it seems to be working fine. Thank you. How and where do I implement the sql line you suggested rather than manually deleting the data?

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    There are various different ways, including:
    - You can run it using using the "RunSQL" command in a Macro or VBA
    - You can create a "Delete Query" to do this, and simply run this query (manually clicking it, or calling it from a Macro or VBA).

  6. #6
    DuggyLou is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    8
    Ok the delete query worked perfectly. Also I tried editing in visual basic it saved it as a module but it seems the only way to access it is in the VBA editor and running it from there. Is there a way to run the module directly via the navigation pane?

  7. #7
    DuggyLou is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    8
    Ok I've got it incorporated into a macro now that's working. Thanks for getting me started and helping me automate all this.

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome!

  9. #9
    DuggyLou is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    8
    I'm having a new problem now. My computer crashed while running my macro to import all my data text files. Now anytime I try to import any text file, even one that didn't exist previously in the database and regardless of import specification or no specification at all just importing using default settings I get an error " 'PrimaryKey' is not and index in this table" I tried "Compact and Restore" first I got an error, later it ran with no errors. I noticed that a new file popped up in my database folder "COT.laccdb" which says it's a record locking file. I tried deleting it but it wouldn't allow when Access was open. I closed Access and the file disappeared but reappears anytime I run Access. Anyway it seems like I'm stuck because I can't import my files. Any help?

  10. #10
    DuggyLou is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    8
    I'm afraid my database is corrupt. Worst case I can redo it and make sure I have a backup of it next time. It's just the several hours it would take to do that.

  11. #11
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If the database still exists, you may be able to salvage it without having to redo everything by doing the following:
    - Create a brand new blank database
    - Go to the Import ribbon, select Access, browse to the "corrupt" database, and elect to import all Objects from that old database into this new one.
    - Start using the new one.

    I noticed that a new file popped up in my database folder "COT.laccdb" which says it's a record locking file. I tried deleting it but it wouldn't allow when Access was open. I closed Access and the file disappeared but reappears anytime I run Access.
    Anytime you have a database open, a matching "locking" file will be placed out there. It disappears whenever the database is closed properly.

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    DuggyLou,

    Further to JoeM's advice, don't overlook Google or Bing to find the meaning/importance of some of these things.
    Youtube can also be a good reference.

    Good luck.

  13. #13
    DuggyLou is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    8
    Ok it looks like it brought everything back except for my saved imports and their specifications. I'll just redo those no problem. Thanks again!

  14. #14
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Ok it looks like it brought everything back except for my saved imports and their specifications. I'll just redo those no problem. Thanks again!
    Forgot to mention, you can import those too.

    Just click Access -> Import again, and click the Options button, It will then display other options fro you to import, like Relationships and Import/Expect Specs.
    You don't need to re-import everything again, you can just import those (and not re-import all your objects again).

  15. #15
    DuggyLou is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    8
    Wow! I didn't see that. It all works perfectly now. Thanks!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-31-2016, 03:33 AM
  2. Replies: 4
    Last Post: 08-30-2012, 07:58 PM
  3. Replies: 8
    Last Post: 03-22-2012, 08:48 AM
  4. Replies: 2
    Last Post: 08-01-2011, 11:35 PM
  5. Replies: 2
    Last Post: 10-27-2009, 07:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums