Results 1 to 7 of 7

systematically convert Access 97 mdb to 2016

  1. #1
    sgthuth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Location
    SC
    Posts
    33

    systematically convert Access 97 mdb to 2016

    I found this subject throughout the forums regarding the migration from older versions of ADB to new. Most suggest getting almost every iteration of access from the start to now but I only have 97 that someone at work just handed me and 365 (2016)... With being limited as such, I started trying to think of a way to do this systematically. I think I read somewhere in the forums that a user was trying to do the same thing and that "My task does not involve streamlining/optimizing." I am in the same dilemma and every time I glance at this program, it breaks my heart that someone abused access like this.

    Just to accomplish the task at hand, I wrote a script in 97 that exported every table to excel. From there in 2016, I wrote a similar script to import them which seemed to work. I have yet to compare formatting and I'm sure with the 239 tables, there's an issue as such.
    Please feel free to annihilate this...
    Code:
    Public Sub ImportXLFiles()
        Dim myfile As String
        Dim theName As String
        Dim mypath As String
        mypath = "G:\ExpTbls\SaveExp\"
        ChDir (mypath)
        myfile = Dir(mypath)
        Do While myfile <> ""
            If myfile Like "*.xls" Then
                theName = Left(myfile, Len(myfile) - 4)
                DoCmd.TransferSpreadsheet acImport, 8, theName, (mypath & myfile), True
            End If
            myfile = Dir()
        Loop
    End Sub
    Can I do something similar for queries (too many to count) and macros (23)?
    What would be an easy way to tackle the formatting?

    There's only 5 Forms so recreating those ones once the data is moved will be cake but there's more reports than I can count.


    Also, the switchboard manager is used and it's been so long since I looked at that. Does 2016 even have that anymore?

    Any help would be appreciated.
    Last edited by sgthuth; 08-10-2018 at 01:47 PM. Reason: Extra code brackets

  2. #2
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,484
    You could save each of the queries as SQL which would make it relatively easy to transfer across.
    Macros may be a problem as the format changed with A2007 IIRC.

    You can also export all the code as text files then import this into the new version.

    Personally I would obtain a copy of A2007 and use that as an intermediary step as it can open/convert A97 files to ACCDB and A2016 can then open those files.
    Colin (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

  3. #3
    sgthuth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Location
    SC
    Posts
    33
    @ ridders52
    I'll see if I can dig up A2007 but I'm not going to put all my eggs in that basket. I was just curious if someone had done this just programming-wise as not all of us were admins in 97'. Lol.

  4. #4
    sgthuth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Location
    SC
    Posts
    33
    Found this in the "Similar Threads" below

    "Convert format from mdb(Access 97) to accdb(Access 2016)"
    http://www.accessforums.net/showthread.php?t=66520

    I may have a copy of 2010. I'll have to look when I get home but will 2010 work to convert A97?
    I'd still like to figure out how I'd do it with vba straight from A97 to 2016. Keep me from having to bounce between versions.

  5. #5
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,315
    to add to Colins comments, I seem to recall back in 97 there is an option to convert macros to VBA - certainly available in later versions. However with code in general, although much will still be valid some stuff has been deprecated - you may be using it or maybe not. When you have copied to 2016, ensure each module has Option Explicit at the top and compile.

    for queries, have code to loop through the querydefs and export the sql to notepad or similar.

    dim qdef as querydef
    for each qdef in querydefs
    export qdef.name and qdef.sql code here
    next qdef

  6. #6
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,484
    Quote Originally Posted by sgthuth View Post
    Found this in the "Similar Threads" below

    "Convert format from mdb(Access 97) to accdb(Access 2016)"
    http://www.accessforums.net/showthread.php?t=66520

    I may have a copy of 2010. I'll have to look when I get home but will 2010 work to convert A97?
    I'd still like to figure out how I'd do it with vba straight from A97 to 2016. Keep me from having to bounce between versions.
    I don't think its possible to update all A97 objects to A2016 using VBA
    Its certainly impossible to open A97 databases in A2016
    There are issues with system tables from A97 that aren't used in later versions

    I had thought the last version that officially supported A97 was A2007 but it seems I'm wrong
    I tried converting a couple of A97 database in A2007. The conversion completes with one error caused by Access no longer supporting user-level security

    Click image for larger version. 

Name:	Capture.PNG 
Views:	10 
Size:	10.8 KB 
ID:	35070

    Trying to open the MSysACEs table gives this error

    Click image for larger version. 

Name:	Capture2.PNG 
Views:	10 
Size:	6.7 KB 
ID:	35071

    I also tested the same A97 databases in A2010 with the following results:

    a) Open in A2010 and convert as above.
    Conversion completes with same errors as in A2007

    b) Import all A97 db objects into a blank database in 2010. Completed without errors
    The MSysACEs table opens without issues

    Both methods seemed functionally identical in ny tests but that doesn't necessarily mean either will work 100% with your much larger database.

    Neverthless its definitely worth a try - especially method b)

    Good luck
    Colin (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

  7. #7
    sgthuth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Location
    SC
    Posts
    33
    @Ajax,
    I think I will look into systematically converting the database another time. I'll have to keep the objects in mind when I do start the write up but I will be sure to put what I can on here for later use.

    @ridders52
    Thanks for looking out. I actually searched my plethora of storage at home and found A2010. I followed the steps to convert it but wasn't able to analyze the errors. I was going to bring the laptop into work today to look at it but left it at home. Not really too sure why I'm having such a hard time today. Anyways. I should be able to get it here tomorrow and take a look and let you guys know. From looking at the A97 db I was given, there were no modules of code. Again, I shake my head every time I look at this thing. 420 Mb, 239 Tables, etc. And the lady that brought this to me said this was her smallest db and that there were more. ... One day at a time.

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

Similar Threads

  1. Replies: 4
    Last Post: 08-07-2018, 04:38 AM
  2. Replies: 7
    Last Post: 04-19-2018, 11:57 AM
  3. Automating Outlook 2016 from Access 2016
    By jcc285 in forum Programming
    Replies: 10
    Last Post: 09-30-2017, 12:53 PM
  4. Replies: 4
    Last Post: 06-12-2017, 01:01 AM
  5. Replies: 2
    Last Post: 11-02-2016, 10:41 AM

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
  •  
Tech Forums: Microsoft Office Forums