New Bug - Connection String malformed !

[Minty]

Hi All,
After updating to version 2312 (Microsoft® Access® for Microsoft 365 MSO (Version 2312 Build 16.0.17126.20126) 64-bit)

We have a new and nasty bug - if you check and reset a connection string using

? currentdb.TableDefs("AnyLinkedTableNameGoesHere").Connect

The returned result is malformed :

And fails.
This is a nightmare, we reset the connection string on reusable pass-through queries every time they are run, in loads of our databases, as it avoids issues with changes of drivers, development backend switching etc. So code like this fails;

    Set qDef = db.QueryDefs("qPT_Generic")
    qDef.Connect = db.TableDefs("tbl_Employees").Connect
    qDef.SQL = strQuery
    qDef.ReturnsRecords = bRetRecs
  
    If Not bRetRecs Then
        db.Execute "qPT_Generic", dbSeeChanges & dbFailOnError
    Else
        qDef.Close  'db.Execute "qPT_Generic", dbOpenSnapshot
    End If

It's definitely the update to 2312 that causes it, as I forced the update this morning after a client had the issue on one machine, and I immediately have the issue, which wasn't there prior to the update.

 

[Minty]

And after some searching this appears to possibly be a similar issue that was claimed as fixed, but doesn't appear to have been!

RefreshLink has started failing with Error 3611 - Version 2312 (Build 17126.20014) - Microsoft Q&A

Having posted in the Community forum, it has been suggested that this is a better place to ask my question. I have a split Access database that has started raising Error 3611 (Cannot execute data definition statements on linked data sources) in the…

learn.microsoft.com

@isladogs - Colin, I can confirm this bug has surfaced in the update I did this morning, and on two client machines that have automatically updated today/overnight.

 

[isladogs]

Hi Minty
Thanks for the feedback.
Yes, its a serious bug but I believe unrelated to the 3611 error from November
The issue has already been reported & action taken by the A-team.

If you carefully check the connection string you will see it has been modified (not by the client but somehow by the update) and the required semicolon after ODBC is missing. The issue was reported yesterday, a fix has already been prepared & it should be fixed in next Tuesday's patch.

 

[Minty]

Thanks for the speedy update Colin.

I just hoping too many clients don't get updated in the meantime.
The larger ones are mainly on Enterprise channel so won't be affected, but the smaller ones will be, and rolling back an Office update is not a simple task for most end-users.

 

[isladogs]

Thanks for providing the version info which I was able to confirm as Current Channel

If the links aren't refreshed, do the existing links get messed up or is it just when they are refreshed?
I've checked and, with the DSN-less connections I use, the issue doesn't arise on a refresh as I don't have ODBC; as the start.

This is the EE thread where the issue was first reported yesterday:
TableDef Connect string missing semi-colon | Experts Exchange (experts-exchange.com)
As a temporary work-round can you edit the conn-string to reinsert the semicolon (or revert to a backup where the string is still OK?)

There will be a short article for Access Forever hopefully later today about this bug & the speedy fix

 

[Minty]

It's only when the links are refreshed using the reported table def connection string.
Its returned incorrectly in every database opened with the later version using the .connect property, so a backup doesn't help.

It's a technique we use to save having to reconnect any pass through queries when we change from DEV, TEST and Prod environments.

We can fix it by hard coding the conn string as a Global Const and updating the method, but we use it everywhere, and often have a number of reusable passthroughs in each database for various different functions depending on complexity.

It also makes swapping backends a much more manual process.

If you look in the system table the connection string is still correct, it's the function/property lookup that is broken.

 

[NauticalGent]

Good morning Colin - just in case nobody has mentioned it, allowing you to join the MVP ranks was a solid decision. Good to know we have a "Man-In-Havana"...

 

[isladogs]

@Minty
I'm somewhat confused by your comment about MSysObjects
For me, ODBC; appears in the tab tip popup when hovering over the linked table. It does not appear in MSysObjects or the LTM

@NauticalGent
Glad to be of use occasionally....

 

[Minty]

@isladogs
Sorry, what I meant was that the connection string as used in MSysObjects wasn't malformed, although as you state it is stored in a slightly different format.

And yes I agree, hovering over the table the link appears correctly, but I assume is pulled together using a slightly different process, as it is missing the password etc.

 

[isladogs]

@Minty
The Access Forever article was published this morning Missing Semicolon in ODBC Connect (accessforever.org) after a lot of discussion & testing amongst group members. Please read it and let us know if it describes the issues accurately from your experience. There is a comment section you can use at the end of the article.

Everyone who has experienced this agrees that the semicolon goes missing from the connect string but after that findings differ.
The 3 original forum respondents including yourself report issues with both the linked table manager and code.
For another AFo member, only code relinking is affected (not using the LTM)
Yet for me it has no impact whatsoever – links still work whether done through the LTM or in code.

Let us hope that the fix is available to all next week and that it solves all the issues.

 

[Minty]

@isladogs
I have subsequently rolled back my update, and advised clients to disable office updates for at least a week, if they are on the normal channel release.

As a result I can't comment on the other aspects, but the description in the article is spot on from the diagnosis I performed at the time.
I didn't try the normal LTM, as it wasn't affecting existing table links only the relinking in code of Pass-through queries.

The routines we routinely use to relink to different backends use connection strings that are hard coded or string lookup's from our own system tables, so again these wouldn't have been affected.

 

[isladogs]

Thanks for the clarification. It would be appreciated if you would comment in the AFo article as well.
It may be worth us adding a sentence about the effect of relinking in code for P-T queries which I personally haven't tested since 2312 was released

 

[isladogs]

Good news.
Build 17126.20132 has been released to the Current channel on January 9. According to initial tests, it fixes the bug.

Surprisingly, those (like myself) who are on the Beta Channel will have to wait a little longer for the fix to become available

 

[Minty]

I'm a glutton for punishment , I'll update when I've finished what I'm on at them moment test and report back.

 

[isladogs]

Just to add, the latest build may have spawned a new bug for Access DBs with SQL Server BEs where an old driver is used.
See this new thread:

Access w/SQL Linked tables Slow/not responding started today

Hello, I am having an issue in 2 different offices that use complelty different access databases and SQL server. Both of them are experience slowness/not responding when performing certain things. I am pretty sure this is Access related because I have one PC that is downgraded to Novembers...

www.access-programmers.co.uk