Results 1 to 6 of 6
  1. #1
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107

    most efficient way to upgrade table database from .mdb to .accdb format

    We have a database on our network that contains a lot of our miscellaneous Access tables that are used by multiple other databases. We originally created this database in .mdb format so it could be used by workstations that had versions of Office older than 2010. We have since upgraded all of our workstations to 2010 or later. We would like to begin auditing some of these tables in this database. After much comparison research we have decided the most accurate and all-encompassing way to do this is to use table event macros. In order to do this, we have to upgrade this database to .accdb format because table event macros aren't available in versions old than 2010. I believe this will force me to relink all of my tables in all of my other databases, unless someone can point out an easier way...

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    new versions of access read .mdb just fine.
    I dont see a need to upgrade. (unless you like extra work)

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I've investigated table event macros and you really need to know what you are doing - there is no means to document as such and when experimenting I found a number of issues where you want something slightly different on one update to another - starts to make the macro more complex. But if you are going this route then correct, you will need to not just relink but delete the existing linked table and create a new one.

    You can do this in VBA looping through either the tabledefs collection or probably safer, with a snapshot of the msysobjects table.

    You can also extend this code to loop through all your front end masters, providing they are easily collectable (or create a table to populate with all the paths), and update users however you do now for updates to the front ends

  4. #4
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    Ajax, thanks for the info. You have answered my question.

    Yes, creating table event macros is a tedious process, but I think it will provide the most accurate information when tracking data history.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My $0.02........

    I think you are going down a rabbit hole....

    I've had to troubleshoot a couple of dBs that used table event macros and it was headache.... actually, it was a migraine level headache.
    As Ajax stated "there are no means to document table event macros". IMHO, anything you cannot document should not be used.

    You must be diligent burning backups to CD/DVD media after every change to the table event macros.


    Maybe, table event macros could be used for your personal dB, but I wouldn't use them in a "mission critical" dB. (actually I don't and won't use them - tables are for storing data. Period!)


    Good luck with your project....

  6. #6
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    I agree that the data macros are not ready for production. From my testing and debugging for others they play best in a non-split database.


    If this were my project, I would use MS SQL Server Express as the back end.

    FWIW: I have stopped using the .accdb file format for back ends before Access 2013 was released. I only use MS SQL Server express or greater as the back end. I now only migrate JET (mdb) to SQL Server Express not accdb whenever possible. SO far it has always been possible.

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

Similar Threads

  1. looking for guidance on designing efficient database
    By mai0f in forum Database Design
    Replies: 3
    Last Post: 12-10-2015, 02:36 PM
  2. Upgrade MDB to accdb using RunTime environment
    By ItsMe in forum Database Design
    Replies: 2
    Last Post: 10-02-2013, 10:09 PM
  3. Unrecognized database format 'C:\xx.accdb'
    By jjotc23 in forum Access
    Replies: 4
    Last Post: 05-06-2013, 03:58 PM
  4. Can't create 2007 format accdb!
    By tym in forum Programming
    Replies: 1
    Last Post: 12-08-2011, 02:47 AM
  5. converting mdb to accdb format
    By crowegreg in forum Access
    Replies: 1
    Last Post: 05-31-2011, 10:34 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