Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Database Conversion (Access 97 > Access 2000)

    We've got 2500 access databases on our network that we need to convert to Access 2000.

    We're going to write a program that automatically converts all of these databases, is there any way to convert without using the command line switch? I don't wan't to use the switch if possible as if the database is already in 2000 format you will get a pop up message informing you.

    Alternatively, is there an extra /s switch to specify silent running...?

    Thanks

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Database Conversion (Access 97 > Access 2000)

    Unfortunately, I'm afraid you have some challenges ahead of you. I don't believe there is any way of using the command line to make the process silent, so the only option I know of would be to test the version of the database after you've opened it, perhaps in VB rather than VBA. Windows Script Host might be of some value, but it depends on how well your 2500 databases are organized and stored. One solution we've used is to simply train the users to do the conversion. But if you have any VBA or old Access Basic lying around, you will have to intervene to get the database to work - conversion doesn't deal with all the issuse, just the common ones. Just out of curiosity, how did you end up with 2500 databases - does each employee have one of their own or something?
    Wendell

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Database Conversion (Access 97 > Access 2000)

    Recommend try the Access Application object ConvertAccessProject method. Example, using Replace function to append "2K" to name of the 97 db:

    Public Sub TestConvert97To2K(ByRef strOldFileName As String)

    Dim strNewFileName As String
    strNewFileName = Replace(strOldFileName, ".mdb", "2K.mdb", , , vbTextCompare)
    ' strOldFileName = "C:AccessPassword.mdb"

    Application.ConvertAccessProject _
    SourceFilename:=strOldFileName, _
    DestinationFilename:=strNewFileName, _
    DestinationFileFormat:=acFileFormatAccess2000

    ' Test msg:
    MsgBox "Access 97 database " & strOldFileName & " has been converted to " & _
    "Access 2000 format. New file: " & strNewFileName, vbInformation, "CONVERTED"


    End Sub

    This works w/o problem in Acc 2002. Not sure when this method was introduced but may be available in A2K?. The acFileFormat enum defines which versions of Access file may be converted to, as noted in "Help" file:

    DestinationFileFormat - Required - AcFileFormat. The Access version of the converted file.

    AcFileFormat can be one of these AcFileFormat constants:

    acFileFormatAccess2
    acFileFormatAccess2000
    acFileFormatAccess2002
    acFileFormatAccess95
    acFileFormatAccess97

    You'd want to add error handling, an error will occur if destination file already exists. As noted by Wendell, if these db's have Access objects (forms, reports, modules, etc) , not just tables, you have your work cut out for you verifying that no conversion errors occurred....

    HTH

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Database Conversion (Access 97 > Access 2000)

    ConvertAccessProject came in with A2002.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Conversion (Access 97 > Access 2000)

    Thanks people, the ConvertAccessProject would've been perfect but I'll just have to stick with the command line for now.

    Wendell - We're quite a large company (1000+ employees) dealing with lots of sales information. We converted to the MS Office suite from Lotus & as soon as Access was available, people were using databases rather than Lotus 123 for all their data projects! It's a bit of a mess to be honest....they take up so much disk space & we really need to review all of them. That's a tedious job though & one I may have to delegate....!!!

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Database Conversion (Access 97 > Access 2000)

    I don't know if you've resolved this issue, by chance (following link in a recent thread on unrelated subject) came across this link you may want to check out:

    Trigeminal Software, Inc. (TSI) - Free Utilities - TSI Conversion

    Among other cool stuff, this site has a free utility called TSI Conversion that you can use to convert an Access db to current version. The download zip file contains 3 versions of the utility (an .MDE file) numbered 08, 09, 10 for Access 97, 2000, or 2002 respectively. In a quick test, the convert09.mde utility successfully converted an A97 db to A2K format. The .MDE exposes one public function, ConvertDatabase. The syntax is simple. Example:

    ConvertDatabase "C:AccessPassword97.mdb", "C:AccessPassword2000.mdb"

    Note there was NO annoying dialog box informing you file was converted - the db was converted w/o fanfare. Recommend check this out to see if you can use it for your thankless conversion project.

    HTH

  7. #7
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Conversion (Access 97 > Access 2000)

    Was toying with my own conversion program but this looks like exactly what I need. I'll test this today & let you know how I get on, many thanks for finding....

  8. #8
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Conversion (Access 97 > Access 2000)

    Have tried with a few databases and seems very good...

    My only moan is that it only returns TRUE or FALSE, in the case of FALSE I would want to know why it failed (security etc). But it's going to save time whatever so it's all good...!

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •