Solved Access 2007 ADP - The Save operation failed

[Fran Lombard]

I have an Access 2007 ADP project that is giving me "The Save operation failed" error message upon attempting to save the DB after compiling.

I have tried creating a new database and importing all objects but still getting the message.

The database runs fine, I can make changes to the forms, modules, macros and reports and save the database.
I only seem to get the error when attempting to save the vb project or closing the db after running Debug->Compile from the vb editor.

As a result I can not save a version of the db with the vb code in a compiled state.

Has anyone found the root cause of this issue and have a solution. As I said, I already have started with a fresh blank db and imported all objects to no avail.

Thanks
Fran

 

[strive4peace]

hi @Fran Lombard

are you also using 2007 to make changes to it? ADPs are no longer supported ... and neither is 2007, but I still use it for regular databases

 

[Fran Lombard]

hi @Fran Lombard

are you also using 2007 to make changes to it? ADPs are no longer supported ... and neither is 2007, but I still use it for regular databases

Yes im using 2007

 

[strive4peace]

@Fran Lombard

perhaps you can try decompiling it. This is the code equivalent of compact/repair for data.

BACK IT UP FIRST!!!

'~~~~~~~~~ Decompile ~~~~~~~~~

make an make a "generic" decompile icon where the next database you open will be decompiled... use something like this as the target:

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" /decompile

if your Access program is not located in the directory specified, make the appropriate substitution

the next database you open will be decompiled... you can verify this by going to the design view of any module -- Compile will not be gray, it will be available

then, do compact/repair
then, compile the database

~~~
alternate steps to make a Decompile icon:

Open Windows Explorer
(right-click on [Start], choose 'Explore'

scroll to the top of the left pane

right-click on Drive C:
choose --> Search

look for -->
MSACCESS.EXE

once you see where the program is located, navigate to MSACCESS.EXE using Windows Explorer

then right-click on MSACCESS.EXE and choose -->
SendTo > Desktop (create shortcut)

then, right-click on the desktop icon you just made and choose --> Properties

1. click on the 'Change Icon' command button -- so it doesn't look like a regular Access icon (I use the 2 inerlocking circles one screen to the right on the top row)

2. click in the Target textbox
press the [End] key to move to the end of the line
then, add this to the end of the target -->
/decompile

3. change the icon if you want (I use the interlocking circles)

 

[Fran Lombard]

@strive4peace

Thank you for your suggestions

I have an icon set up for decompiling my database and have tried.

I want to point out, this error message has just occurred on a brand new db which all objects have been imported from the source db.
As such, this error is occurring on the very first compiling of the instance.

 

[strive4peace]

you're welcome, @Fran Lombard
perhaps examine one of your backup files when everything was working
Sadly, my code documenter tool won't work on an ADP ... it does work on regular databases

http://www.accessmvp.com/strive4peace/CodeDocumenter.htm

EDIT: it seems that is what you currently have is probably a regular db (see my comment below the next one)... so you probably CAN document that code with this tool, if you want to

 

[strive4peace]

@Fran Lombard
ps, as ADPs are no longer supported, have you given any thought to pushing the BE to SQL Server/Azure?

 

[strive4peace]

I have tried creating a new database and importing all objects but still getting the message.

When you create a new ADP project, you need to specify the right type and make sure the extension is ADP

https://support.microsoft.com/en-us...a0-55a4-45d4-9ee5-95f67383d4cb#__toc257281381

 

[Fran Lombard]

Thanks again for your comments.
By their very nature all access adp projects have a mssql backend. Mine is running on mssql 14 express.

To be clear - everything about the application is running fine. I can compile the vb project with no errors. The only issue is upon attempting to save the project after compiling. This is when access throws the "The Save operation failed" error message. As a result the compiled code is not saved. After closing the application, going back to the vb editor the next time (without making any changes to the vb code) the compile option is not greyed out. Access is just not saving the compiled code and does not remember the project was compiled.

This is a known issue. All my searching of various avcess related internet sites have not uncovered a root cuase. Everyone says the solution is to create a new access project and import all objects and you are good to go. I have done this countless times over the 12 year life of this application and it has always solved the problem.

This time, the problem has persisted into the new project. I have attempted this re-creating of the project multiple times and the problem continues.

I am looking to hear from someone who has actually had this problem and has solved it by fixing the root cuase.

I believe (just guessing) the root cuase may be an issue with one of access's hidden system tables or some corrupted setting (compiled flag) in a system table.

 

[strive4peace]

hi @Fran Lombard

I have another idea for you!

> root cuase may be an issue with one of access's hidden system tables or some corrupted setting (compiled flag) in a system table

or maybe it's a database property!

Get this code, turn on the Debug Window (Ctrl-G), and run Show_Properties

http://msaccessgurus.com/VBA/Code/Properties.htm#Download

maybe not though since database properties aren't imported, but it would be worth inspecting them anyway since perhaps theyre created when you make an ADP

 

[strive4peace]

@Fran Lombard

forgive me for telling you things you already know ... perhaps someone reading this needs that information.

and if it IS something in a system table, get this code

VBA > Document > Access Tables to Excel​

http://msaccessgurus.com/VBA/Code/Document_Tables2Excel.htm#Code
and modify code to be:

   sSQL =  "SELECT o.Name AS TName " _
      &  ", o.Type AS iType" _
      &  ", Switch([Type]=1,'Table'" _
      &  ",[Type]=4,'ODBC Table'" _
      &  ",[Type]=6,'Linked Table') AS TType" _
      &  " FROM MSysObjects AS o" _
      &  " WHERE((o.Type In (1,4,6)) " _
      &  " ORDER BY o.Name" _
      &  ";"
   Set rsTable = db.OpenRecordset(sSQL,dbOpenSnapshot)

that way, it will document system tables too

You could also run this code in one of your projects that works and compare the results

Here's the link to just get the BAS

http://msaccessgurus.com/VBA/Downloads/mod_Document_Tables2Excel_s4p__BAS.zip

 

[strive4peace]

@Fran Lombard
one more idea ... Compact/Repair THREE TIMES

 

[strive4peace]

@Fran Lombard

I did a little research. Someone with your same error had duplicate names for procedures. You can press F2 in the Visual Basic Editor for the Object Browser, and then pick your database from the Libraries at the top.

then look in the lower right in the Members pane to see if there are any duplicate names. Duplicates are allowed is if only one is Public and the others are Private

ALSO, module names can't be the same name as any procedure -- good idea to preface them with something to ensure that. I usually use "mod_" or "bas_"

AND the project name also has to be unique

 

[Fran Lombard]

@strive4peace

Thank You so much for your insights.
It turns out I did have 2 sets of duplicate names.
In both cases I had a Public Sub and a Public Function in different modules with the same names.
I changed the names and now all is well.

Again - Thank you
Fran

 

[strive4peace]

that's awesome, Fran ! You're welcome ~ happy to help