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.
Stuart SeganAsked:
Who is Participating?

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

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.

John TsioumprisSoftware & Systems EngineerCommented:
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.
Hypercat (Deb)Commented:
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?
At least you are not blaming Access for this debacle.  Better training for the people who build these Rube Goldberg devices might be in order.

One situation where a working app just fails is that Access doesn't automatically "see" changes to ODBC linked tables.  You might start by refreshing the links and trying again.  Databases change over time.  A column gets added to a table or perhaps a new index, etc.  You could resolve this by having the app refresh the links every time the app opens.  It slows down the open so it isn't generally done.  Or, you can keep track of schema changes and if you know a table used by an Access app has been changed, you can force the refresh yourself.

Compact is a very important process if the apps were not built correctly and therefore are constantly adding/deleting rows from temp tables or completely rebuilding them with make table queries.  There are better solutions that won't bloat the app.  If that is the problem, let me know and I'll explain how I handle interfacing with ERP systems where the system owners refuse to let me link to THEIR data and force me to constantly replace local tables with updated data.

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
Stuart SeganAuthor Commented:
I'm accepting this as the answer because, well I can!
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

From novice to tech pro — start learning today.