Link to home
Start Free TrialLog in
Avatar of Stuart Segan
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.
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Well i must say i lost you a bit...if your issue is to delete records/tables and you have a SQL BE then you issue either SQL commands via VBA or you create action passthrough queries to perform this kind of operation.
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.
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
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Stuart Segan
Stuart Segan

ASKER

I'm accepting this as the answer because, well I can!