How to change references in VBA

[Beginer]

I'm developing using Access 2010 distributed as ACCDE. However, one user is switching to Access 2016 and I suspect (without having tested yet) will cause issues with references and their locations. Is there a way to change these via code in an ACCDE?

The refs. that I use are are;



Visual Basic For Applications
Microsoft 14.0 Object Library
OLE Automation
Microsoft Office 14.0 Access database engine Object Library
Microsoft Word 14.0 Object Library

[RuralGuy]

Here's some links that should be helpful:
http://www.granite.ab.ca/access/latebinding.htm
http://www.trigeminal.com/usenet/usenet026.asp
http://www.accessmvp.com/djsteele/Ac...nceErrors.html

[Beginer]

Hi RuralGuy,

Many thanks for the links. I suspect you wont remember but you've you've saved A$$ numerous times some years ago, nice to hear from you again.

I found this from that genius datAdrenaline which I'm trying to understand and see if I can amend it for my use. I'm putting the link up in case someone else finds it helpful too.

http://www.utteraccess.com/forum/Aut...html&p=1446423

I'm currently trying to interpret the code and see if I cant get it to work for my purposes. I'm giving it a go but suspect it might be a little beyond my understanding

[pbaldy]

Post 3 was moderated, I'm posting to trigger email notifications.

[Beginer]

Sorry you had to intervene pbaldy, I'm new to this forum, did I do something wrong?

[pbaldy]

No, you didn't. Since you're new, the forum software automatically moderated the post because it had a link in it. It's an anti-spammer thing. Once you have a certain number of posts (10 maybe?), it won't do that anymore.

Welcome to the site by the way!

[Beginer]

I haven't touched Access for 5 years due to the onset of early alzheimer's but it's lovely to hear from 2 people that helped me in the past. I feel welcomed already. Ta

[Beginer]

Just read through most of the links supplied by RuralGuy (thanks) and failed abysmally to interpret and change datAdrenaline's code.

I wonder if anyone has any code that'll change references on an ACCDE so I can develop on my current version of Access 2010 but swap ref. locations to 2016 on deployment

[RuralGuy]

Did you consider the Late Binding suggestion?

[Beginer]

I did. It's a new concept for me and over the day I've read up on it as much as I can. I've spent most of my time on it in fact. I get the idea in general, perhaps I could work out how to call and automate word using late binding but then can't work out how on earth I would do it for the following which are set by default:

Visual Basic For Applications
Microsoft 14.0 Object Library
OLE Automation
Microsoft Office 14.0 Access database engine Object Library

[RuralGuy]

I would uncheck them one at a time and try to compile and see what burps.

[CJ_London]

Visual Basic For Applications and OLE Automation will not change and I don't believe can be late bound anyway. And my understanding for Microsoft 14.0 Object Library is that access will just substitute a later version (but not an earlier one). All three are the minimum requirement for libraries, all others are optional and can be late bound.

Not sure what else is in it but the Microsoft Office 14.0 Access database engine Object Library is used for early binding ADODB.

Do what Rural Guy suggests to find out if you have libraries no longer required. BUT take a note of the library name first. Once unticked, the library will return to the alphabetical list below - and there are many libraries with very similar names.

One thing that will matter for .accde is the bit size - you have 32bit access. If your colleague has 64bit access they will not be able to use your .accde. You can check the bit size by going to File>Help and look for the section 'About Microsoft Access. Note this is about the bit size for Access/Office, not Windows

[Beginer]

In relation to burping:
The following two references can't be unchecked as I get a Visual Basic Application message box "Can't remove control or reference; In use"
Visual Basic For Applications
Microsoft 14.0 Object Library

I have a very heavy reliance on 'Microsoft Office 14.0 Access database engine Object Library'. Too much for me to try and change and still meet the deadline which is 2 days away.

The only ref that didn't 'burp' when removed was OLE Automation which lives in C:\Windows\SysWOW64\stdole2.tlb so I'm assuming I can remove this but perhaps wouldn't have caused an issue anyway considering it's location.

I'm hoping as Ajax suggest that once I run the ACCDE on the user's machine Access will automatically pick up the new locations for refs (although I can't ever recall being so lucky). Certainly the location of 'Visual Basic For Applications' is currently C:\PROGRA Files(x86)\COMMON Files\MICROSOFT SHARED\VBA\VBA7\VBE7.DLL so I suspect this will be fine. However, 'Microsoft 14.0 Object Library' is currently located at C:\Program Files (x86)\Microsoft Office\Office14\MSACC.OLB and I rather suspect the new location will be in Office16 and may cause issues as will 'Microsoft Office 14.0 Access database engine Object Library' (but fingers crossed Ajax is correct about that access substituting for a later version).

The bit sizes are the same, both running 64 bit windows and 32 bit Office. That's an issue I we'll all have to face sometime further down the line if MS decide to drop the 32bit version (God help us all when that happens).

I suppose as a last resort I could change the ref's by copying the ACCDB onto the users machine, changing the refs then creating the ACCDE file and then removing my ACCDB, but I'm very very reluctant to do that for various and perhaps obvious reasons.

Certainly getting re-baptism of fire with Ms.A.

[CJ_London]

you could try a quick test

create a simple .accde including the v14 libraries a small table and form with a bit of code. send it to your 2016 user and see if he can open it. If he gets referencing errors you know you are going to need to fix it.

I was wrong about the Microsoft Office 14.0 Access database engine Object Library - it replaces the old (2003 and earlier) DAO 3.6 library - so is actually to do with DAO objects e.g.

dim db as DAO.Database
dim rst as DAO.Recordset
etc

so your bit of code could be simply to open a recordset and use a msgbox to display the recordcount.