Results 1 to 7 of 7
  1. #1
    tgall is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Location
    na
    Posts
    38

    How can I import ColumnHistory into a new Access shell?

    My group is using Microsoft's Call Details template. The template corrupted, so we downloaded a fresh template and imported the database objects. Voilą! No more anomalies. However, the Comments history (data type = Memo; Append Only = Yes) did not immigrate. How can I import the ColumnHistory?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I have never used ColumnHistory before so I had to look it up. It seems this data would be stored in the System tables and if it is, it will be in your old broken database file.

    How are your retrieving the ColumnHistory? Are you using VBA code?

  3. #3
    tgall is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Location
    na
    Posts
    38
    Hi, "ItsMe"
    Not sure what you mean by "retrieving ColumnHistory", so I'll answer a couple of ways:
    -- If you're referring to a working database, I "see" the history in a History field on a form.
    The History field's control source is a lovely {said with tongue in cheek) piece of function code: =ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0)).
    I cannot enter data into the History field. Nor can I modify data in the History field.
    The "mother" field is Comments, which is a memo field in a table.
    The scenario is: I enter commentA in the Comments field on the form; commentA appears in both the Comments field and the History field. I close the record.
    When I open the record again and enter commentB in the Comments field, it wipes out commentA in the Comments field, but commentA still appears in the History field.
    I can only retrieve the last comment entered via the Comments field in the table. I cannot retrieve the previous comments.
    -- If you're referring to how I'm trying to import the data, I'm using the good ol' Import and Link functions in the External Data tab.

    If I could find the table where the history comments were stored, I could import it.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by tgall View Post
    ...

    If I could find the table where the history comments were stored, I could import it.
    Yes, it may be available in the system tables. The system tables are used by Access to keep track of stuff. They are not meant to be opened and or edited. Therfore, they are hidden objects.

    Because of this ...
    =ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0)).

    I believe there is a good chance you can find what you are looking for in the old DB's system tables. Once retrieved, you should be able to update your new DB. However, I would not attempt to update the new DB's system tables. Instead, I would devise a way to update the original Comments field that is memo type.

    The first step of retrieving the ColumnHistory would be to make a copy of the broken old DB file. Then, create a new blank DB. You will want to display the system tables in your new blank DB. Right click the top of the Navigation Pane and select 'Navigation Options'. Within the Navigation Options dialog box, tick 'Show Hidden Objects' and tick 'Show System Objects'. Click OK to close the dialog box and save your changes.

    I do not recall if you need to restart Access or not in order to see the hidden stuff.

    With all of that, you should be able to use the Import Wizard to import all of the system tables from the copy you made of the broken DB. You might try and isolate which table contains the data you desire. One approach might be to create a second blank DB and create a dummy table. In the table, add a memo field and set the appropriate options. Then type a few memorable phrases. Go to the system tables and open them, looking for the memorable text.

    I think this paints a big enough picture how I would approach this. Post your additional questions here.

  5. #5
    tgall is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Location
    na
    Posts
    38
    "ItsMe", it WORKED! Just finished opening the corrupted database's hidden and system objects, called a new Access shell, imported all objects, opened the new database, and the history appeared. Thank you! Now, how do I label this thread as solved?

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by tgall View Post
    ...Now, how do I label this thread as solved?
    Top of the thread there is a little pulldown, 'Thread Tools'.

    Glad everything worked out OK and thanks for posting the solution. Oh, and welcome to the forum.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    By chance I answered a very similar question today at another forum. You may find it useful.
    See https://www.access-programmers.co.uk...6&postcount=13

    Hope it helps
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calling Microsoft Power Shell from within Access
    By crowegreg in forum Programming
    Replies: 1
    Last Post: 11-18-2014, 02:59 PM
  2. Run Database while hiding Access Shell
    By Sarah88 in forum Programming
    Replies: 1
    Last Post: 08-19-2014, 10:11 AM
  3. Replies: 3
    Last Post: 01-07-2014, 09:57 AM
  4. Replies: 8
    Last Post: 02-05-2013, 02:36 PM
  5. Replies: 0
    Last Post: 01-19-2011, 04:20 PM

Tags for this Thread

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