none
Access 97 to SQL Server - workgroup security issues

    Question

  • I have an old Access 97 database that I'm trying to migrate to SQL Server Express 2008 with no luck. The database is encrypted and tied to a certain workgroup information file. I have tried many different ways to migrate and/or export this data, and I keep coming across the same class of errors.

    Some background: I have successfully joined the workgroup to which this database is tied. From Access 97, I can log in as the user 'Adm' (the owner of all the database objects). Just to be sure that I was the owner of all of these hundreds of objects, I went through the permissions/ownership screen and explicitly set this user as the owner of all objects, with full permissions on all objects. I have set my windows user account and password to match this user, just in case.

    First, I tried the SSMA for Access. Immediately after adding the database and getting to the step where objects are selected for migration, it starts to throw errors like "Access Object Collector error: query : Could not read definitions; no read definitions permission for table or query 'blah'. Query 'blah' is encrypted." and " "Access Object Collector error: query :  Could not read definitions; no read definitions permission for table or query 'Raw Materials - Distinct by ProductID'. Query 'Raw Materials - Distinct by ProductID' SQL is encrypted." Well... I know it's encrypted, but credentials were never requested...

    Since I couldn't get anywhere with the SSMA, I tried the "Import and Export Data" wizard that was included with SS Express (not a separate download like SSMA). I chose Access as a data source, found the database, entered my credentials and could not proceed past this error: "The operation could not be completed. ADDITIONAL INFORMATION: Cannot start your application. The workgroup information file is missing or opened exclusively by another user. (Microsoft JET Database Engine)." That's impossible because I've made a copy of the database and I'm working on it in isolation. I even disconnected from any networks and tried this immediately after a clean boot. I inspected any services and applications that may even have the slightest chance of touching any database and disabled them. I also tried going into the advanced dialog, but there were no settings there that I could tweak to get past this error. The documentation mentioned a "Data Link Properties" dialog box to specify a workgroup information file, but try as I could, I could not find how to get to this dialog.

    Since I was stuck using the migration tools provided by SQL Server, I thought I would try some intermediate upgrade and/or something to decrypt the database or otherwise release the objects. First, I tried some things with Access 97. I tried to decrypt the database; it stops after a few seconds with the error "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time." You can see my above explanation for the precautions I took to make sure this wasn't the case. Also, I thought decrypting the database to a separate target file was a read operation; why does it think I'm changing anything at all?

    Next, I tried the User-level Security Wizard; no matter what settings I put in here, after a few moments I got the same error as above.

    I thought maybe I could try upgrading to another version of Access. I have 2000 and 2007 available. I was able to get the database to convert from 97 to 2000, but I got "There were compilation errors during the conversion or enabling of this database. THis might be due to old DAO syntax that is no longer suported" followed by "There were compilation errors during the conversion or enabling of this database. The database has not been saved in a compiled state. The performance of this database will be impaired because Microsoft Access will need to recompile the database for each session." I don't really feel comfortable proceeding without knowing what these errors are, but I figured, "What the ____? At least it worked, let's try SSMA again."

    I take my converted Access 2000 database and attempt to load it into SSMA, and I got more of the same encrypted errors! "Access Object Collector error: table : Employees .Could not read definitions; no read definitions permission for table or query 'Employees'. Error occurred while loading indexes." and "Access Object Collector error: query :  Could not read definitions; no read definitions permission for table or query 'Raw Materials - Distinct by ProductID'. Query 'Raw Materials - Distinct by ProductID' SQL is encrypted."

    To say that I'm frustrated would be a huge understatement.
    Friday, February 19, 2010 3:18 PM

All replies

  • Try this:
    1. Create a new Access database (with no security file)
    2. Log in into your secured Access database
    3. Export all the database objects from the secured into the new database
    4. Run the application in the unsecured database to ensure everything is working correctly
    5. Use SSMA to upgrade your database
    Sunday, February 21, 2010 8:15 AM
  • Ok, maybe I'm being stupid here, but it seems like I have to do to step 3 manually for each object in my database, which is about 900..is .there has to be a better way.
    Monday, February 22, 2010 6:32 AM
  • You'll need to create a script.  Example:

    Dim frm As Form
    For Each frm In Application.Forms
        DoCmd.CopyObject "c:\destination.mdb", frm.Name, acForm, frm.Name
    Next frm
    Monday, February 22, 2010 6:45 AM
  • I didn't have to write a script; I created a new database, and from the new database imported the old objects. You can easily 'select all' when doing this. The problem is that I still get "Could not read definitions; no read definitions permission for table or query 'Example'" for every table that I try to migrate with SSMA.
    Monday, February 22, 2010 6:49 PM
  • I have a possible solution for you if there is still a problem.
    Sunday, July 04, 2010 5:36 AM
  • I have a possible solution for you if there is still a problem.

    Can you post it for forum readers benefit?  Thanks.
    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    Sunday, July 04, 2010 11:00 AM
  • Was there anything further on this? I am having the same problem on an old 97 database that was converted to 2000. my problem is there is no one to support the database here and it was written back in the horse and cart days (almost!!)

    Every time i try to move it into SQL I get the same errors as the above fellow. I am just about to attempt the new database option but if i could get it into sql i think this would be the best result.

    One other thing however, this database has macros enabled that autostarts it into the main form? As SQL doesnt do forms and is usually handled by the front end of a software package, how would i go about converting this into something that is SQL and usable?

     Would love to hear if anyone any helpful information they can add as its been over a year since the last post. Maybe new tools and different options are out there. 

     

    Tnx

     


    Troy Michie
    Tuesday, January 10, 2012 1:21 AM