Home » VB.Net

VBA Code Disappears from Access 2007 Database

I have an Access 2007 database application that was working yesterday, but today when I opened the file, all of the VBA code behind the forms stopped working. To be more specific - it appears all of the code modules for each form have disappeared from the project explorer as well as all of the class modules and standard modules. The odd part is that according to the form properties window in Access, each form still has the HasModule set to "yes". Also, all of the standard modules and class modules still appear in the Navigation Pane in Access - although if you try to open one from there, Access says they don't exist. If I try to delete the references from the Navigation Pane, they disappear then immediately reappear.

I am of course, able to revert to a backup of the file - but this is the second time it has happened in a month. I am mostly interested in the mechanism behind how this happened (how can the modules all disappear at once, while Access seems to hold onto some sort of reference to them).


1 Answer Found


Answer 1

Hi Rvs109,

Welcome to MSDN forum and we are very glad to help with you.

After reading your post, I knew your issue that the code modules for each form disappeared from the project sometimes. If I have misunderstood you, please let me know.

I haven't encountered this issue before, however, it seems that others ever experienced and here is the thread about this issue and some experts have provided the solution:

VBA module code disappear missing


I hope it can help you and feel free to follow up after you have tried the solution mentioned in the thread.

Best Regards,




I'm working in VBA in Access 2007 and I have code to send an email from Access using MS Outlook 2007.  I am receiving an error on the code trying to attach a document on my machine to insert into the email.  THe error is occurring in the followin code:

If Not IsMissing(AttachmentPath) Then

      Set objOutlookAttach = .Attachments.Add(AttachmentPath)

End If

I am gettign an error saying the following:

Run-time error '-2147024891 (80070005)':

You don't have appropriate permission to perform this operation.

What could be causign this?  I have all the same libraries in my references in VBA...(matching what I had in Access 2003).

Please advise!





I am having a problem with VBA and modules within my database.  The DB was developed using past versions of Access, and when I try to open it using Access 2007....I have problems with using the "export spreadsheet" command in VBA. 

DoCmd.TransferSpreadsheet acExport, , "ForecastQueryExport", "C:\" & Me![Test] & ".xls", True

I get the following error:

Run-time error '3051':

The MS Office Access database engine cannot open or write to the file 'c:\Sample.xls'.  It is already opened exclusively by antoher user, or you need permission to view and write its data.

Does anyone know why i'm getting this error in Access 2007 and how i can correct it?




I have an access 2007 app located on a lan.  There is a front end and the tables are linked to sql server.  There are less than a dozen regular users.  Today, when I opened the app I could view the main form but none of the buttons had functionality.  When I went looking for the code, it was not there?

I found some others with this problem when searching "access 2007 vba disappear" but there found no good resolution or rationale.

To fix I restored a backup front end.

This happened a second time today and it seems to be isolated to a particular user who is causing the problem.




I need VBA code "Recurrence" which will take 2 dates as an input, and then insert a vale(s) in Access db table.

Could you please point me to a such code?

Thank you,




I haveavery annoyingproblemin Access 2007.

When Irun VBAcodethat refersto a query, access  takesaway the codefrom the sqlquery.The question is left isbutsql codeisgone.

