Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

I need to delete a corrupt form in Access 2K

Posted on 2015-02-15
15
Medium Priority
?
271 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
[X]
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
15 Comments
 
LVL 85

Accepted Solution

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

Author Comment

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

THanks
0
 
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"
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:pcalabria
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.
0
 
LVL 120

Expert Comment

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

Author Comment

by:pcalabria
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?
0
 
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.
0
 

Author Comment

by:pcalabria
ID: 40610943
@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
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.
0
 
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:

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

Author Comment

by:pcalabria
ID: 40611927
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 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.
0
 

Author Comment

by:pcalabria
ID: 40612167
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 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.
0
 

Author Closing Comment

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

PC
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

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.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

618 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