Access 2003 queries on Vista (1 Viewer)

Steve Lang · Jan 26, 2007

Hi

I have a product working fine under XP written in VB (access 2003 SP2). Most of the program works fine on Vista business but displaying graphs throws up errors. I think i may have narrowed it down to the SQL in the queries.

I used the simple query wizard as a test, the SQL produced automatically caused an error message saying that the function is not available.

Here is the automated SQL:

SELECT DISTINCTROW Format$([MonthlyHoursInit].[Date], 'mmmm yyyy') AS [Date By Month], Sum(MonthlyHoursInit.DayCapDec) AS [Sum Of DayCapDec]
FROM MonthlyHoursInit
GROUP BY Format$([MonthlyHoursInit].[Date], 'mmmm yyyy'), Year([MonthlyHoursInit].[Date])*12+DatePart('m',[MonthlyHoursInit].[Date])-1;

Any help would be great.

Dennisk · Feb 2, 2007

this sounds like a missing reference. Open a vba module and click tools/references. see if any references are flagged up a missing.
it may well be Visual Basic for applications

Steve Lang · Feb 3, 2007

Hi, Thanks for the reply.

You were right, i changed the ddl and security reference to 2.8 and that side is working fine.

BUT

I have a new problem, i am also using embedded graphs (ms graph) in access 2003, i dynamically change the SQL for the graph at runtime, the problem is that the graph does not display, if i click the graph or goto another form and then return i can see the graph. I have also added a graph slightly overlapping another and the topmost graph changes as required. This sounds like a graphic issue with Vista, i am guessing that it may be something to do with double buffering of graphics but i may be wrong.

Any help with this would be great

Thanks

Steve

boblarson · Feb 3, 2007

When you change the graph at run time you can try using either:
Me.Repaint:

Access database help file said:
The Repaint method completes any pending screen updates for a specified form . When performed on a form, the Repaint method also completes any pending recalculations of the form's controls .expression.Repaint
Return Value
Nothing

expression A variable that represents a Form object.

Remarks

Microsoft Access sometimes waits to complete pending screen updates until it finishes other tasks. With the Repaint method, you can force immediate repainting of the controls on the specified form. You can use the Repaint method:

When you change values in a number of fields. Unless you force a repaint, Microsoft Access might not display the changes immediately, especially if other fields, such as those in an expression in a calculated control , depend on values in the changed fields.
When you want to make sure that a form displays data in all of its fields. For example, fields containing OLE objects often don't display their data immediately after you open a form.
This method doesn't cause a requery of the database, nor does it show new or changed records in the form's underlying record source. You can use the Requery method to requery the source of data for the form or one of its controls.

Me.Refresh

Access Database Help File said:
The Refresh method immediately updates the records in the underlying record source for a specified form or datasheet to reflect changes made to the data by you and other users in a multiuser environment.expression.Refresh
Return Value
Nothing

expression A variable that represents a Form object.

Remarks

Using the Refresh method is equivalent to clicking Refresh on the Records menu.

Microsoft Access refreshes records automatically, based on the Refresh Interval setting on the Advanced tab of the Options dialog box, available by clicking Options on the Tools menu. ODBC data sources are refreshed based on the ODBC Refresh Interval setting on the Advanced tab of the Options dialog box. You can use the Refresh method to view changes that have been made to the current set of records in a form or datasheet since the record source underlying the form or datasheet was last refreshed.

In a Microsoft Access database , the Refresh method shows only changes made to records in the current set. Since the Refresh method doesn't actually requery the database, the current set won't include records that have been added or exclude records that have been deleted since the database was last requeried. Nor will it exclude records that no longer satisfy the criteria of the query or filter. To requery the database, use the Requery method. When the record source for a form is requeried, the current set of records will accurately reflect all data in the record source.

Me.Requery

Access Database Help File said:
The Requery method updates the data underlying a specified form by requerying the source of data for the form.expression.Requery
expression A variable that represents a Form object.

Remarks

You can use this method to ensure that a form or control displays the most recent data.

The Requery method does one of the following:

Reruns the query on which the form or control is based.
Displays any new or changed records or removes deleted records from the table on which the form or control is based.
Updates records displayed based on any changes to the Filter property of the form.
Controls based on a query or table include:

List boxes and combo boxes .
Subform controls.
OLE objects , such as charts .
Controls for which the ControlSource property setting includes domain aggregate functions or SQL aggregate function.
If you specify any other type of control for the object specified by expression, the record source for the form is requeried.

If the object specified by expression isn't bound to a field in a table or query, the Requery method forces a recalculation of the control.

If you omit the object specified by expression, the Requery method requeries the underlying data source for the form or control that has the focus . If the control that has the focus has a record source or row source, it will be requeried; otherwise, the control's data will simply be refreshed.

