Solved

I need to delete a corrupt form in Access 2K

Posted on 2015-02-15
15
258 Views
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/
0
Comment
Question by:pcalabria
  • 8
  • 5
  • 2
15 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
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.
0
 

Author Comment

by:pcalabria
Comment Utility
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.

THanks
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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"
0
 

Author Comment

by:pcalabria
Comment Utility
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.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
were you able to save the forms as text?
0
 

Author Comment

by:pcalabria
Comment Utility
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?
0
 
LVL 84
Comment Utility
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:pcalabria
Comment Utility
@Ray

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.
0
 

Author Comment

by:pcalabria
Comment Utility
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.
0
 
LVL 84
Comment Utility
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:

https://support.microsoft.com/kb/811889?wa=wsignin1.0
0
 

Author Comment

by:pcalabria
Comment Utility
Scott,

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.
0
 
LVL 84
Comment Utility
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.
0
 

Author Comment

by:pcalabria
Comment Utility
Scott,
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=192.168.50.12,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.
0
 
LVL 84
Comment Utility
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.
0
 

Author Closing Comment

by:pcalabria
Comment Utility
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.

PC
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now