Stuart Segan
asked on
MS Access
i have the same problem with Access and deleting tables, especially deleting the linked table entries. Its not the only problem I have with Access but I have the delete problem all the time. the scenario is usually that I'm handed a MS Access .mdb or .accdb file and told it worked perfectly until just now and now its broken. Please fix it. then I find out that the back end is a SQL Server and there's linked tables up the wazzoo, not to mention connection string calls to stored procedures on full blown production servers, and of course the local tables in Access for the reporting sets which is why these horrible contraptions are often built in the first place.
I do not have a solution other than to sometimes copy the entire front end apparatus piece meal to an intermediate location and then start a new Access project and copy the forms, functions, connection strings, to the new project and then begin the process of establishing the need local tables and SQL Server linked tables, etc....
Speculating wildly here but my intuition is that somehow the NOt being able to delete tables and deleting links to SQL Server tables comes about through the Windows and SQL Server priveleges of the folks who put these yucky things together. CFO's and super duper savvy salespeople are famous for this junk. They often have SQL Server privileges they don't understand and then build these front end apps using access that are a gothic nightmare.
I do not have a solution other than to sometimes copy the entire front end apparatus piece meal to an intermediate location and then start a new Access project and copy the forms, functions, connection strings, to the new project and then begin the process of establishing the need local tables and SQL Server linked tables, etc....
Speculating wildly here but my intuition is that somehow the NOt being able to delete tables and deleting links to SQL Server tables comes about through the Windows and SQL Server priveleges of the folks who put these yucky things together. CFO's and super duper savvy salespeople are famous for this junk. They often have SQL Server privileges they don't understand and then build these front end apps using access that are a gothic nightmare.
What exactly is your question? It sounds as though these users have made pretty complicated Access databases by putting together SQL linked tables with tables, forms and queries that they create. If that's the case, my experience has shown me that often the problem is that the Access databases are constantly being used, tables updated, etc., etc., possibly by multiple users, without ever being compacted. And then sometimes they just break...and as you have discovered you can't always fix that. My practice has always been to make a backup copy of every Access database on a regular basis (daily or at least weekly) so that if a user breaks it, there's a recent backup that can be provided to them (after making sure it's compacted and all the links work). Is there a possibility for you to do this in your environment?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm accepting this as the answer because, well I can!
If you lack permission the you have to talk to your SQL dba to sort this out...although deleting records or deleting (dropping) a table is quite a serious thing not to be treated lightly.
Give some more accurate info to better assist you.