Database libraries references

[tatarik]

Dear all,

I have been searching the web for an answer, I might have found it but I still don't get it. :banghead:

I'm trying to deploy a database to several users, and I would like to make sure they all have a valid reference to the Microsoft Outlook Object Library so as to be able to send emails with attachments from Access.

On the computer on which I developped the database, it is located at : Outlook: C:\Program Files\Microsoft Office\Office14\MSOUTL.OLB, but on some of the users computer, the reference library might be C:\Program Files (x86)\Microsoft Office\Office15\MSOUTL.OLB.

Is this a problem with the library OLB or finding the path to it?

From what I can gather online, I have the following options:
1. Copying the OLB to each one of the users'computers manually. But it sounds a little the most painful option, and probably harder to sustain over the long-run. (link)

Another method of verify the correct reference is by including files in the application's root folder. As this is the last folder that get checked for libraries before VBA decides that they don't exist, it is a good place to store various reference files that may not be available elsewhere in a particular environment.

2. Late binding seeems like an extreme solution: I understand that I would have to modify my underlying code accordingly (rplace the 'New' statements by a 'Create Object' (?) and in the end, I guess the others that do not have the required library won't be able to enjoy the full functionality in the database I've distribute to them...

3. Programmatically (via VBA code or macro?) ensure all libraries references are valid before the database launches. And if they're not, reconnect them with a Declare statement (link)
Something like:

Declare Function DllRegisterServer Lib "<yourlib.dll>" () As Long

This methods has my preference for now, but I'm not sure how to implement it. How do I declare the library file path?

 

[Rx_]

In vba module (where there is code) go to Tools and References.
The OLB are registered as part of your Registry.
This provides common ground for the application distribution.

For example, one PC could have Excel installed in a different directory.
But the Tools Reference will still point to the Excel Object.

This is why many offices will switch to a virtual machine such as Citrix.
All the instance run on one single server. The ICA client like a lightweight IE give the user access to the virtual server. This way, everything is consistant and running as a template of a virtual workstation.

The real key in your situation is to have all PC workstations with at least the same version. There is code to connect to an avalilable version as the application starts up. Personally, I don't like late binding. I am too forgetful to work with out the intellesense.

 

[pbaldy]

I've used late binding several times. The only real headache is not being able to use constants, but it's not that hard to find the numeric equivalents. No experience with 1 or 3, so I can't give a comparison of pros and cons.

 

[tatarik]

Thanks to both of you.
@Rx: I'm not sure this is even realistic for me to go into this territory. Virtual machiens/desktops... In this case a manual copy/paste of the OLB file would be easier -- provided I have to do it on a handful of computers.

@Paul: If I go for late binding, does it mean that I have to use constants everywhere in my VBA code?
Also, will the email work for users that do not have a reference to the Outlook library? :0

Otherwise, I just thought of
4. Sending emails from Access VBA without any reference to the Outlook library.

Not sure if this is even possible though :-0

 

[pbaldy]

@Paul: If I go for late binding, does it mean that I have to use constants everywhere in my VBA code?
Also, will the email work for users that do not have a reference to the Outlook library? :0

Otherwise, I just thought of
4. Sending emails from Access VBA without any reference to the Outlook library.

With late binding you don't include a reference. The code would (should) work as long as the user has some version of Outlook (obviously you couldn't use a feature not available in an older version).

It would mean you couldn't use constants that you can use with early binding. In other words, to start an email with early binding I can do (using made up constant in red because I don't remember it):

Set MyMail = MyOutlook.CreateItem(acNewMailItem)

with late binding you can't use the constant, so the code becomes

Set MyMail = MyOutlook.CreateItem(0)

because 0 is the numeric value of acNewMailItem.

 

[tatarik]

