By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Error deleting a closed object in Access 2002

P: 69
All,

I'm not sure how to adequately explain my problem in two sentences or less, so at the risk of providing TMI, here's the condensed verion.

I have developed an Access 2002 database file that contains a form, multiple queries and multiple reports. The purpose of the form is to allow the user to run various queries against my company's Sybase server and display a report.

Since the queries return ADO recordsets from the Sybase server, I've found it MUCH easier to write VB code that creates a table in Access based on the fields and data in the ADO recordset. I then use the table as the record source for my reports.

Each time a query is run, the old tables are deleted and then recreated with the new data from the Sybase server before the report is displayed.

I have many people in my company that like the using the form and reports in my database. But since the users of the database are constantly deleting and recreating tables, each user must have their own copy of the database file to avoid stepping on each other.

I continually get requests from users to modify and/or add new queries and reports. But since all of the users have their own copy of the database, distributing updated forms, queries and reports is a challenge.

I hit upon the idea of creating a table in the database that contains the names, types, and revision dates of all database objects (i.e. all queries, forms, reports, macros, modules, etc.). Then I placed a master copy of the database file on my companies network with the latest and greatest version of all database objects.

Finally, I added an "Update" button to the main form in the database. When the user clicks the button, a macro is launched. The macro runs a procedure that first closes the main form, then compares the revision date table in the master database file (on the company network) with the revision date table in the users local copy of the database.

If the revision date for a particular object is newer in the master database, the object is deleted from the user's local copy and the updated version of the object is copied in from the master database.

Make sense?

Everything work beautifully until today when I made a revision to the main form. Now when the "Update" button is clicked, the macro fires, the procedure is run, the main form is closed, but when the procedure tries to delete the main form object, Access gives me an error. The error says:

Run-time error '2008':
You can't delete the database object [
main form name] while it's open.

What gives? My procudre has already closed the form and it is no longer visible on the screen. Why does Access still think it's open?

I figure the problem has something to do with the fact that the form I'm trying to delete is the same form that called the macro which called the procedure that's trying to delete it.

Any thoughts on how I can get the form to completely close so I can delete it?

Here's the code I use to close the main form (called "PROGMAN_frm")
Expand|Select|Wrap|Line Numbers
  1. If CurrentProject.AllForms![PROGMAN_frm].IsLoaded Then
  2.     DoCmd.Close acForm, "PROGMAN_frm", acSaveNo
  3. End If
Thanks for your help and reading this lengthy post.

Scott
Jan 15 '08 #1
Share this Question
Share on Google+
1 Reply


jaxjagfan
Expert 100+
P: 254
Are you trying to delete a form in a multiuser environment? If more than one user is in the database with the "Main" form open - it cannot be deleted until they exit the database. If all users are clear - try closing the mdb and reopening - it may be the .ldb file thats locking the form.

I had a multi-user reporting db that served a similar purpose as yours. The back-end databases (Oracle and DB2) were queried once per day and loaded the Access tables (loaded 20-30k records per day and only had the past 18 months of data in the Access tables). Once loaded an automated email when out an let the end-user group know it was available for download.

The load process was done early in the morning. Most of the end-users didn't have access to the Oracle or DB2 tables (no drivers to set-up or admin issues for Oracle or DB2).

Additional reports were added as requested and were available via a reports form to the end-user. Each time a report was added or something changed - the version # changed (MyAccessApp v5.012). A version report was also available within the app.
Jan 15 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.