VBA References

[Guest]

When I modify my database using version 2003 some of the VBA references
disappear when users using version 2000 try to open it. It only seems to be
the references for Outlook & Excel. I have to then go into the references and
reselect them using version 2000. When I open the references in 2000 it shows
those two "MISSING". Why is that? Is there a way to fix this?

 

[Allen Browne]

If you code includes references to Outlook and Excel, they are version
specific. When you open the database in a different version, Access sorts
out the reference to the Access library, but (as you found) not to the
libraries for the other Office products.

If you are certain that the other machines your database runs on will have
some version of Outlook and Excel on them, you can solve the problem by
using late binding. Here's the info to get you started:
http://support.microsoft.com/kb/210111/en-us
http://support.microsoft.com/kb/260410/en-us

If you cannot be certain, the safest choice is to avoid using those
additional libraries, and do what you need just with the standard Access
libraries and your own code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Guest]

Well I know a few of the machines it runs on do not have outlook on them so I
assume that would cause a problem. Is there any way to automatically load
these references when the DB opens? I need to libraries due to the code I'm
using.

 

[Allen Browne]

Hmm. There are questions here, e.g. do you have the right to distribute the
Outlook library onto machines that do not have a valid copy of Outlook on
them, register the libraries, and then use them from Access?

If Outlook is not on those machines, you probably need to investiage
alternatives. For example, if you are just trying to send an email,
SendObject should work with any MAPI-compliant email client.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Tony Toews [MVP]]

When I modify my database using version 2003 some of the VBA references
disappear when users using version 2000 try to open it. It only seems to be
the references for Outlook & Excel. I have to then go into the references and
reselect them using version 2000. When I open the references in 2000 it shows
those two "MISSING".

You really want to use late binding so that the user doesn't get any
such message if they don't have Outlook or Excel installed. Or if
they have a different version installed. Especially in the situation
where your corp is in the middle of upgrading work stations to a new
version of Access.

Late binding means you can safely remove the reference and only have
an error when the app executes lines of code in question. Rather than
erroring out while starting up the app and not allowing the users in
the app at all. Or when hitting a mid, left or trim function call.

You'll want to install the reference if you are programming or
debugging and want to use the object intellisense while in the VBA
editor. Then,. once your app is running smoothly, remove the
For more information including additional text and some detailed links
see the "Late Binding in Microsoft Access" page at
http://www.granite.ab.ca/access/latebinding.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Guest]

So basically what you're saying is use your code snipet instead of the
reference itself? And when I'm in VBA doing programming install the reference
back and dim out the code until I am done? So that code takes place of the
reference? Is that how I'm understanding this?

 

[Douglas J. Steele]

When using late binding, you need to ensure three things:

1) Declare the variables as Object, rather than Excel.Application, etc.
2) Use CreateObject to instantiate the objects, rather than the New keyword.
3) EIther replace any intrinsic constants with their values or else define
the same constants in your code.

For example, rather than

Dim objXL As Excel.Application

Set objXL = New Excel.Application
With objXL.Application
.Visible = True
.Workbooks.Open "D:\CARM\SYS\CARMaV5\CARMaV5a.XLS"
.ActiveWorkbook.RunAutoMacros xlAutoOpen
End With
Set objXL = Nothing

use either

Dim objXL As Object

Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
.Workbooks.Open "D:\CARM\SYS\CARMaV5\CARMaV5a.XLS"
.ActiveWorkbook.RunAutoMacros 1
End With
Set objXL = Nothing

or

Const xlAutoOpen = 1
Dim objXL As Object

Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
.Workbooks.Open "D:\CARM\SYS\CARMaV5\CARMaV5a.XLS"
.ActiveWorkbook.RunAutoMacros xlAutoOpen
End With
Set objXL = Nothing

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Pieter Wijnen]

right'ho

Pieter

So basically what you're saying is use your code snipet instead of the
reference itself? And when I'm in VBA doing programming install the
reference
back and dim out the code until I am done? So that code takes place of the
reference? Is that how I'm understanding this?

 

[Guest]

