Migrating MS Access database from 2003 server to 2008 R2 needs cleanup

I moved our MS access database from Windows server 2003 to Server 2008 R2. The database is split, and also uses security.mdw. The most immediate issue was that the tables were read-only unless the user sets their shortcut to "Run as Administrator." Is there a way to address that? The client is MS Access 2010.

Also, I would like to get rid of the security.mdw function, but Google confuses me. Is there a clear procedure to do that?

Thanks!
LVL 28
mikebernhardtAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
a couple of critical issues WRT migrating Access db to SQL Server.  Every table should have a primary key; no primary key, table will not be able to be updated.

I also add a timestamp field to every table during the migration process.
mikebernhardtAuthor Commented:
No, it's not that stuff. It works fine as long as I run as administrator.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Be sure your users have Read/Write/Create/Destroy permissions on the folder hosting the backend database, as well as on the folder hosting the .mdw file.

You can remove User Level Security, but you should first insure that doing so won't break your application. Often ULS (i.e. the .mdw file) is used for user navigation (and sometimes data security), so if your application depends on that you'll make a pretty good size mess if you remove it with nothing in place to replace it.

To remove ULS:

https://support.office.com/en-us/article/Set-or-change-Access-2003-user-level-security-in-Access-2010-0c6a10e7-966f-44f4-864e-5d2ef79439fa

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

mikebernhardtAuthor Commented:
Nope, that didn't do it (the permissions thing). in fact it gets worse. Although running as administrator works for me, another user set up exactly as I am, with the same domain permissions, etc., cannot get past the read-only thing, but I can write just fine if I run as administrator!
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Make sure everyone is out of the database, then look in the folder hosting the file. If you see a lock file (a file ending in .ldb or .laccdb, depending on your version) then delete that file, and see if the issue continues.

If so, then take a screenshot of the actual error messages. Many of them are very, very similar, and subtle differences in the actual message can give clues.

Also be sure the ReadOnly flag hasn't been set on the file itself.

But if that doesn't work (deleting the lockfile) then you're back to permissions. How exactly are you connecting to the server from your workstations? How you connect can determine exactly how you apply those permissions.
mikebernhardtAuthor Commented:
The only odd files I see are .snp. The problem isn't that the other user can't log in, it's that his access is read-only, even though I've modified the user security policy (using the wizard) to make him an admin. We just set up a 3rd person and he can write, so it may be a problem on one user's setup.

Regarding removing the security model, having the front and and back end might be making it more complicated, because if I open the back end and run the user security wizard, at the end it saves an unsecured copy. I can open it without security. But if I run it from the front end, it saves a copy of the front end which is still secured (I rename them so that the links match up).

I'm also trying to re-merge them. The solution is supposed to be to save the database as .accdb, but the only option is to save it in the old mdw format, even though I'm using Access 2010. I don't know if these issues are all related or not. Let me know if you want me to open multiple questions about these issues. I'm a network expert, not an Access expert :-{
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
.snp files are "snapshot" files, which were an early version of PDF-like files used by Access.

The problem isn't that the other user can't log in
Folder permissions have nothing to do with the ability to log into a machine, as you know. Nor does the Windows logins (and permissions) have anything to do with Access ULS logins and permissions. The two have no correlation, so don't confuse that part.

If you have a single user that is having troubles, then I too would suspect that user's profile. Try logging in as that user on a different machine and see what happens. If the troubles follow the user, then you'll have to delete the profile, and recreate it.

because if I open the back end and run the user security wizard,
You shouldn't be running the security wizard at all, and in fact doing so can cause a myriad of problems. ULS is a difficult methodology to work with. If you don't use the right .mdw file to open a properly secured database, then Access will deny you the ability to do so. Doesn't sound like your file is properly secured, else you'd be getting much different errors, but that may actually work in your favor on this.

I'm also trying to re-merge them.
This is a bad-bad-bad idea if you're dealing with a multiuser system. Multiuser systems should have a Backend (Tables only, in the case of an Access backend) and a Frontend (everything else). You should relink a single FE to the BE, and them make copies of that FE and distribute them to your users. You cannot effectively run a monolithic Access application with multiple users. Your performance will be horrid, and you'll be inundated with record locks, write conflicts, etc etc etc. Take our advice - do it the right way, and you'll have a much smoother time with this.

In my mind, the first step is to remove ULS. One simple thing to try is to simply use Access 2010 to open the database, and allow it to convert to the new .accdb format. The .accdb format doesn't support ULS, so the conversion will remove ULS.

After doing that, then create a new, blank database in the .accdb format and use the File - External Data - Import method to move everything from the db you converted into the new one.

Now do a little maintenance (make a backup of your new db first):

1. Compact the database
2. Compile it - from the VBA Editor click Debug - Compile, and fix any errors you find. Continue doing that until the menu items is disabled.
3. Compact again

From there you should have a valid FE database that you can use to make copies and distribute to your users. Your users would install those copies on their local desktops, and run them from there.
mikebernhardtAuthor Commented:
"In my mind, the first step is to remove ULS. One simple thing to try is to simply use Access 2010 to open the database, and allow it to convert to the new .accdb format."

I would love to do this, but for some reason the only option available to save in is mdb format, not accdb! Any ideas?
mikebernhardtAuthor Commented:
We found the problem with the one user, so he has proper access again and the database now works properly. But what I'd still like to to is to upgrade it to accdb and remove the user security. Unfortunately when I try "Save Database As" the only option is mdb.

Please let me know if you have any ideas why that is, or if I should open a new question (although it was part of the original post).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.