I need to delete a corrupt form in Access 2K

Posted on 2015-02-15
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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"
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.


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 84
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 84
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 84
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 84
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

732 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