Split Database (Access 97)

Topic

New Reply

Ever since I split my database, I can no longer access my table via Microsoft Query. Is there something I did wrong during my split?

thanks
christine

Reply | Quote

Replies

Hi Christine,

You should use MS Query to connect directly to the table in the backend database. Trying to connect through the linked table in the frontend probably won’t work.

Reply | Quote

I am trying to go directly to the dataset. I am still getting a “cannot access” message. I am trying all 3 data source definitions:MS Access 97 Database*
MS Access Database (not sharable)
MS Access Database*

Reply | Quote

I just found an error that states I have the database open exclusively. Strange. I have it setup for shared. All users are in right now and working. What did I do?

I also just kicked all users out to clear the lock. It still is showing the files in use with no one in. What do I do with this?

Reply | Quote

Did a rogue .ldb file get left behind in the same folder as the database?

Reply | Quote

I think so. I trace it back to a user that I just had shut down their entire system. No luck. I can’t get by the files.

Reply | Quote

I’m not an expert in this, but if the database is on a network share, perhaps the network administrator can reset the file locks or something like that, and remove the .ldb if it is still there.

Reply | Quote

Okay, it only took a few hours, but I finally got it all cleared up. No one is in the database, no records are locked, nothing! The problem with Query is still happening. Where to now?

thanks
christine

Reply | Quote

What happens if you make a copy of the backend database? Can MS Query retrieve data from the copy?
If that doesn’t work, create a new blank database in the same folder, and import all tables from the backend database. Can MS Query retrieve data from the new database?

Reply | Quote

I’ve tried the copy and the importing suggestion. No luck. I’m still getting the “cannot access” error.

Reply | Quote

Umm, you didn’t happen to secure your database at the same time you split it, did you?

Reply | Quote

No, it’s been secured for months. I changed everyone’s password when I split it, though. Everyone is in there working and updating with no problems. Even some of the reports that I have written before I did the split work fine. It’s only when I try to create a new one that I get the problem.

Reply | Quote

If you’re sure that you could get at the data using MS Query after securing but before splitting the database, I’m stumped. Splitting should have no influence whatsoever; the back end is just a database with all the tables from the original, without any modification. I’m sorry…

Reply | Quote

Okay, I think I’ve found something. I tried to create a new database, totally unrelated to my original. I have a brand-new table and found the same problem. It’s got to be the workgroup setup. But, when I went to review the permission, there was nothing unusal. Am I missing something?

Reply | Quote

Have you tried joining the default unsecured workgroup System.mdw (assuming that you haven’t modified that)?

Reply | Quote

No luck. Joined the default and still can’t get to the tables.

Reply | Quote

I am very sorry, but I am out of ideas

Reply | Quote

As an administrator, check the security settings for new objects for the various groups and users. Who owns the new objects? Ownership of objects frequently gets people into trouble when using Access security.

Reply | Quote

Charlotte, you ROCK!!!!! (No offense, Hans! I couldn’t have gotten to this point without your steps guiding me there.) For the benefit of other users that may have the same problem:

When you split a database, even though the system.mdw remains the same, you need to access the backend dataset as ADMIN. You will find that the admin has no access to the tables. Thus, no query access. Even though you as the user has access, if the admin doesn’t, you “ain’t” gettin’ in. So, re-setup your permissions as Charlotte suggested and you’ll be all set.

THANKS A BUNCH! YOU GUYS ARE THE BEST!!!!!

Reply | Quote

To explain a bit further what goes on here – MS Query goes into Access assuming that it is an unsecured database, but in your case it wasn’t. You will want to be careful what permissions you give Admin however, as doing so may create potential problems for you if you give users too many priviledges.

Reply | Quote