If a subform control has the focus, this method only requeries the record source for the subform, not the parent form.

Notes

The Requery method updates the data underlying a form or control to reflect records that are new to or deleted from the record source since it was last queried. The Refresh method shows only changes that have been made to the current set of records; it doesn't reflect new or deleted records in the record source. The Repaint method simply repaints the specified form and its controls.
The Requery method doesn't pass control to the operating system to allow Windows to continue processing messages. Use the DoEvents function if you need to relinquish temporary control to the operating system.
The Requery method is faster than the Requery action. When you use the Requery action, Microsoft Access closes the query and reloads it from the database. When you use the Requery method, Microsoft Access reruns the query without closing and reloading it.

Steve Lang · Feb 3, 2007

Hi Bob

Thank you for taking the time to reply.

I have tried refreshing, requerying etc both the ole graph and the form but no change. If i leave the properties window open and then move it over the graphs they then paint correctly, this works fine under XP but not Vista.

Any more ideas?

minniethecat · Feb 19, 2007

Steve Lang said:
Hi Bob

Thank you for taking the time to reply.

I have tried refreshing, requerying etc both the ole graph and the form but no change. If i leave the properties window open and then move it over the graphs they then paint correctly, this works fine under XP but not Vista.

Any more ideas?

Hi, Steve. I have the same problem you have with graph.

If I iconize and then resize the form, the chart appears...strange...isn't it?

Have you find a way to solve it?

Thanks in advance

Enrico Berengan

Steve Lang · Feb 19, 2007

Hi Enrico

I have found a solution, its only temporary as i am moving to Dot Net with my product, in my opinion its a messy solution but it works.

I change the SQL for the graph when required, i add a timer interval for 1 second and then set focus to the chart and then to another object on the form, this forces a repaint displaying the charts correctly. Dont forget to reset the timer interval to zero.

As i said its messy but for a short term solution it has taken the pressure off whilst i move to dot net.

Regards

Steve

minniethecat · Feb 19, 2007

Thanks for answering so fast.....

Do you think that me and you are the only persons having that kind of problem? I can't find any clue on the Net...Sounds strange.....

Thanks againg. Take care.

Enrico Berengan

Steve Lang · Feb 19, 2007

Enrico, you would think so, i have searched extensively on the net and came out with nothing.

Just another reason to leave MS Access.

Regards

Steve

Algis Kuliukas · Oct 13, 2007

Steve Lang said:
Enrico, you would think so, i have searched extensively on the net and came out with nothing.

Just another reason to leave MS Access.

Regards

Steve

If you are leaving MS Access, what are you going to? .Net?

I hate the way Microsoft appear to be pulling the plug on Access. It's one of the most productive development environments ever and they should be putting more R & D into it as a web site rapid development platform not forcing developers to take the huge leap to .Net.

Algis

boblarson · Oct 13, 2007

Algis Kuliukas said:
I hate the way Microsoft appear to be pulling the plug on Access. It's one of the most productive development environments ever and they should be putting more R & D into it as a web site rapid development platform not forcing developers to take the huge leap to .Net.

They aren't pulling the plug on it by any means. However, it is totally possible that they will move VBA from the VB6 based code to .NET based in coming versions. It would only make sense as it is much more robust and able to do things that VB6 can't.

Algis Kuliukas · Oct 13, 2007

boblarson said:
They aren't pulling the plug on it by any means. However, it is totally possible that they will move VBA from the VB6 based code to .NET based in coming versions. It would only make sense as it is much more robust and able to do things that VB6 can't.

Thanks Bob. I hope you're right.

Do you have any advice on moving to 2003 apps to Access 2007. I use customised toolbars and menus extensively but they don't work in 2007 unless you use the rather clumsy Add-Ins menu. Why did they have to throw the spanner into this beautifully simple and pwoerful tool?

All the best

Algis Kuliukas

Algis Kuliukas · Oct 13, 2007

PS

I also have a problem with Access 2003 apps running in Vista. The charts do not display unless you <doubleClick> them but that launches the Chart applet which is often a pain to then extracate one's self from.

Why do they have to mess things up every time?

Algis Kuliukas

boblarson · Oct 13, 2007

Algis Kuliukas said:
PS

I also have a problem with Access 2003 apps running in Vista. The charts do not display unless you <doubleClick> them but that launches the Chart applet which is often a pain to then extracate one's self from.

Why do they have to mess things up every time?

Algis Kuliukas

Have you set the MSAccess.exe file under C:\Program Files\Microsoft Office\Office or Office11 to run as Administrator on Vista?

Steve Lang · Oct 13, 2007