Do I put these late bindings into a module or build it within the vba code
for the object? For example I have code on some of my forms to send emails so
should I add this late binding to that code?

 

[Douglas J. Steele]

You replace whatever existing early bound code you have with the equivalent
late bound code everywhere in your application.

 

[Guest]

Here is an example of what I'm using for email code. Can you give me an
example of how I would add theis late bound code to it?

Sub SendMessageGenerateReport(Optional AttachmentPath)
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim db As Database
Dim rsEmail As Recordset
Dim stTo, stFrom, stCC, stBCC, stSubject, stSal, stText As String
Dim stQuery As String
Dim stAttachPath As String
Dim stAccessLink As String

stSubject = "CA#: " & Forms![frmCorrectiveActionByPass]![CA#]
stText = "A Corrective Action request has been issued to you: " &
Forms![frmCorrectiveActionByPass]![CA#]

'Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
'Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
.To = Forms![frmCorrectiveActionByPass]![Employee].Column(5)
.Subject = stSubject
.Body = stText
.Importance = olImportanceNormal ' Normal Importance
.Send

End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing

End Sub

 

[Douglas J. Steele]

What's so hard about

1) Declare the variables as Object, rather than Excel.Application, etc.
2) Use CreateObject to instantiate the objects, rather than the New keyword.
3) EIther replace any intrinsic constants with their values or else define
the same constants in your code.
?

Sub SendMessageGenerateReport(Optional AttachmentPath)
' Rule 1: Declare as Object
' Dim objOutlook As Outlook.Application
' Dim objOutlookMsg As Outlook.MailItem
' Dim objOutlookRecip As Outlook.Recipient
' Dim objOutlookAttach As Outlook.Attachment
Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objOutlookRecip As Object
Dim objOutlookAttach As Object

Dim db As Database
Dim rsEmail As Recordset
Dim stTo, stFrom, stCC, stBCC, stSubject, stSal, stText As String
Dim stQuery As String
Dim stAttachPath As String
Dim stAccessLink As String

stSubject = "CA#: " & Forms![frmCorrectiveActionByPass]![CA#]
stText = "A Corrective Action request has been issued to you: " &
Forms![frmCorrectiveActionByPass]![CA#]

' Okay, you're already following rule 2 (use CreateObject rather than New)

'Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
'Create the message.

' Rule 3: either replace the constants
' (you're using olMailItem & olImportanceNormal)
' by their values (0 & 1 respectively), or else declare variables
' by those names.

Const olMailItem As Long = 0
Const olImportanceNormal As Long = 1

Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
.To = Forms![frmCorrectiveActionByPass]![Employee].Column(5)
.Subject = stSubject
.Body = stText
.Importance = olImportanceNormal ' Normal Importance
.Send

End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing

End Sub

Incidentally, your line

Dim stTo, stFrom, stCC, stBCC, stSubject, stSal, stText As String

doesn't do what you likely think it does. It actually only declares one
variable as a string: stText. All the others are actually being declared as
variants. To declare them all as strings, you need

Dim stTo As String, stFrom As String, stCC As String, stBCC As String,
stSubject As String, stSal As String, stText As String

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Here is an example of what I'm using for email code. Can you give me an
example of how I would add theis late bound code to it?

Sub SendMessageGenerateReport(Optional AttachmentPath)
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim db As Database
Dim rsEmail As Recordset
Dim stTo, stFrom, stCC, stBCC, stSubject, stSal, stText As String
Dim stQuery As String
Dim stAttachPath As String
Dim stAccessLink As String

stSubject = "CA#: " & Forms![frmCorrectiveActionByPass]![CA#]
stText = "A Corrective Action request has been issued to you: " &
Forms![frmCorrectiveActionByPass]![CA#]

'Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
'Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
.To = Forms![frmCorrectiveActionByPass]![Employee].Column(5)
.Subject = stSubject
.Body = stText
.Importance = olImportanceNormal ' Normal Importance
.Send

End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing

End Sub

You replace whatever existing early bound code you have with the
equivalent
late bound code everywhere in your application.