We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Access 2007 Runtime VBA issues

Medium Priority
479 Views
Last Modified: 2013-11-27
I have developed a mini application in Access 2007 (development machine is running Vista with Office 2007).  I have developed custom Ribbons and automated a number of features using VBA functions and sub routines.  All routines have error handling built in.  I have done my original testing and development using Access 2007 runtime are on my machine

My distribution targets are using Access 2007 runtime, with Office 2003 on XP.  

I am getting problems occuring on my target environment that do not show up when I run Access 2007 runtime on my development machine

In my target machines.  
1) My ribbons work fine -- apart from the OnAction method which does not appear to be firing the named procedure.   I have tried putting in MsgBox statements at the top of the procedure and as far as I can tell, the procedures is not being invoked -- but works fine on my development machine.  The procedure does not run, however the Runtime application continues

2) Some of my command button 'Event' VBA procedures are working consistently, however others are failing and seemingly missing the On Error Goto Error_Handler statements that I have included at the top of the procedures.  I have tried putting in MsgBox statements at the top of these procedures, but again it appears that the procedure is not being invoked.  These failures are causing Runtime to abort with the friendly option of 'Click to close'.  Example given in Code Snippets of one routine that is working fine and another that is failing

My initial thought was that VBA was not installed on the target machines, however I have ruled this out since at least some of the VBA procedures are working OK.

I suspect that their is an environment issue here -- I'd appreciate guidance
'Here is one Subroutine that causes the Runtime on XP to error
Private Sub btnASX2007_DblClick(Cancel As Integer)
On Error GoTo Error_Handler
If (Not IsNull(Me.Code)) Then
   Call basASXannouncements(Me.Code, "2007")
End If
Exit_Procedure:
   On Error Resume Next
   Exit Sub
Error_Handler:
   DisplayUnexpectedError Err.Number, Err.Description
   Resume Exit_Procedure
   Resume
End Sub
 
'Whilst this seemingly similar sub routine runs fine
Private Sub btnasxpage_Click()
On Error GoTo Error_Handler
  Call basASXpage(Me.Code)
Exit_Procedure:
   On Error Resume Next
   Exit Sub
Error_Handler:
   DisplayUnexpectedError Err.Number, Err.Description
   Resume Exit_Procedure
   Resume
End Sub

Open in new window

Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Are your references all good on the enduser machines?

It'd seem that BOTH of those would fail, not just one, but missing refs can cause all sorts of issues.

Are users running both 2003 and 2007 simultaneously? I've had troubles on my machine when doing this.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thanks for the comment,
The target machine have 2003 only whilst development has 2007 only so I  think my  problem lies elsewhere.

But to your observation, mixed environments do appear to create grief sometimes due to mixs ups with the references to Microsoftware Object library and Microsoft Office Library -- which I think are version 12.0 for 2007 and version 11.0 for 2003
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
So then you are 100% certain that your references are GOOD on the enduser machines? That is, you've verified this by either (a) opening one of the databases in Design mode and checking the references via the VBA Tools - References menu or (b) you have made a list of those references and you've insured that those references are the correct ones and are properly installed and registered on the enuser machines.


Author

Commented:
I am far from certain that my references on the Target machines are 100% GOOD, however they do appear to be OK

The Target machines do not have Access installed, so instead I have looked at the references via Excel and what I see appears to be correct for a office 2003 installation

1) Visual Basic for Applications
2) Microsoft Excel 11.0 Object Library
3) OLE Automation
4) Microsoft Office 11.0 Object Library
I have found a workaround:)

Doing the following has resolved my issues.

1) I split the database into a front-end and back-end (which is a suggested good practice for multi-user databases; Database ToolsL Move Data> Access Database)
2)  I then found my Custom Ribbons dissappeared due to the USysRibbons table having been shoved into the backend database.  So I deleted the linked refence to this table and restored the table to the front end database from my back-up
3) Then I converted the front end database into ACCDE (Database Tools >Database Tools>Make ACCDE)

All my procedures are now working  -- best of luck to anyone else working with Access 2007 Runtime

Author

Commented:
Workaround accepted as solution
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.