Results 1 to 14 of 14
  1. #1
    jsblume is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    20

    Question Programatically Upgrading Format


    I'm working with a client that has thousands of Access 2003 databases, most of which are single-table. They need to upgrade to 2016. Rather than opening each one at a time, I'm hoping there's a way to right VBA code that will do that.

    Any suggestions would be greatly appreciated.

    Thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Certainly db can be programmatically opened and manipulated in VBA code.

    If 2003 file has security and workspace setup, the conversion might not be clear cut.

    Maybe you just need to import table using DoCmd.TransferDatabase. That would not require opening and manipulating db object.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    There may also be issues with references when you upgrade to the newer version.
    Similarly there may only be one table, but what about code in forms and reports.
    Although the code should still run once references are fixed, there may be exceptions where Access features have been dropped or modified

    I assume you will be converting to the ACCDB file format.
    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

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    And another thing.
    Although you haven't mentioned 64-bit Access, if you are using it all API declarations will need to be converted.
    Even if that's not relevant to you I strongly recommend you read this parallel thread about upgrading as it contains other advice you will need to be aware of.
    See https://www.accessforums.net/showthread.php?t=74013
    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

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    with regards vba code, I presume you are only talking tables, not queries, forms etc. can't provide any specifics - but you will

    1. need code to loop through all the mdbs
    2. within the loop, create a new .accdb with the mdb name (but with .accdb) - see createdatabase function
    3. loop through all the tables in the mdb (see tabledefs collection) and use a make table query to make a table in the new db or perhaps the copyobject or transferdatabase command. Not sure if the last two will work since you would be trying to copy from a JET db engine to an ACE db engine. See transferdatabase to link or import the table from the mdb
    4. move to next mdb

    You will probably need to tidy as you go, deleting linked tables etc. Note the make table query will not copy indexes, field formats and the like, for that you would need to use copyobject or transferdatabase.

  6. #6
    jsblume is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    20
    Thanks! TransferDatabase might just do the trick.

    Quote Originally Posted by June7 View Post
    Certainly db can be programmatically opened and manipulated in VBA code.

    If 2003 file has security and workspace setup, the conversion might not be clear cut.

    Maybe you just need to import table using DoCmd.TransferDatabase. That would not require opening and manipulating db object.

  7. #7
    jsblume is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    20
    Thanks for all of the suggestions! TransferDatabase looks like the best way to go. I'm not overly concerned about code - it's macros that might be an issue.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Macros are nothing like what they used to be.
    Suggest you convert all the macros to VBA before upgrading
    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

  9. #9
    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
    As others have mentioned, these could be factors in moving to 2016
    -32 vs 64 bit,
    -use of 2003 type of security
    -any deprecated functions/features that are used in your 2003.

    thousands of Access 2003 databases, most of which are single-table.
    seems to imply that database concepts and practices have not been followed with any discipline.

    Does anyone know what the "thousands" of databases do/are used for?

    Have you done some analysis to see what "features" exist? API calls; class modules; 3rd party routines?

    As an approach, you might take 2 or 3 random databases and do some analysis to help scope the project.
    Adjust as necessary to "define" a pattern.

    Good luck.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So you are not trying to merge data? You still want each db to be independent?

    Thousands of single-table databases is very odd.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    jsblume is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    20
    Databases were created by casual users for their own particular needs. There is not time to do any analysis at the moment to determine the purpose of all the databases in order to consolidate. Primary goal is to convert them so that they will run on Windows 10 - roll-out of the OS will complete by the end of next year.

    Quote Originally Posted by orange View Post
    As others have mentioned, these could be factors in moving to 2016
    -32 vs 64 bit,
    -use of 2003 type of security
    -any deprecated functions/features that are used in your 2003.

    seems to imply that database concepts and practices have not been followed with any discipline.

    Does anyone know what the "thousands" of databases do/are used for?

    Have you done some analysis to see what "features" exist? API calls; class modules; 3rd party routines?

    As an approach, you might take 2 or 3 random databases and do some analysis to help scope the project.
    Adjust as necessary to "define" a pattern.

    Good luck.

  12. #12
    jsblume is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    20

    Upgrading Databases - Sample Code

    The code below illustrates what I'm trying to do. TransferDatabase and CopyObject won't work because I can only import to or export from the database I have open. What I need is code that will iterate through a text file containing the list of mdb's, create a corresponding accdb, and copy all the objects from one to the other.

    Code:
    Public Sub UpgradeDatabase()
    
    
        Dim app As Object
        Dim dbOld As Database
        Dim dbNew As Database
        Dim tbl As TableDef
        Dim fileName As String
        Dim dbName As String
        
        Set app = CreateObject("Access.Application")
        app.Visible = False
        app.UserControl = False
        
        'mdbList.txt is a file with a list of mdb names and the path to it
        Open "C:\temp\MS Access\mdbList.txt" For Input As 1
    
    
        While Not EOF(1)
        
            Line Input #1, fileName
            
            'extract the name of the database from the path
            dbName = Replace(Mid(fileName, InStrRev(fileName, "\") + 1), ".mdb", "")
         
            'create a new accdb database with the same name
            Set dbNew = app.DBEngine.CreateDatabase("C:\temp\MS Access\" & dbName & ".accdb")
            dbNew.Close
            Set dbNew = Nothing
            
            Set dbOld = app.OpenCurrentDatabase(fileName)
        
            'copy the objects from old to new
            For Each tbl In dbOld.TableDefs
                'this line does not compile, but essentially what I need to do
                'also need to do a similar loop for each object type
                dbOld.CopyObject "C:\temp\MS Access\" & dbName & ".accdb", , acTable, tbl.Name
            Next
        
            dbOld.Close
            
            Set dbOld = Nothing
            
        Wend
    
    
        Close 1
           
        app.Quit
        
        Set app = Nothing
        
    End Sub

  13. #13
    jsblume is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    20
    Quote Originally Posted by June7 View Post
    So you are not trying to merge data? You still want each db to be independent?

    Thousands of single-table databases is very odd.
    Correct. At some point in the future the data may be merged, but no time for that now. I'm working at a very large government agency, which explains the thousands. A lot of companies are prohibiting users from having MS Access for this very reason.

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    A lot of companies are prohibiting users from having MS Access for this very reason.
    On that basis you might as well ban Excel - they store and manipulate data as well. However I would agree a lot of companies are not using 2003 and would have upgraded years ago. In over 20 years, I've only ever come across one company that banned the use of Access.

    It feels to me that your approach is wrong. Chances are many of those db's are defunct and no longer in use. I don't see the point of one person upgrading all of them. What about the users who actually use them - If they are capable of creating a db, they are capable of upgrading it - and fixing their links/shortcuts at the same time. If one person is doing it, chances are everything else will stop working. Give them a time limit, and when that time is up, move all the mdb's to an archive somewhere. If they still want something that had not been converted, they can arrange for it to be returned to them.

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

Similar Threads

  1. SQL Statement Won;t Execute Programatically???
    By mrmmickle1 in forum Access
    Replies: 7
    Last Post: 12-08-2014, 08:35 PM
  2. Set Combo Box Value Programatically
    By Sibtain in forum Forms
    Replies: 1
    Last Post: 05-23-2013, 05:04 PM
  3. How do I send an email programatically?
    By bcmarshall in forum Access
    Replies: 1
    Last Post: 02-01-2013, 09:00 AM
  4. Programatically Checking Queries
    By dandoescode in forum Programming
    Replies: 8
    Last Post: 07-11-2012, 12:33 PM
  5. Programatically Add Columns to a Datasheet
    By altemir in forum Forms
    Replies: 2
    Last Post: 03-27-2012, 10:02 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
  •  
Other Forums: Microsoft Office Forums