Private SubCommand_Click()
Dim abc
abc =InputBox("Youwill now..)
DoCmd.OutputToacOutputQuery,"qryXXXX",acFormatXLS,"c:\ exportfiles\"& abc&Date &".xls"
MsgBox "The filehas been created"
End Sub

The first timethe coderuns withouterrorsnext timethere isno sqlcodein question"qryXXXX"

Itoccurs randomly. Haveany of youencounteredthis?


adding a new button by code with Action = ppActionRunMacro and run = mySub.

this works fine on pptm file, but when saving the file as ppam it won't call the mySub.

the idea is - I want to be able to click on a button that will start the mySub while the slideshow is running.

any suggestion?

thanks, Nir.


I have the following VBA code in a module of one of my .xlt files:


Public Sub SetShapeTop(ByVal shapeName As String, ByVal topValue As Single)
  ThisWorkbook.ActiveSheet.Shapes(shapeName).Top = topValue
End Sub

In my VSTO add-in, I have the following C# code which tries to call that VBA function:

m_worksheet.Application.Run("SetShapeTop", kv.Key, kv.Value, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

That line throws an exception, as follows:

(Excerpt from my log4net logs)

[Exception: COMException]
{Target: Cluster Report}
{Invoked Method: Macro execution}
{Target Method: Cluster Report}
{Parameters: }
{Message: Cannot run the macro 'SetShapeTop'. The macro may not be available inthis workbook or all macros may be disabled.}
{Stack trace: 
Server stack trace: 

Exception rethrown at [0]: 
  at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
  at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
  at Microsoft.Office.Interop.Excel._Application.Run(Object Macro, Object Arg1, Object Arg2, Object Arg3, Object Arg4, Object Arg5, Object Arg6, Object Arg7, Object Arg8, Object Arg9, Object Arg10, Object Arg11, Object Arg12, Object Arg13, Object Arg14, Object Arg15, Object Arg16, Object Arg17, Object Arg18, Object Arg19, Object Arg20, Object Arg21, Object Arg22, Object Arg23, Object Arg24, Object Arg25, Object Arg26, Object Arg27, Object Arg28, Object Arg29, Object Arg30)
  at MSA.Excel.ClusterPresenter.RenderReport(IReportData reportData, IResponseReportParameters reportParams, Worksheet worksheet) in C:\workspace\MSABasketlink\2.0\MSA.Library.UI\Presentation\ClusterPresenter.cs:line 647} 

Am I missing something here?  If anyone can point me in the right direction, I'd really appreciate it...!



I'm working with .mdb database and want to get rid of the Ribbon in VBA code.

I'm sure this is a simple thing but am having trouble with finding code.

Can anyone help?


Is there a way of protecting VBA code in Excel 2007 so that a user can't simply click on the developer tab and start fiddling?

Hi there,

I'm creating a presentation template in PowerPoint 2007 and am using VBA to create a macro that will update text boxes on multiple master slides with the information provided in a userform.

Basically I have two master slides, one I've called TitleMaster (as this function is no longer available in 2007) and the second is called SlideMaster.  When I run the macro, everything works fine for the first slide master, but when it gets to the code for the second master, it displays a run-time error saying that the item is not found.

Here is my code:

'updates information on TitleMaster Master Slide to include information as provided in the userform
ActivePresentation.SlideMaster.Shapes("txtTitleofSub").TextFrame.TextRange.Text = frmData.txtTitleofSub.Value
ActivePresentation.SlideMaster.Shapes("txtMeetDate").TextFrame.TextRange.Text = frmData.txtMeetDate.Value
ActivePresentation.SlideMaster.Shapes("txtEMTExec").TextFrame.TextRange.Text = frmData.txtExecName.Value  _
  & ", " & frmData.txtExecTitle.Value

'updates information on SlideMaster Master Slide to include information as provided in the userform
ActivePresentation.SlideMaster.Shapes("txtComName").TextFrame.TextRange.Text = frmData.txtComName.Value
ActivePresentation.SlideMaster.Shapes("txtDate").TextFrame.TextRange.Text = frmData.txtMeetDate.Value

The code only stops working when it hits "txtComName" as it appears on the second master slide.

Is there a way to distinguish between different master slides in the above code?

Any help is greatly appreciated!!



Hi -

I need to run an update query on a linked table as part of a procedure in VBA, and I need to run it directly on the DB2 host (without using Access' query engine) to keep the data in the correct format (basically, I'm having to add milliseconds into a timestamp field).  I tried the following code using dbExecDirect, but since we're using Access 2007, that doesn't work.  Unfortunately I'm not familiar with ADO so I don't know how to rewrite this routine, but I'm guessing that's how I need to fix it. 

The query referenced in the code below (qryHighValue) is a pass-through query that's basically just a placeholder - I already learned the hard way that pass-throughs can't do updates.

Help? Pretty please? I've lost days of work and gained many grey hairs to this!

  Dim Q As DAO.QueryDef
  Dim db As DAO.Database
  Dim highTest As Date
  Dim parentID As String
  Dim sqlString As String
  Dim cnnect As String

  highTest = Me.END_TSTMP
  parentID = Me.CMPGN_ID

  cnnect = "ODBC;DSN=aaaaa;UID=xxx;PWD=xxxx"
  cnnect = cnnect + ";MODE=SHARE;DBALIAS=aaaaa;DISABLEUNICODE=1;"
  sqlString = "UPDATE MY_TABLE SET END_TSTMP = '9999-12-31-' "
  sqlString = sqlString + "WHERE (((CMPGN_ID)=" + parentID + "));"

  If highTest > #12/1/5000# Then

    Set db = CurrentDb()
    Set Q = db.QueryDefs("qryHighValue")
    Q.Connect = cnnect
    Q.SQL = sqlString
    db.Execute sqlString, dbExecDirect

  End If



Hope someone can help.

I have an Access 2007 Tool being developed for our customer, which we currently have producing PDF reports using:

DoCmd.OutputTo ACOutputReport, "REPORTNAME", acFormatPDF, , -1

This code produces a PDF report and offers to save it with location/name up to the user.  However it is not producing a tagged PDF report that is required for screen readers and our Section 508 requirements. 

When you do a save as PDF manually it gives an Options button that allows you to select "Document structure tags for accessibility"  Which would seem to add the tags to the PDF for screen readers to use but for the life of me I do not see a way to automate this setting via VBA so that it happens automatically from our tool. 

any help with this situation would be greatly appreciated.






I created a database for a customer in Access 2010. The default format I am using is Access 2007. I saved it as an accdb. The customer has Access 2007 SP2. When I try to open it on the customer's machine (and one of my machines that has Access 2007) I get "Unrecognized database format". I have tried removing all of the references I could to Office 2010 libraries to no avail. I created a blank db using the same settings and it opens fine.

I was supposed to deliver this today, but I guess that's out. Any ideas?


Here's an in depth update.

To find out where the problem lies, I created a db on the target machine and moved it to my machine. I imported everything from the problem db to the blank one. And then moved that file back to the target machine and it failed to open.

So I created another blank db on the target machine and moved it to my machine. I imported only the tables and moved the file back to the target. It opened fine.

So I moved it back to my machine and imported the queries (tables still in db). I moved it back to the target machine and it opened OK.

So I moved it back to my machine and imported the forms. I moved it back to the target machine and it failed to open. I then took it back to my machine, removed the forms, and moved it back to the target. It still failed to open.

After much work I have determined that I can include everything (tables, queries, modules, and reports) but the forms and the db will open OK on the target machine.

Is the problem with VBA?


The shortcut contains the /profile switch as it did for 2003.  Made required changes to shortcut to use Access 2007.  Created the Profile in the Access 2007 part of the registry.  When I click on it I get the following message:

Microsoft Office Access couldn't find file 'C:\Program Files\Microsoft Office\Office12\'.  This file is required for startup.

If I take out the profile it runs fine, but unfortunately I have several items in the profile for proper functioning of the program. 

Any ideas?




Greetings all,

I come here rather perplexed by a problem that doesn't make any sense.  To make a long story short, below are the details of my problem.

 - VBA code being written for custom data analysis and statistical report generation for a complex database
 - code must be maintainable by boss after I leave as I'm only at the company temporarily (no IT department), so all work is in VBA.
 - development work being done in Access 2007 on a Windows Vista machine.

Basic code structure:
- 1 gui (form) with extra code behind to handle logic of data analysis
- 3 classes to represent various forms of data being analyzed

 - while typing in code to complete the internals of an if statement, the program simply freezes up and then Access restarts.
 - if statement I'm working in is the third and final level of a nested if statement structure.  Shouldn't make a difference, but you never know.
 - no error code is generated or given when the program crashes.

I can provide a code sample as needed, as well as explaining some of the data flowing through the system. 

Many thanks to anyone who can help me.

Stephen Carlson


I have tried to find any method that would allow me to send a message to a user from within Access VBA to Communicator 2005.  The only examples I have been able to find are for Communicator 2007 and the SendText() method.  What is the method to send text in the Communicator 2005 API?


Private Sub Command0_Click()
Dim msgr As CommunicatorAPI.IMessengerConversationWndAdvanced

Dim strTo As String
Dim strMsg As String

strTo = "test@domain.com"
strMsg = "Test from VBA"

On Error Resume Next
Set msgr = Messenger.InstantMessage(strTo)
'msgr.SendText(strMsg) 'Only for Communicator 2007

End Sub


Hi there,

I would like to render a particular access Report in my MDB as a PDF - either by automation from VB6 or from Access VBA.

I have downloaded the "Save as PDF / XPS" add-on for Office 2007, so can manually save a report as PDF from Access now.

However, attempts to work out how to do this in code have so far drawn a blank. RunCommand does not seem to be able to access this particular functionality.

Could anybody point me in the direction of a solution?

Many thanks.

Richard Fewster


  I have a form that I am trying to get data from and use as date range validation and also a value in the combo box to use as a key to the other table and get the description.  Both are causing problems and when I try to debug to look at the values and see what they are, I am getting "Object Libraries Not Registered".  This is very frustrating, since it should not be this complicated.  Been trying for 3 days to get this to work and now I am reaching out for help.

  I need help with the date range criteria and also gettting the value from the Combo Box.  Using Access 2007.  The code is as follows and I would really like appreciate some guidance and help with this.  Here is the code:


Private Sub cmdCreateFile_Click()

    Dim db As Database
    Dim TblIns As String
    Set db = CurrentDb()
    Dim AdKey As String
    AdKey = Me.Email_Ad_Key.ItemData(1)
TblIns = "INSERT INTO tblMailList ( Phone_Number, Email_To, Email_Body, Date_Paid_Bill )"
TblIns = TblIns & " SELECT tblMain.Phone_Number, tblMain.Phone_Number & tblMain.Email_Provider, "
TblIns = TblIns & " tblAds.Ad_Body, tblTrans.Date_Paid_Bill "
TblIns = TblIns & " FROM tblAds, tblTrans, tblMain "
TblIns = TblIns & " WHERE (tblMain.Send_Email=True) And (tblMain.Email_provider Is Not Null) "
TblIns = TblIns & " And (tblMain.Phone_Number = tblTrans.Phone_Number) "
TblIns = TblIns & " And (tblTrans.Date_Paid_Bill >= " & Me.Sdate_Sel & ") "
TblIns = TblIns & " And  tblTrans.Date_Paid_Bill <= " & Me.Edate_Sel & ") "
TblIns = TblIns & " AND (tblAds.Ad_Name  =  " & AdKey & ");"

    db.Execute TblIns
    MsgBox "Update Complete"

End Sub


Thanx in advance.



I'm developing a form so when you click on one of the buttons an input box comes up and prompt the user to insert a date. Now the box comes up with 2 buttons on it an "OK" and a "Cancel". I would like when the user click "OK" after inserting the date to go to the next form, and when the user click "Cancel" to exit and return to the original form. I know how to do all that, but what's preventing me is i don't know what is the return value when the user click on "OK" or "Cancel" in order for me to finish my code? Microsoft help for "InputBox" doesn't mention a return value for "OK" or "Cancel".

Here's my code, and thanks in advance:

Private Sub MenuOp_Click()

Dim tbl As Recordset, RecDate As Variant, cnt As Long, recd As Variant, intx As Integer, response As Variant


If Len(Nz(Me!StaList)) > 0 And Len(Nz(Me!TypeList)) > 0 And Len(Nz(Me!PosList)) > 0 Then
        cnt = 0
                If cnt = 3 Then
                    MsgBox ("3 Incorrect Entries!!! Form will reset")
                        Me.StaList = ""
                        Me.TypeList = ""
                        Me.PosList = ""
                        Me.MenuOp = 0
                        GoTo MoveOn
                End If
        RecDate = InputBox("Please Enter the date using this format: mm/dd/yyyy", "Enter Date",Date)
                If Len(Nz(RecDate)) = 0 Then
                    response = MsgBox("Date entry cannot be blank. Do you wish to continue?", vbYesNo + vbInformation + vbDefaultButton1, "Blank Field")
                            'MsgBox (response)
                            If response = vbYes Then
                                cnt = cnt + 1
                                GoTo Repeat
                                Me.StaList = ""
                                Me.TypeList = ""
                                Me.PosList = ""
                                Me.MenuOp = 0
                                GoTo MoveOn
                            End If
                ElseIf Not IsDate(RecDate) Then
                    MsgBox (" '" & RecDate & "' " & " is not a proper date")
                       cnt = cnt + 1
                       GoTo Repeat
                    GoTo Continue
                    'MsgBox ("I'm Here")
                End If
                Set tbl = CurrentDb.OpenRecordset("Inspection_Data", dbOpenDynaset)
                tbl!Sub = Me!StaList
                tbl!EquipType = Me!TypeList
                tbl!Pos = Me!PosList
                tbl!Label = recd
                tbl!DateOfService = RecDate
                DoCmd.OpenForm "Inspection_Data_Form", , , "[SUB]= '" & Me!StaList & "' And [EquipType]= '" & Me!TypeList & "' And [Pos]= '" & Me!PosList & "' And [Label]= '" & recd & "'", acFormEdit
                GoTo MoveOn



Hallo. I have googled a lot about this topic, but still could not find an answer. I am trying to pass a Subform as an argument to a function.So the declaration of the called function is:

Sub MyMainForm_ShowSubform(objSubForm as Object)

Now if the call statement is

MyMainForm_Showsubform objSubform = Forms!MyMainForm!MySubform

i see that objSubForm is of type Object/Controls, which I do not want.

Otherwise, if the call statement is:

MyMainForm_Showsubform objSubform = Forms!MyMainForm!MySubform.Form

i see that objSubForm is of type Object/Form_MySubform, wich I neither want.

What I want is an object of type Object/Subform, i.e. the vey same type of object that I would have if from within the main form code I wrote:

dim obj as Object

set obj = Me.MySubform

I am stuck here and I do not want how to go on. I only know that I want to pass neither a "Controls" object, nor a "Form" object: I want to pass a "Subform" object.

Thank you for any suggestion.






<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure