[AccessD] Upsizing ACCDB to SQL Server

[Stuart McLachlan]

In line:

On 19 Oct 2021 at 20:04, Arthur Fuller wrote:

> In all my many years of Access development, I don't recall ever using
> the DECLARE statement, so please forgive my ignorance It would appear
> that it's fpr developers that must maintain versions of an app's code
> that depend on such things as 32 or 64 bit. Is that correct?  Is that
> all there is to it? Am I missing something? Couldn't that be done with
> compiler directives? Please advise. I am not trying to start a war
> here, just trying to learn..
> 

You use DECLARE if you are using anything other than a built in  VBA function or a function 
which has been writtne in a VBA module in the application.  
Generally that will be a WIndows API function or a function in a third part DLL. 
It is a "pre-process instruction to the compiler so that it knows how to construct a call to the 
function.  (It's like the first line of a VBA Function() with the addition of the location of the 
DLL containing the function)

The WIndows API has both 32bit and 64 bit versions of all the system DLLs.

You DECLARE any Windows API function you are going to use as though they are in 
%WINDIR%\System32\.  That is where the 64biyDLLS are physically lcated.  If you are 
working with a 32 bit application, Windows transparently changes that to SYSWOW64 to 
point to the 32 bit DLL.

Third party DLLs are a different issue since you have to actually point at either a 32bit or a 
64bit DLL depending on whether you are using 32bit or 64bit Access.

32bit DLLs use a 32 bit pointer to memory locations, 64 bit DLLs use a64bit point.  WHich is 
why you need to make any refeerence to such pointers in Access a LONGPTR rather than a 
LONG- that way Access will use the correct sized variable when it compiles your VBA.

The keyword PTRSAFE is just a signal to the VBA compiler to tell it that your declares have 
been written correctly. Without it, Access will complain that it doesn't know whether your 
LONGs in your declarations are true 32bit variables or whether they should be 
LONGLONGs in a 64bit Access environment.


> Following up on something Stuart wrote, and given an app of
> reasonably large size, is there some simple way to find all API calls,

Just do a Find on "DECLARE" in the VBE with "Current Project" selected. and you can step 
through them all of the non-internal functions usedi in your project.

You don't need to find "all API calls".  Once they have been declared, they are no different 
to any other function call in your project.

> so I can isolate them and deal with them prior to trying to migrate a
> 32 bit FE to a 64 bit equivalent?

The DECLARE statements will always be in the  first lines in any VBA module.before any 
Sub/Function code.  If they appear anywhere else, the module won't compile.

>  Perhaps there is  a more fundamental

> question here: what does one gain by migrating? Performance, capacity
> (meaning larger data sizes of types and tables and databases),
> arithmetic accuracy in binary/decimal conversions? I'm just guessing
> here, I really have no idea  Please explain or link me to an
> explanation of why it is worth the  effort to convert from 32 to 64
> bit. what are the gains? 

There is no such thing as a 32bit or 64bit "FE", or of "converting" from one to the other. .  
(Unless your application has been compiled to an accde or it uses "Add-Ins" - see Add-Ins 
under the Database Tools Menu)

It's just a matter of whether your .accdb file has been opened by someone with 32bit or 64bit 
Office installed on their computer.  The same file will run on either version if it is properly 
built unless you use "Add-Ins". In which case, you would need two different versiosn - one 
with 32bit Add-Ins linked and one linking 64bit Add-Ins.


Since 64bit is now the default Office installation, there are less and less instances of 32bit 
Access out there and you really must ensure that your application is 64bit compatible if you 
intend to distribute it.  

 
> I'm in one of those rare situations where it's the client who wants to
> move to something new (64 bit) and I am the reluctant one, wondering
> why. So somebody please tell me what the app will gain by this move.

If you don't have any DECLARE statements in your project and you don't use any "Add-Ins", 
the  fact that your client is using 64bit Office is irrelevant.  Your application should function 
exactly the same as it does on 32bit Office.