I have moved to .net, this hasn't been an easy task and some things that are so easy with access like reporting are more involved and i am also finding that i am writing a lot of low level code. The results so far are very good.

I also had the security problems with access, so i bought a digital certificate, then comes another problem, PC's with scandinavian regional settings do not recognise the id (i have tried 2, 1 from Thawte and also Verisign), this makes your software look amateurish.

The other side to .net that i like is my application runs on its own, and isnt affected by installing other products. I have had several customers of mine totally unable to use the software, either conflicting with access 2007 or a reference issue, it seemd the only way to solve this is with a format.

Access has been good to me, its easy to get started with but i think if you need greater control and connectivity then i think .net is the way to go.

Algis Kuliukas · Oct 16, 2007

boblarson said:
Have you set the MSAccess.exe file under C:\Program Files\Microsoft Office\Office or Office11 to run as Administrator on Vista?

Thanks Bob, but this didn't help. I log in as administrattor anyway. It's MS Chart, I think, that's the problem.

I wonder if there's a way of configuring the Chart applet to run as administrator or something.

All the best

Algis Kuliukas

boblarson · Oct 16, 2007

Algis Kuliukas said:
I log in as administrattor anyway.

That isn't the point. In Windows Vista Microsoft Access (MSAccess.exe) MUST be set to run as administrator (doesn't matter if you log in as admin, you need to escalate its privileges this way) or else it won't work right.

Algis Kuliukas · Oct 16, 2007

boblarson said:
Have you set the MSAccess.exe file under C:\Program Files\Microsoft Office\Office or Office11 to run as Administrator on Vista?

I tried running as administrator and I tried running Access in Windows XP compatibility mode. It still doesn't show charts without double clicking on them.

Did anyone test this 7 gazillion dollar programme before it went live, or did they assume that no-one uses charts in Access?

Algis Kuliukas

JohnnyG · Apr 10, 2009

Steve Lang said:
I have found a solution, its only temporary as i am moving to Dot Net with my product, in my opinion its a messy solution but it works.

I change the SQL for the graph when required, i add a timer interval for 1 second and then set focus to the chart and then to another object on the form, this forces a repaint displaying the charts correctly. Dont forget to reset the timer interval to zero.

As i said its messy but for a short term solution it has taken the pressure off whilst i move to dot net.

Regards

Steve

Hi Steve.. hope your move to .NET has gone well

But for those of us who have past clients who refuse to consider new technologies when the old worked just fine.. this timer tip has been VERY helpful. I've tried ALL of the other techniques (form/chart refresh, repaint etc etc) and nothing seemed to work.

However, I had problems trying to set focus to the chart as it would report an error - can't set focus to chart object. A more effective solution is to requery the chart in the Form_Timer function.

for those who need to know how this is done, it's quite simple actually and it results in no perceived performance issues. follow these steps:

Under form properties, select On Timer event and choose [event procedure] and browse and add the following code under the Form_Timer function:
{Chart Name}.Requery
Form.TimerInterval = 0
Add the following statement after every time you requery or reset the filter for the chart (and on form open):
Form.TimerInterval = 1

Simple as that.. Note: substituting Requery here with Repaint and/or Refresh DID NOT work for me. I tried using the Form, Me, and Chart objects where the functions were applicable, and none seemed to worked. The requery function would have mixed results if embedded in the code (complex as mine is as it deals with many parameters). Simply best to use it as part of the Timer property.

Hope this helps. And again Steve, thanks for putting me on the right track!

Cheers,

John

BrianM · Dec 2, 2009

Hi there, I have same problem with Access 2003 and Vista , but found that doing timer based .requery does not work; It does work on simple apps but on more complex ones it doesnt so I need a better work-around. Any ideas ?

Brian

Steve Lang said:
I have moved to .net, this hasn't been an easy task and some things that are so easy with access like reporting are more involved and i am also finding that i am writing a lot of low level code. The results so far are very good.

I also had the security problems with access, so i bought a digital certificate, then comes another problem, PC's with scandinavian regional settings do not recognise the id (i have tried 2, 1 from Thawte and also Verisign), this makes your software look amateurish.

The other side to .net that i like is my application runs on its own, and isnt affected by installing other products. I have had several customers of mine totally unable to use the software, either conflicting with access 2007 or a reference issue, it seemd the only way to solve this is with a format.

Access has been good to me, its easy to get started with but i think if you need greater control and connectivity then i think .net is the way to go.

Access 2003 queries on Vista (1 Viewer)

New member

AWF VIP

New member

Smeghead

New member

New member

New member

New member

New member

New member

Smeghead

New member

New member

Smeghead

New member

New member

Smeghead

New member

New member

New member

Similar threads

Users who are viewing this thread