Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Modules _ Removing Ptrsafe

Posted by: Knuckles Oct 17 2019, 10:25 AM


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


Posted by: nuclear_nick Oct 17 2019, 10:44 AM

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... tongue.gif

Posted by: arnelgp Oct 17 2019, 10:48 AM

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.

Posted by: isladogs Oct 17 2019, 10:55 AM

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 http://www.mendipdatasystems.co.UK/available-connections/4594418530

Posted by: Knuckles Oct 17 2019, 10:58 AM


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

Posted by: isladogs Oct 17 2019, 11:02 AM

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

Posted by: Knuckles Oct 17 2019, 11:10 AM


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

Posted by: arnelgp Oct 17 2019, 11:11 AM

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

Posted by: Knuckles Oct 17 2019, 11:19 AM

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

Posted by: isladogs Oct 17 2019, 11:22 AM

There should only be one # on the If line
#If VBA7 Then

#Else

#End if

Just copy the exact code arnel has given you.

Posted by: arnelgp Oct 17 2019, 11:22 AM

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

Posted by: Knuckles Oct 17 2019, 11:22 AM


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

Posted by: Knuckles Oct 17 2019, 11:23 AM


what about the compile error?

Posted by: arnelgp Oct 17 2019, 11:27 AM

leave as-is "user32"

Posted by: Knuckles Oct 17 2019, 01:30 PM

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

Posted by: isladogs Oct 17 2019, 01:42 PM

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'

Posted by: Knuckles Oct 17 2019, 01:49 PM


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

Posted by: PhilS Oct 17 2019, 01:56 PM

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

QUOTE
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.

Posted by: Knuckles Oct 17 2019, 02:04 PM


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

Posted by: isladogs Oct 17 2019, 02:14 PM

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


Two suggestions:
1. Install the https://www.rondebruin.nl/win/dennis/windowsapiviewer.htm 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&targetDoc=windows-api-declaration-vba-64-bit
Also read the linked articles by MS including https://docs.microsoft.com/en-gb/office/vba/Language/Concepts/Getting-Started/64-bit-visual-basic-for-applications-overview

Hope that helps you move forward

Posted by: Knuckles Oct 17 2019, 02:18 PM

I'm on it. Thanks.

Posted by: tina t Oct 17 2019, 04:12 PM

hey, Colin, when i click your posted link for Windows API Viewer, it takes me to a website called Miss Hosting...? tina

Posted by: isladogs Oct 17 2019, 04:22 PM

Hmm...that was the link given in the utility!!!
It sounded worse than it actually is!

Anyway, I've updated the link to one that works https://www.rondebruin.nl/win/dennis/windowsapiviewer.htm
Thanks for letting me know

Posted by: tina t Oct 17 2019, 07:58 PM

you're welcome, Colin, and thanks for the good link! i've already downloaded the zip; just waiting from permission from my IT mgr to unzip it and take a look. :) tina

Posted by: BruceM Oct 18 2019, 10:08 AM

