Problem with Access after Office Update

Question

Hello,

He distribute an application which uses Msaccess as a front end Linked to an SQL database.

Some of the more complex proccesses will utilise sql usually with pass through queires.

After office update I beleive version 2005, the version number in Access is 16.0.12827.20200 we are experiencing error 2004, There isn't enough memory to perform this operation. Close unneeded programs and try the operation again.

Below is a function I have written to replicate this behaviour in vba, the live code causing the error is calculating totals, profit and profit percentages and is complex.

---

Public Function runtest()

On Error GoTo 0

Dim s As String

Dim MySet As DAO.Recordset

Dim qdf As DAO.QueryDef

Dim var As Variant

s = "select Cast(Sum(Cast(248.83 as numeric(24,8))) / cast(SUM(272.16)as numeric (24,8)) as numeric(24,8)) As Calc"

Set qdf = CurrentDb.CreateQueryDef("")

qdf.Connect = getglbSQLConnString

qdf.SQL = s

qdf.ReturnsRecords = True

Set MySet = qdf.OpenRecordset(, dbSQLPassThrough)

If Not MySet.EOF Then

    MySet.MoveFirst

    Forms![Test]![TxtTest].Value = Nz(MySet![Calc], 0)

    ' var = MySet![Calc]

End If

End Function

---

Setting the result to a form controls value throws the error.

Setting the result to a variable throws no visual error. However if you try to interact with the variable in any way access will crash / close.

Looking in the locals window the field property value for the recordset is "".

In versions without the office update the value is present.

I have run this on older version of access which i presume have not had any recent update and all works fine.

Office / Access 365 version that have not been updated are working fine.

We have been encouraging our customers to migrate to office 365 which has been going well and so far.

Three sites have reported the error but as the update rolls out I fear more will report this.

Alot of our customers are still using older versions of office / access I have not looked into the updates for these yet.

Therefore any assitance on this matter will be grately appreciated.

Answer 1

Thank you for your feedback. Very soon, we will ship a new data type, known as Date & Time Extended, which enhances syntax compatibility with SQL while increasing accuracy & level of detail in date & time records.

While the feature is not yet enabled in Current Channel builds, most of the code for the feature is in the product in an inactive state. Nonetheless, we are aware of a problem that currently exists with this new code. As of today, if you are on version 2005, build 12827.20010 or greater, and you manipulate Decimal fields (Access DataType=Number/FieldSize=Decimal, or SQL DataType=Decimal) using DAO (Data Access Objects), you might have identified your app crashing. If you hover a variable with the type in VBA code, you may see that the field is not being displayed properly; either reading as ‘?????’ or giving a Type Mismatch error, or Access may crash.

We are very sorry for the inconvenience this causes in your Access app. The Access team is working on resolving this issue as soon as possible, and we will report back to you once this error has been fixed. In the interim, we’d recommend for you to roll back to a previous version (https://support.microsoft.com/en-us/help/2770432/how-to-revert-to-an-earlier-version-of-office-2013-or-office-2016-clic), or switch to a slower channel (e.g Monthly Enterprise Channel, or SemiAnnual Channel), until the issue is fixed in Current Channel.  The issue only impacts Decimal types so if avoiding DAO code that manipulates Decimal types is possible, we’d advise this as a short term solution.

Answer 2

There have been several reports of issues with version 2005.  So I'd recommend reverting your build number to a prior build.  This will quickly identify if it is that build causing your current issue.

Your build also indicates you are on the Monthly update channel which means you are on the leading edges of updates, this the most prone to bugs.  I'd urge you to switch over to a more stable update channel such as Semi-Annual (Targeted) or control the update process yourself manually.

I'd also urge you to report this issue by using the feedback command from within Access.  Give sad much detail as you can, and your email address so they can followup with you if needed.

Answer 3

Hello Ebo,

Thank you for your reply, excited to hear about the new data type looking forward to that.

Thank you for your explanation and advice, we are currently working around the issue in various ways and appreciate you efforts to resolve it.

Kind Regards, John

Answer 4

Hello Daniel,

Thank you for your prompt reply.

I will investigate what you have said and definately report via the feedback in Access.

I think I need to start spending more time with 365 and move away from developing in the older versions.

I dont usually post or comment on sites but would just like to say thank you for all your articles and posts via the various platforms.

They have helped me out alot over the years so thank you very much.

Kind Regards, John

Answer 5

"Give sad much detail ..."

Unintentional, but totally appropriate autocorrect.