[X]My Assistant

[Knuckles]

Greetings UA,

I inherited a few 32 bit databases.
Added ptrsafe and they seem to run ok on my machine with O365.

Since not all users have been upgraded to O365 we thought we could have parallel front ends pointing to the same back end. That's when something unexpected happened...

A 32 bit user was able to open the front end successfully.
I got an error that came up in a loop "Cannot open any more databases"
I then removed ptrsafe from mine and passed the front end back to the 32 bit user that successfully opened it before and he now got the same error message.

Is that to be expected? Is it not possible to go back once opened in O365?

This is the tip of the iceberg so any direction would be much appreciated.
Thank you in advance.

Knuckles

[nuclear_nick]

Allow me to help you clarify your post...

1) O365 is a subscription service, not an Office version.
2) O365 does not automatically mean the subscriber has 64 bit Office.

Finally, a question of my own... have you looked into ALL the possibilities behind the message "Cannot open any more databases"? I don't offhand know of any that would be caused by ptrsafe, but perhaps someone else will come along and help with that part...

[arnelgp]

same access version (32 or 64), there won't be any problem.
newer version has newer system tables.
so older version will complain about it.

for compatibility enclosed all your api in:

#If VBA7
Private Ptrsafe Function
#Else
Private Function
#End If

use Currentdb instead of Dbengine(0)(0) on your code.
and always destroy (set db = Nothing), when exiting
from any sub/func, to eliminate the error.

[isladogs]

To add to Nick and arnel's comments, the way to deal with mixed 32/64-bit versions of Office is using conditional compilation.

The cannot open any more databases error is usually seen when Access has a lot of objects open at once e.g. Tables, forms, reports, recordsets.
Each open object uses up some of the 255 connections theoretically available.
For that reason it is a good idea to close objects not currently in use
You may find this utility useful Check Available Connections

[Knuckles]

I am running 64 bit.

The error is triggered by this line in a loop
strEmail = Nz(DLookup("[E-mail]", "tbl_UserSecurity", "[Person_Name] = '" & rst.Fields("Requester") & "'"), "")

looks like it failed on the 12th iteration.

Is this correct?
If VBA7 Then
Declare PtrSafe Function SetWindowPos Lib "user32" (ByVal hwnd As Long, _
ByVal hWndInsertAfter As Long, _
ByVal x As Long, _
ByVal y As Long, _
ByVal cx As Long, _
ByVal cy As Long, _
ByVal wFlags As Long) As Long
Else
Declare Function SetWindowPos Lib "user32" (ByVal hwnd As Long, _
ByVal hWndInsertAfter As Long, _
ByVal x As Long, _
ByVal y As Long, _
ByVal cx As Long, _
ByVal cy As Long, _
ByVal wFlags As Long) As Long
End If

[isladogs]

Run a debug.print aster the strEmail line in the loop.
Check the available connections as suggested.

Changes needed
Use # before If, Else, End if in the conditional compilation
Use LongPtr for both hWnd items in the VBA7 part

[Knuckles]

Thanks Colin, I will check out the available connections and will try the debug.print

[arnelgp]

CODE

#If VBA7 Then
    Declare PtrSafe Function SetWindowPos Lib "user32" (ByVal hwnd As LongPtr, _
             ByVal hWndInsertAfter As LongPtr, _
             ByVal x As Long, _
             ByVal y As Long, _
             ByVal cx As Long, _
             ByVal cy As Long, _
             ByVal wFlags As Long) As Long
#Else
    Declare Function SetWindowPos Lib "user32" (ByVal hwnd As Long, _
             ByVal hWndInsertAfter As Long, _
             ByVal x As Long, _
             ByVal y As Long, _
             ByVal cx As Long, _
             ByVal cy As Long, _
             ByVal wFlags As Long) As Long
#End If

This post has been edited by arnelgp: Oct 17 2019, 11:12 AM

[Knuckles]

Everything turned red and I got a compile error on the second # in the first line.

[isladogs]

There should only be one # on the If line
#If VBA7 Then
…
#Else
…
#End if

Just copy the exact code arnel has given you.

[arnelgp]

it will turn red on x64 office, that is fine.

[Knuckles]

Declare PtrSafe Function SetWindowPos Lib "user32" (ByVal hwnd As LongPtr should this be"user32" or 64?

[Knuckles]

what about the compile error?

[arnelgp]

leave as-is "user32"

[Knuckles]

I have gone through all the declarations and I am now getting a "mismatch" on WNetGetUser

' Get the log-on name of the person using product.
Status = WNetGetUser(lpName, lpUserName, lpnLength)

Is there more to it than just modifying the declarations?

#If VBA7 Then
Declare PtrSafe Function WNetGetUser Lib "mpr.dll" _
Alias "WNetGetUserA" (ByVal lpName As String, _
ByVal lpUserName As String, lpnLength As Long) As LongPtr
#Else
Declare Function WNetGetUser Lib "mpr.dll" _
Alias "WNetGetUserA" (ByVal lpName As String, _
ByVal lpUserName As String, lpnLength As Long) As Long
#End If
This post has been edited by Knuckles: Oct 17 2019, 01:32 PM

[isladogs]

I believe the first part should with As Long and not As LongPtr

How are you doing these conversions? Are you looking them up or just hazarding a 'guess'

[Knuckles]

Let's just say I am in water well over my head...

[PhilS]

I got an error that came up in a loop "Cannot open any more databases"

The error is triggered by this line in a loop
strEmail = Nz(DLookup("[E-mail]", "tbl_UserSecurity", "[Person_Name] = '" & rst.Fields("Requester") & "'"), "")

looks like it failed on the 12th iteration.

DSomething and Recordset related code may very well cause an "Cannot open any more databases"-error. - PtrSafe may not, because it has no effect past compilation.
You're barking up the wrong tree.

[Knuckles]

Thank you Phil. We did think they were separate issues.

[isladogs]

Let's just say I am in water well over my head...

Two suggestions:
1. Install the Windows API Viewer which covers the 32-bit/64-bit conversions for most common APIs. Unfortunately not all.
2. Read articles explaining how to do such conversions e.g. one by Phillip Stiefel at https://codekabinett.com/rdumps.php?Lang=2&...tion-vba-64-bit
Also read the linked articles by MS including https://docs.microsoft.com/en-gb/office/vba...ations-overview

Hope that helps you move forward