Just to clarify (I don't think it has been mentioned), the VBA7 conditional compilation is needed only if there are Access versions 2007 or earlier. 2010 and later versions of Access use VBA 7, so you can just use the declaration that appears under the VBA7 banner, and eliminate the rest (and get rid of the number sign).

Some variables (in API declarations in particular) indicate memory locations, which are typically referred to as pointers or handles. These memory locations are 32-bit in a 32-bit version of Access, and 64-bit in a 64-bit version. A variable such as hWnd is almost surely such a variable. In a 32-bit version of Access 2010 or later it works to use Long instead of LongPtr, since Long (a 32-bit data type) is the same size as the memory location. However, with a 64-bit version of Access the program doesn't know what to do if a 32-bit value references a 64-bit memory location. To anthropomorphize, it says "Where's the rest of it? I can't proceed until I have the rest".

Declaring a function as PtrSafe means it is ready to accept memory location variables (it is safe to do so). Then when the variable is LongPtr, Access allocates either passes a 32-bit Long value to the memory location or first converts it to LongLong, a 64-bit value that can express a value in the quintillions, I believe, but can also express any value that can be expressed as Long. It doesn't work the other way around, though. A LongLong value cannot be used in a 32-bit Office application. I have not yet had a use for LongLong, but I doubt it is used often outside of specialized situations.

Procedure documentation for APIs should include information about conditional compilation needs, but I will just point out that there are a very few and rather uncommon APIs where it makes any difference if it is a 32-bit or 64-bit version of Office. This is to say the #If Win64 conditional compilation is rarely needed as long as PtrSafe and LongPtr are used.

Posted by: Knuckles Oct 18 2019, 10:33 AM

Thanks for the additional information Bruce. I am making my way through it but I just a hit a bit of a wall.

I modified this function
#If VBA7 Then
Private Declare PtrSafe Function SHFileOperation Lib "shell32" Alias "SHFileOperationA" (lpFileOp As SHFILEOPSTRUCT) As LongPtr
#Else
Private Declare Function SHFileOperation Lib "shell32" Alias "SHFileOperationA" (lpFileOp As SHFILEOPSTRUCT) As Long
#End If

When I debug it fails on the line lnLong = SHFileOperation(shf) in the sub below

Sub CopyFile(ByVal CopyFrom As String, ByVal CopyTo As String)
Dim shf As SHFILEOPSTRUCT
Dim FO_FUNC As Long
Dim GetOptionFlags As Long
Dim lnLong As Long
FO_FUNC = CLng(2) ' 2 is const for copy
GetOptionFlags = GetOptionFlags Or FOF_NOCONFIRMATION
With shf
.wFunc = FO_FUNC
.pFrom = CopyFrom
.pTo = CopyTo
.fFlags = GetOptionFlags
End With
lnLong = SHFileOperation(shf)
End Sub

even if I remove the ptr from longptr.

Can you tell me if I missed something obvious?


Posted by: arnelgp Oct 18 2019, 10:36 AM



#If VBA7 Then
Private Declare PtrSafe Function SHFileOperation Lib "shell32" Alias "SHFileOperationA" (lpFileOp As SHFILEOPSTRUCT) As Long

Posted by: Knuckles Oct 18 2019, 10:39 AM

It still gives me a mismatch.

Might it be related to a different function?

Posted by: Knuckles Oct 18 2019, 10:51 AM

I found an extra longptr, Thank you.

Posted by: PhilS Oct 18 2019, 12:21 PM

QUOTE
To anthropomorphize, it says "Where's the rest of it? I can't proceed until I have the rest".

Not quite. It has no clue all those zeroes in the upper half of the Int64 are not there intentionally. It will read or write the memory at the partial address and then, after some weird effects happened, tell you: "How should I have known you gave me only half an address?"

Posted by: Knuckles Oct 18 2019, 12:25 PM


Thank you all for your guidance. I am on my way and I now have some good resources.

One last question (for now)...

If I miss adding ptr to a long that should have had it will it bark at me and let me know?

Knuckles

Posted by: BruceM Oct 18 2019, 12:29 PM

Ah, these language barriers.

Posted by: BruceM Oct 18 2019, 12:56 PM

If you have 64-bit Office and are using APIs, or perhaps other code that uses memory pointers, quite likely. Actually, it may happen when compiling, but I'm not sure. I think the second link below has more information about that. Remember that replacing Long with LongPtr may or may not work in cases where Long is needed.

In line with what Isladogs pointed out, use LongPtr for any variable that starts with "h": hWnd, hStd, etc. since it is almost surely a handle (memory location).

Here is a https://www.jkp-ads.com/articles/apideclarations.asp about APIs with VBA 7. It is an Excel forum, but that doesn't matter. Here is https://www.codekabinett.com/rdumps.php?Lang=2&targetDoc=windows-api-declaration-VBA-64-bit. Reference documentation is your best friend when dealing with APIs. And there is also the UtterAccess Wiki tab near the top of the screen, which will lead you to an API reference section.

Posted by: Knuckles Oct 18 2019, 03:06 PM

Bruce,

Thanks for the additional resources. As I have been working through the code I am understanding it much more than 2 days ago.

To all of you that weighed in I thank you for your guidance and patience.

K

cheers.gif

uarulez2.gif

Posted by: isladogs Oct 18 2019, 03:10 PM

QUOTE
If I miss adding ptr to a long that should have had it will it bark at me and let me know?


Unfortunately not. Nor will it tell you when you've used longptr incorrectly instead of long
You need to remember that just because PtrSafe allows the code to compile in 64-bit, unless you assign Long/LongPtr where necessary, the code will not actually work correctly.
Using references such as those Bruce & I provided are definitely your best support in this process

I agree with Bruce's comments about only needing conditional compilation where you have users running A2007 or earlier.
However, I always use it as a lot of my clients still run older versions and because I publish many example apps online

Posted by: Knuckles Oct 18 2019, 03:15 PM


Thanks Colin. I guess I won't really know until some users get in there.
These databases have been around for 12 years or so and I have only gotten into them last week.
As far as I know I am the only one using 64 bit.

Posted by: isladogs Oct 18 2019, 03:23 PM

It would obviously help to have at least 1 user on 64-bit!
Because I have to develop for both bitnesses, I have workstations running different versions including 32-bit A2010 (my main development PC) and 64-bit A2019/365.
If your apps are distributed as ACCDE files, you will need to create/distribute different versions for each bitness

ALSO:
Do remember that development should ALWAYS be done in the oldest Access version used by your clients - this is to prevent issues with VBA references

Are there still issues with 'cannot open any more databases errors'?

Posted by: Knuckles Oct 18 2019, 03:29 PM


The databases are all .accdb.
I don't think there was any consideration given to operating systems.

I started 2 weeks ago was given a cubicle and a computer. Didn't know there were any issues until I put my versions on the server.
Live and learn!

For whatever reason there are no more issues with the error message I was receiving.
Coincidentally I used a version of the front end that I converted yesterday.

Posted by: AlbertKallal Oct 18 2019, 06:30 PM

Sounds like you are on your way.

Just as a FYI?
It not really the operating system choice that matters much here. Just about "everything" is x64 bits.

What matters of course is the version of Access (x32, or x64). As of late, I am seeing more x64 bit deployments of office.
(x64 bit versions of office used to be rather rare - but not so much anymore).


Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Posted by: Knuckles Oct 21 2019, 11:39 AM

Greetings all and happy Monday,

We have been testing out the databases that have been converted and received this error message.

Error message:Run-time error '429':
ActiveX component can't create object

here is the code that triggered it...
Function RunProcessChain(ByVal ApplicationServer As String, ByVal Client As String, ByVal System As String, ByVal SystemNumber As String, ByVal UserName As String, ByVal PassWord As String, ByVal ProcessChainEvent As String, Optional TestConnection As Boolean = False) As Boolean
Dim SapBWApp As Object
Dim SapBWLogon, SapBWConnection, SapBWFunction, SapBWEventParamater As Object

RunProcessChain = False
Set SapBWLogon = CreateObject("SAP.Logoncontrol.1")

First question is did I miss something in the conversion that might be triggering this?
Second question is if that is not the case can anyone point me in the right direction? Do I need to start a new post?

Thanks in advance

Knuckles



Posted by: isladogs Oct 21 2019, 01:09 PM

I believe the error occurs when the object already exists. In which case you 'get' it rather than create it.

Place the following code in your error handling routine for this procedure

CODE
If err 429 then
Set SAPBWLogon=GetObject(, "SAPLogonControl.1")
Resume Next
End If


This is air code so check for typos

Posted by: cheekybuddha Oct 21 2019, 01:13 PM

Either that, or the SAPLogonControl is not registered.

Posted by: tina t Oct 21 2019, 01:16 PM

PMFJI, guys, but i'm wondering about the following line, for my education as much as to help Knuckles:

CODE
Dim SapBWLogon, SapBWConnection, SapBWFunction, SapBWEventParamater As Object

am i right in thinking that the last variable is dimmed as an Object, but all the preceding ones are dimmed as Variant?

tia,
tina

Posted by: cheekybuddha Oct 21 2019, 01:17 PM

Yes, Tina, you are correct.

Posted by: isladogs Oct 21 2019, 01:19 PM

Tina
You are correct. Each variable needs to be defined individually or they revert to variant.

Posted by: Knuckles Oct 21 2019, 01:31 PM

It is registered, we are thinking it is that SAP is 32 bit

Posted by: isladogs Oct 21 2019, 01:54 PM

It may well be so, but I doubt that would cause error 429 (famous last words...!)

Posted by: tina t Oct 21 2019, 04:28 PM

thanks, Colin and David. :) tina

Posted by: Knuckles Oct 21 2019, 05:04 PM

Thank you all, we are digging into SAP a bit.

Knuckles