From what I can gather online, I have the following options:
1. Copying the OLB to each one of the users'computers manually. But it sounds a little the most painful option, and probably harder to sustain over the long-run. (link)
2. Late binding seeems like an extreme solution: I understand that I would have to modify my underlying code accordingly (rplace the 'New' statements by a 'Create Object' (?) and in the end, I guess the others that do not have the required library won't be able to enjoy the full functionality in the database I've distribute to them...

3. Programmatically (via VBA code or macro?) ensure all libraries references are valid before the database launches. And if they're not, reconnect them with a Declare statement (link)
Something like:

Declare Function DllRegisterServer Lib "<yourlib.dll>" () As Long

This methods has my preference for now, but I'm not sure how to implement it. How do I declare the library file path?

Does anyone have experience with methods 1 or 3, if possible? :-0

 

[spikepl]

Why on earth would you want to mess with 1 or 3, when late binding does away with all worries in this area? And there is absolutely nothing "extreme" about late binding - it is a fairly common practice to develop using early binding (to make full use of Intellisense) and then switch to late binding before release. Solution 1 may prove undoable anyway, because it depends on IT-policies set by the customner'sd IT people. And 3 is redundant if you use late binding.

 

[gemma-the-husky]

Not sure, but

1. May run foul of ms licensing and copyright
3. You can verify references are not missing in code, but you cannot fix them in an mde/accde

You can use the lowest library you might require. Access will upgrade automatically, but cannot downgrade

So if you design for outlook 2003, you should not encounter any issues.

 

[CJ_London]

It is very little more work - I tend to develop with early binding then convert to late binding before distribution

Set MyMail = MyOutlook.CreateItem(acNewMailItem)

with late binding you can't use the constant, so the code becomes

Set MyMail = MyOutlook.CreateItem(0)

But you can create you own constant

Public Const acNewMailItem=0

Whilst still early bound, you can either type ?acNewMailItem in the immediate window to determine its value or use the Object browser to find them that way.

 

[smig]

You can easily copy OLB files if you use some kind of install tool.

In one aplication I reference to an MDE I built. On the first run I remove the reference and add it again to make sure it reference the corect one in the corect place.

 

[tatarik]

Guys, I really want to thank you all!

I guess it took me some time to understand what late binding was about. Because I imagined the lines of code depending on the Outlook library wouldn't work, I was quite reluctant to switch to late binding.

I just adjusted my code based on pbaldy and CJ_London suggestions, and I was really suprised to see that the outlook procedure (sending an email) worked perfectly even without having ticked the reference to the Outlook 15.0 OLB reference in the libraries.
What a surprise!
I also thought that if going for late binding I would have had to somehow translate my entire project in late binding standard, but I just had to do it for the procedure that involved the Outlook library.

I guess my problem is solved since my project will work for all users regardless whether they have ticked the added references library in their Access.

Thank you so much!

 

[tatarik]

I simply (1) used objects instead of Outlook specific objects, and (2) replaced constants with their numerical values.

'--------------------------------------------------------------------------
'The following procedure sends the email.
'--------------------------------------------------------------------------
'LATE BINDING
'Note: Compile with Option Strict Off to allow late binding.
'--------------------------------------------------------------------------
Dim objOutlook, objOutlookMsg, objOutlookRecip, objOutlookAttach As Object
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(0) '?olMailItem = 0

With objOutlookMsg
   ' Set the Subject, Body, and Importance of the message.
   .Subject = "Certificates"
   '------------------------------------------------------------------------
   'Make the email body text:
   .Body = strText 
    '------------------------------------------------------------------------
    .Display
End With

Set objOutlook = Nothing

 

[spikepl]

Dim objOutlook, objOutlookMsg, objOutlookRecip, objOutlookAttach As Object

I am surprised that that worked, because that declares all varables as Variant, apart from the last one. You need to specify type for each and one variable.

 

[smig]

I am surprised that that worked, because that declares all varables as Variant, apart from the last one. You need to specify type for each and one variable.

As I know a variant variable will get anything you put into it.

 

[tatarik]

I am surprised that that worked, because that declares all varables as Variant, apart from the last one. You need to specify type for each and one variable.

Sorry spikepl, I did not understand that. What would you suggest I put instead of the below:

Dim objOutlook, objOutlookMsg, objOutlookRecip, objOutlookAttach As Object

I did this trying to replicate what I found on the Microsoft Office documentation here (sample code below).

' To use this example, you must have Microsoft Excel installed on your computer.
' Compile with Option Strict Off to allow late binding.
Sub TestLateBinding()
    Dim xlApp As Object
    Dim xlBook As Object
    Dim xlSheet As Object
    xlApp = CreateObject("Excel.Application")
    ' Late bind an instance of an Excel workbook.
    xlBook = xlApp.Workbooks.Add
    ' Late bind an instance of an Excel worksheet.
    xlSheet = xlBook.Worksheets(1)
    xlSheet.Activate()
    ' Show the application.
    xlSheet.Application.Visible = True
    ' Place some text in the second row of the sheet.
    xlSheet.Cells(2, 2) = "This is column B row 2"
End Sub

 

[pbaldy]

The equivalent would be

Dim xlApp As Object, xlBook As Object, xlSheet As Object

You should specify the type of each variable.

 

[avinpandey]

use below code:

.VBProject.References.AddFromFile (Application.path & "\MSOUTL.OLB")


So it will automatically detect your path.
(Application.path & "\MSOUTL.OLB")

 

[The_Doc_Man]

The real complaint is this:

Dim objOutlook, objOutlookMsg, objOutlookRecip, objOutlookAttach As Object

In that syntax, only the LAST object in the line is treated as an object. The "AS xxx" syntax doesn't distribute across the comma-separated name list.

Dim objOutlook [COLOR="Red"]As Object[/COLOR], objOutlookMsg [COLOR="red"]As Object[/COLOR], objOutlookRecip [COLOR="red"]As Object[/COLOR], objOutlookAttach As Object

is the correct syntax to make those items objects.

The comment about "variants" is because the default datatype for a DIM statement is a Variant, which can be anything you want it to be INCLUDING an object. So your code would work anyway. The reason you DON'T want too many variants in your code is that if you try to assign the wrong type to a variable, there is no such thing as a type mismatch. If you have a variant, you can sequentially assign values that are LONG, DOUBLE, DATE, CURRENCY, BOOLEAN, an array, and any kind of application object - and there will NEVER be an error. However, if you have something that is a more specific data type, the run-time system will trap a bad assignment and tell you about it.

 

[CJ_London]

Reviving a 4 year old thread?

 

[sonic8]

use below code:

.VBProject.References.AddFromFile (Application.path & "\MSOUTL.OLB")

The Access.Application-Object has no Path-Property.
Even if it had, you could not rely on this being the path to the library. You could be in a runtime installation or a different version of Office where the path of the application does not match the path of the library.
And the above does not work in an compiled accde file.