MS Access - created a dB in vers 2.1 !!! with forms, tables, queries, etc: Works perfectly on Windows XP and then current version of Access.

First things first. What is the bitness of the newly installed MS Office 2021, 32 bit or 64 bit?

The libraries you mention would have been fine for 32 bit, but if you now run them in 64 bit, they probably won't be.

That can be remedied, if that is the case.

Next thing, you mention one error (by code only, without the error message, which can be very helpful so we don't have to go look it up). But there are "countless error messages". What are they, by error code and accompanying error message? That detail is almost always highly useful in helping someone help you.

But, given the scenario, I'm guessing there is a good chance the problem is bitness, so let's rule that in or out first.

Thank you.

First things first. What is the bitness of the newly installed MS Office 2021, 32 bit or 64 bit?

The libraries you mention would have been fine for 32 bit, but if you now run them in 64 bit, they probably won't be.

That can be remedied, if that is the case.

Next thing, you mention one error (by code only, without the error message, which can be very helpful so we don't have to go look it up). But there are "countless error messages". What are they, by error code and accompanying error message? That detail is almost always highly useful in helping someone help you.

But, given the scenario, I'm guessing there is a good chance the problem is bitness, so let's rule that in or out first.

Thank you.

Thanks for your speedy reply.

Win 10 64-bit operating system, x64-based processor. MS Office Ver 2402 64-bit
Access LTSC MSO (Ver 2402) 64-bit.

Error msg:

"Compile error: The code in this project must be updated for use on 64-bit systems.
Please review and update Declare ststements and then mark them with the PtrState attribute."

"The Visual Basic module contains a syntax error.
Check the code, and then recompile it."

Returns ERR: 7960

VBE shows:

I was guessing it was more likely to be with the platform level, having started on 16 bit, advanced to 32 bit and now having 64 bit OS & apps.

I must admit I designed this dB 20+ years ago when Access was in its infancy and MS aquired Ashton-Tate, due to their highly successful search engine.

My dB basically stores all the details (Catalogue number, Matrix code, Label name, Artist, Track names, Release dates, Deletion Dates, Acquisition Dates, Notes, etc: for in excess of several thousand 7" vinyl 45s'. One issue I had was not normalising the dB correctly, therefore having numerous relationships to link the various tables. The GUI works well, when it does! and I was happy with the outcome, an easy to read and search by artist, label and title.

You might ask why I did all this? I used Excel a lot and mastered VBA from its inception in Excel Ver5. Transposing that knowledge into Access was reasonably easy, as objects, methods & properties are a common feature of MS apps.

I can see that Access is trying to get me to look at the code in the VBE, but I'm somewhat reluctant to tamper with the code I don't yet understand fully.

Again many thanks.

Roger.

******************************************************

@GroverParkGeorge

Thanks for the vids. It seems that pre-stating the Declare statements with PtrSafe and re-compiling the dB has worked.

The dB is clearly antique now, but its data content was years of work at the British Library, here in London UK.

Old gramaphone companies printed catalogues going back eons was the major source of data.

Clearly I need to normalise better the dB and your colleagues on here also suggest an update to the dB's format to the current .accdb format.

The original concept was to store data by record label name, each label having a seperate table containing fields thus:- Catalogue number, an Artist iD (lists of artists stored in a separate table), 'A' side title, 'B' side title, 'A' side duration, 'B' side duration, + other relevant fields of data.

This has worked well but I realised a long way into the design that I ought to have had another table for the track titles so they could be stored once and not multiple times if more than one artist sang the same title! Consequently, with multiple Forms, one for each Label, there is quite a bit of duplication in the data stored in the dB. The Relatonships looks like a major railway station layout!!

Another issue that has occured, due to MS developments in their Office Suite, is the replacement of Toolbars with a Ribbon, which does not appear to be exposed as an object in the VBE and hence no methods or properties that can be set programaticaly.

I'd be happy to forward the dB as is, if you have the time to review my work and offer any suggestions.

One option I woukld consider is creating a new empty dB in the current format, and then importing the data held in the various tables, as well as the Forms, Reports and Queries. Is that a simple and feasable way forward?

Roger Marson

*** Email address is removed for privacy ***

**************************************************

Each version of Access tightens up on VBA syntax. So, code that might have worked for years may stop working. Also, continuing to use the mdb fo0rmat is another red flag. While I agree with George that bitness is the likely cause (your code is not 64 bit compliant) there may be other issues as well.

We will be glad to to help you but you need to help us help you. Showing the exact text of an error message and the line of code that cause the error will go a long way towards diagnosing the problem.

Thanks.

Your problem is that the APIs used are set up for use in a 32 bit version of Office, i.e. Access in this case.

They must be modified to work in the 64 bit version you now have. The good news is, of course that this has been encountered and dealt with thousands of times, so there is a lot of information available on how to accomplish that.

An internet search on converting 32 bit VBA to bit VBA will turn up many of those articles, blogs and videos.

Here is a good starting video

but there are many others as well

including this one.

It is the bitness of Office that matters, not Windows. So the error message is clearly telling you that the code needs to be updated for 64 bit. These updates are actually pretty simple and are explained fully in the links George gave you.

However, that's only the first hurdle. It may be enough to restore the app to working order. Or there may be other issues once the bitness issue is corrected.

Note: you should keep a safety copy of the app before you start making the changes.

This is a really important point I neglected to mention.

"you should keep a safety copy of the app before you start making the changes."

You can never have too many backups.

Having followed GroverParkGeorge's suggestions and the vid:

Is there a simple way of translating the 32-bit code to 64-bit, or should I think of starting all over again with a new dB in the current format, and import the tables and their data into a new dB?

One issue I found when returning to MS Excel after a long pause (15 yrs), was the introduction of Lists (I remember Database) and the loss of Toolbars, with their replacement by a Ribbon; which does not appear as an object in the VBE, hence cannot have its methods and properties manipulated by VBA.

Roger Marson

*** Email address is removed for privacy ***

*******************************************************

First, congratulations on resolving the first element of the migration. We're all going to face that transition at some point as 64 bit applications replace their 32 bit versions more widely.

Other than that, there may be no other changes needed in the VBA for this particular factor. That doesn't mean you won't find other places where a different, perhaps more efficient, approach is possible, but very little would require modification if everything works as expected now, and all of the VBA compiles.

The ribbon is another matter. If you create a custom ribbon to replace or augment the standard ribbon, the XML which creates it is actually stored in a table in the accdb. It must have this exact name and layout.

Microsoft does not provide built-in tools for creating and modifying custom ribbons, although there is a way to make changes to the standard ribbon in the Options.

Your options are to use a different tool to handle custom ribbons, or learn to edit XML in a text editor. I use a 3rd Party tool called IDBE RibbonCreator. I imagine there might be others available.

This is, unfortunately, an area in which not much improvement has been forthcoming from Microsoft. All I can say is that, once you have figured out how to create and use custom ribbons via XML, it's quite possible to do so effectively despite the lack of built-in tools in Microsoft applications such as Access.

Is there a simple way of translating the 32-bit code to 64-bit,

The PTRSafe change IS the simple way of making VBA code 64bit compatible.

Is there a simple way of translating the 32-bit code to 64-bit,

I copy the whole 64-bit declare apis to a standalone module and remove all 32-bit declare.