Solved Possibly of Use (1 Viewer)

[DickyP]

Being newly joined I've been browsing the forums an noticed that users are regularly being told how to add references. I got bored years ago with the process when creating new databases so wrote the following procedure which I copy into every new database and run from the immediate window (and then delete).

Thought it might possibly be of use and couldn't find the equivalent anywhere else in the forums. I'm running Office 2021 but I've changed it for versions from 2003 onwards.

If you don't recognize them 'scrrun.dll' is need for using the File System Object and 'vbScript.dll\3' for Regular Expressions.

Public Sub refs()

On Error Resume Next

    ' =========================================================================================
    ' Standard Non Built-In References Used in a typical Access Database with Office Automation
    ' =========================================================================================

    With Application.References
        .AddFromFile "C:\Program Files\Common Files\Microsoft Shared\OFFICE16\MSO.DLL"
        .AddFromFile "C:\Windows\System32\scrrun.dll"
        .AddFromFile "C:\Windows\System32\vbscript.dll\3"
        .AddFromFile "C:\Program Files\Microsoft Office\root\Office16\MSWORD.OLB"
        .AddFromFile "C:\Program Files\Microsoft Office\root\Office16\EXCEL.OLB"
        .AddFromFile "C:\Windows\System32\msxml3.dll"
   End With
End Sub

 

[The_Doc_Man]

The only warning I would make is that the paths that contain \Office16\ (upper or lower case) will change that part of the path string based on which version of Access you are using. AND you might also strengthen this by doing a file check for existence of each of those named files. I'm not sure when the vbscript.dll file first appeared, ditto for the msxml3.dll file. Also, in older versions of Office, I think the paths that included \root\ might have been a little different. I know you said "Standard Non Built-In..." but let's be honest. MS, for all their posturing, is not that big on file path standards.

Not trying to burst your bubble, here, because it ain't a bad idea. But looking for uniformity in MS products MIGHT be the bad idea.

 

[GPGeorge]

Being newly joined I've been browsing the forums an noticed that users are regularly being told how to add references. I got bored years ago with the process when creating new databases so wrote the following procedure which I copy into every new database and run from the immediate window (and then delete).

Thought it might possibly be of use and couldn't find the equivalent anywhere else in the forums. I'm running Office 2021 but I've changed it for versions from 2003 onwards.

If you don't recognize them 'scrrun.dll' is need for using the File System Object and 'vbScript.dll\3' for Regular Expressions.

Public Sub refs()

On Error Resume Next

    ' =========================================================================================
    ' Standard Non Built-In References Used in a typical Access Database with Office Automation
    ' =========================================================================================

    With Application.References
        .AddFromFile "C:\Program Files\Common Files\Microsoft Shared\OFFICE16\MSO.DLL"
        .AddFromFile "C:\Windows\System32\scrrun.dll"
        .AddFromFile "C:\Windows\System32\vbscript.dll\3"
        .AddFromFile "C:\Program Files\Microsoft Office\root\Office16\MSWORD.OLB"
        .AddFromFile "C:\Program Files\Microsoft Office\root\Office16\EXCEL.OLB"
        .AddFromFile "C:\Windows\System32\msxml3.dll"
   End With
End Sub

I'll never forget my introduction to the problem of Office version and references.

I created a little database application on my computer. I scheduled a demo of it for some department heads who might want to use it. The demo was in a company meeting room. It turned out that the computer in the meeting room had an older version of Office than I did on my development computer. The first thing the demo did, in front of a dozen people, was fall over when it couldn't resolve the references to newer versions of Word and Excel than were on that computer.

Morale of the story, never assume that, even within an organization, all Office installations will be the same.

 

[isladogs]

On the same lines as above, if you have 32-bit Office in 64-bit Windows, the paths to Word & Excel need to be modified
e.g. for Word to C:\Program Files (x86)\Microsoft Office\root\Office16\MSWORD.OLB
For Excel, it should be: C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE (not OLB)

None of this is criticism of the basic idea . . .
but I would modify the code to do a version and bitness check then add references in line with those results

 

[Josef P.]

[Application.References.AddFromFile]

As these are all registered libraries, you could alternatively use the AddFromGuid method.

 

[DickyP]

Thanks for the replies - all the warnings are good and as I said I've changed thee entries regularly. Luckily (being retired) I no longer have to worry about Enterprise level consistency so I actually changed from using AddFromGuild. Also, of course, the whole idea's usefulness depends on how often you create new databases.

 

[tvanstiphout]

Alternatively you could use this process.

 

[DickyP]

At Joseph P's suggestion here's an AddFromGuid version.

Public Sub RefsFromGUID()
On Error Resume Next
    ' =========================================================================================
    ' Standard Non Built-In References Used in a typical Access Database with Office Automation
    ' =========================================================================================

    With Application.References
        .AddFromGUID "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}",2,8  ' Office MSO.dll
        .AddFromGUID "{420B2830-E718-11CF-893D-00A0C9054228}",1,0  ' scrrrun.dll
        .AddFromGUID "{3F4DACA7-160D-11D2-A8E9-00104B365C9F}",5,5  ' vbscript.dll\3
        .AddFromGUID "{00020905-0000-0000-C000-000000000046}",8,7  ' MSWORD
        .AddFromGUID "{00020813-0000-0000-C000-000000000046}",1,9  ' EXCEL
        .AddFromGUID "{F5078F18-C551-11D3-89B9-0000F81FE221}",3,0  ' MSXML
   End With
End Sub

 

[Pat Hartman]

I could get behind this idea but I don't like hard coding anything that is changeable. If you upgrade your system or you have to install on multiple PCs with different versions installed, it would be far better to have the GUIDs or file names stored in a table so they can be easily changed.

Personally, I have a base FE that I always start with. It has menus, some common code, some common forms/reports, and simple security as well as my standard lookup table mini-app already installed. From time to time, I change the base and add new features but I rarely change existing features unless I think of a better version. In that case, I might go back and change old apps but probably not.

 

[DickyP]

I could get behind this idea but I don't like hard coding anything that is changeable.

Generally agree with Pat although the whole idea depends on a need to create multiple Access databases regularly. The references I showed in my example were those I'd use today but, of course, they'll changer over time.

Attached is an .ACF file which can be imported into any Access db using the Access built-in LoadTromText hidden procedure.

'LoadfromText acForm ,"fpopListReferences","{path}\fpopListReferences.acf"'.

As the forum won't let files with .ACF etc be uploaded, you'll have to change the extension of the file from .txt to .acf before using it.

This file is a form which allows you to list and see all your references in any current DB and then use 'Build Sub' command button to create a personalised variant of my opening post code.

I hope I've made it such that it should 'run out of the box' but if it doesn't please shout.

 

[Pat Hartman]

Here's a sample that might be easier to use. There are several other samples in the database as well.

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk

 

[The_Doc_Man]

Personally, I have a base FE that I always start with. It has menus, some common code, some common forms/reports, and simple security as well as my standard lookup table mini-app already installed.

I have used similar methods that I have mentioned in other posts as using "templates." The templates allow me to bypass as much as 60% of the work on an individual form because so many of the desired event code and formatting functions are pre-defined, only waiting to be customized with specific names of things. Doing things with a template form also gives you a consistent look-and-feel to what you build, which helps your product - whatever it is - look more professional.

 

[Pat Hartman]

Experience teaches us to reuse what worked whenever possible. There is nothing more wasteful than reinventing the wheel each time you start a new app.