?
Solved

Access 2007 Runtime VBA issues

Posted on 2009-02-18
6
Medium Priority
?
466 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

0
Comment
Question by:Pinecone2007
  • 4
  • 2
6 Comments
 
LVL 85

Assisted Solution

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

Author Comment

by:Pinecone2007
ID: 23673145
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
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 160 total points
ID: 23673705
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.


0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Pinecone2007
ID: 23677286
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
0
 

Accepted Solution

by:
Pinecone2007 earned 0 total points
ID: 23677777
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
0
 

Author Comment

by:Pinecone2007
ID: 23677799
Workaround accepted as solution
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

829 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