I need to delete a corrupt form in Access 2K

Posted on 2015-02-15
Medium Priority
Last Modified: 2016-02-11
My Access 2K program file (front end of db) has become corrupt and can not be repaired using Compact and Repair.
While importing all the object into a new database I learned that several the problem is corrupt forms and reports.

I would prefer to repair the existing mdb file if possible.  (If I recreate the database, I will need to deal with MS SQL database connections and third party add-in software security problems.).

I don't care about these objects.  I want them out of my database.  Can you help?

When I try to delete them I can three error messages.
1.  The form name 'myform' is misspelled or refers to a form that does not exist.
2.  You canceled the previous operation
3.  Module not found.

I have also opened the objects, deleted the controls, and the code in the module, but get the same error message and am unable to solve the problem.

Access 2K/XP Pro/
Question by:pcalabria
  • 8
  • 5
  • 2
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 40610794
I don't think there's much you can do with errors of that nature. You will almost certainly need to create a new database and import everything that is good to that new database - and you should do that, regardless of the level of effort required to do so. Corrupt Forms/Reports are often the tip of the iceberg, so to speak, so you're better off starting over.

That said, you might try a Decompile to see if doing so will reset the PCode. To do that, create a shortcut with this as the Target:

"full path to msaccess.exe" "full path to your db" /decompile

Run that, then see if you can remove your objects - but even if you can, I think you're playing with fire if you don't create the new db and do the import.

Author Comment

ID: 40610843
Thanks Scott,  I've been working on that in the meanwhile, and yes, I did try decompile first.
I was afraid that was the path I would need to take.

Where I am stuck is with linking to the the MS SQL 2008 R2 tables.  
Apparently the linking information did not transfer to the new database.

I did this years ago (on a different development machine) but it was more than five year ago.  I don't remember everything.

It seems that the DSN needs to be recreated.
(I went to the linked tables window and used refresh and nothing appear in the create data source window)

First, which driver should I be using, I think either 10 or 10.5 but they aren't listed.  
The workstations are all XP Pro SP3 and the back end is MS SQL Server 2008.

 Also, do I use a System DSN?  All the users will be authenticated by our windows 2003 server and will be on the same LAN.  I don't want them to have to enter any passwords once they are logged into windows.

Your help, or help from any of the experts would be appreciated.  I need to get the database up and working today.

LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40610855
have you tried saving the form as a text file

application.saveastext acform, "YourFormName",CurrentProject.Path & "\YourFormName.txt"

then recreate the form with

application.loadfromtext acform, "YourFormName",CurrentProject.Path & "\YourFormName.txt"
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 40610865
Thanks Ray, that's great info but recreation of the form is not a problem in this case.  
The problem is getting rid of it! Access will not let me delete it.
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40610883
were you able to save the forms as text?

Author Comment

ID: 40610937
Perhaps I did understand... as far as the form that I want to delete I should use the code your provided to save it as text and then delete it?
LVL 85
ID: 40610939
You can create a System DSN and use that, I suppose, but you could also simply use the Linked Table Manager to recreate the links, and when you do so choose to Save the Password. That would generally be sufficient to allow the FE to link to the tables.

If you do create a DSN, be sure to use the correct applet to do so. if you're running on a 32-bit machine, then the Control Panel ODBC applet will work, but if you're running a 64-bit machine you'll need to use the odbcda32.exe file found in Windows\System32.

Author Comment

ID: 40610943

I pasted the following line in the immediate window and pressed enter:

application.saveastext acform, "NewProspectTestOnly",CurrentProject.Path & "\NewProspectTestOnlyText.txt"

I got a mesage that the form "NewProspectTestOnly" was misspelled or does not exist.

Author Comment

ID: 40610969
Scott, I did this:

Opened Linked Table Manager
Click Select All
Click Ok

Error Message:
Connection Failed:
SQLState: 'S1000'
SQL Server Error: 11011
[Microsoft][ODBC SQL Server dRiver]Can not generate SSPI context
<Okay button displayed>

Not that I am doing development on a different machine than the machine used to create these links about five years ago.
LVL 85
ID: 40611912
When using the LTM, make sure you also select to "Always prompt for new location", then try creating your new links.

Regarding that SQL error:


Author Comment

ID: 40611927

When I use the LTM I can get things to work if I use SQL Server authentication, although using a System DSN I can't find a way to save the password.  It is not acceptable for the users to enter a password to access the linked tables.

Is there a way to embed the password in the Access code, or should I use a different type of DSN?
This must be a common problem?

Regarding the SQL error I've been looking at that article all night, but its still over my head.
LVL 85
ID: 40612127
You would have to delete the links, and then recreate them. When you do that, you're given the option to store the Password in the connection string.

Or, as I've suggested in your other question, you can use DSNless connections, and create this yourself.

Author Comment

ID: 40612167
This is the connection string that works with the corrupt database.  (I think its dsn-less)
The problem at this point is that I don't remember how I added it to Access 7 or 8 years ago, but its been working fine until the database crashed.

ODBC;DRIVER=SQL Server;SERVER=SQLSERVERLITE;UID=DistiMasterUser;PWD=1234567;APP=Microsoft® Access;WSID=EXECUTIVE;DATABASE=Inventory_CompPricingSQL;Network=DBMSSOCN;Address=,1433

So how would I go about adding this connection string without having to do put it in the autoexecute or an on open event of a form.  I didn't have to run any code before.  I remember there was a password button somewhere that I found and then access took the password and created the connection string for me.
LVL 85
ID: 40612251
Short of recreating all of your linked tables via code, there is no way to handle this other than by deleting and recreating your linked tables.

Also, you're connecting to a specific instance of your SQL Server via an IP address + Port,  so you must be sure your machine can get to the IP address and Port.

Author Closing Comment

ID: 40612350
Thanks to everyone for their help.
I awarded the points to Scott because his solution, creating a new DB, regardless of the effort, was the best path.

It was a lot of effort, but it's done now. Thanks to all of the help.


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question