[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access problem with references

Posted on 2012-09-10
17
Medium Priority
?
425 Views
Last Modified: 2012-09-11
Hi!

I have an mdb file that runs on many computers. All of the sudden, my users who use 2003 are getting funny errors at different functions. On those computers, I found that there is a missing reference for Microsoft Outlook 14.0. If I deselect it, and add Microsoft Outlook 11.0, it works fine. However, when a 2010 user opens the database, it seems to switch back.

Is there a way to remove a missing reference via VBA?

Note: it probably won't be necessary to add the other one, as those users do not use the portion of the database that involves Outlook.

Thanks!
0
Comment
Question by:etech0
  • 8
  • 7
  • 2
17 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 300 total points
ID: 38383135
I believe it can be done through VBA  (others can help with that)....

But to really make your code version independent, use Late Binding instead of Early Binding:
http://word.mvps.org/faqs/interdev/earlyvslatebinding.htm
0
 
LVL 58
ID: 38383140
Are you calling Outlook in your code?  You can verify by removing the reference and then doing a compile.

If you don't need it, then your set.

If you do need it, then the only way around this problem is to use late binding.  With late binding, you don't setup a reference and use a generic object reference when creating an object variable.

Fill in some more detail and we'll get you going.

Jim.
0
 
LVL 10

Author Comment

by:etech0
ID: 38383153
@mbizup: Is that link for excel?

@JDettman: I do use Outlook in my code. I don't know if it makes a difference, but my 2003 users do not use that code at all.

What's late binding? How does it work?

Also, does it make sense that this problem just popped up out of the blue? Because we've been in this setup for a couple of years, and this is the first time I've seen this problem.

Thanks!
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 58
ID: 38383154
Here's the difference BTW:

' Early
Dim oOutlook As Outlook.Application
Set oOutlook = New Outlook.Application

' Late
Dim oOutlook As Object
Set oOutlook = CreateObject("Outlook.Application")

 Early binding requires a reference, so it's version dependent.  However it gives you intellisense and is considerably faster then late.

  With the new "auto update of references" feature in latter versions of Access, it's getting to be a real pain to early bind.

 So some have gone to early binding in development/testing and then switch to late binding for release.

Jim.
0
 
LVL 58
ID: 38383167
<<@JDettman: I do use Outlook in my code. I don't know if it makes a difference, but my 2003 users do not use that code at all.>>

 Doesn't matter if some users use it and others not; if it's in the code and used by anyone, then you can remove it.

<<What's late binding? How does it work?>>

   See last comment.

<<Also, does it make sense that this problem just popped up out of the blue? Because we've been in this setup for a couple of years, and this is the first time I've seen this problem.>>

 Somehow, the 2003 users got a copy of a DB that was used by a 2010 user.

 Your other option BTW is to early bind, but keep two different versions of the Front end.

Jim.
0
 
LVL 10

Author Comment

by:etech0
ID: 38383173
I changed
Dim appOutLook As Outlook.Application to Object. It wasn't a big part of my workflow anyway, so I don't think Intellisense is necessary.

I still get an error, however, on DoCmd.SearchForRecord. It tells me: "Compile Error: Method or data member not found"

Any ideas?
0
 
LVL 10

Author Comment

by:etech0
ID: 38383182
BTW: re "Somehow, the 2003 users got a copy of a DB that was used by a 2010 user."

we've been doing that all along!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38383195
<<  Is that link for excel? >>

It actually is Word, but the syntax generally applies to any VBA (word, Excel, Access.. etc).

As Jim pointed out, the difference is in how you define your variables.

Your code may also use numerical constants (for example olInbox, olFolder, etc) that will cause compile errors if you switch to declaring your variables as simple Objects.  To resolve those issues you would replace the constant name with the actual value of the constant.
0
 
LVL 58
ID: 38383198
<<Dim appOutLook As Outlook.Application to Object. It wasn't a big part of my workflow anyway, so I don't think Intellisense is necessary.>>

 Need to remove the reference and also change the Set appOutlook =

Jim.
0
 
LVL 10

Author Comment

by:etech0
ID: 38383212
I have:


Set appOutLook = CreateObject("Outlook.Application")

Good?
0
 
LVL 58
ID: 38383425
<<Set appOutLook = CreateObject("Outlook.Application")>>

 Yup.  Just be sure if you've refered to other objects in Outlook objects model changed as well as Miriam pointed out.

 Everything that relates to the object model needs to be dim'd "AS OBJECT"

 and not as something specific.

Jim.
0
 
LVL 10

Author Comment

by:etech0
ID: 38383444
Found another one!
    Dim MailOutLook As Outlook.MailItem
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
    Dim mess_body as string
    mess_body = "yada yada yada"
    With MailOutLook
      '(I have this in two places, with a bunch of stuff)
    End With

Open in new window


What should I change?
0
 
LVL 58
ID: 38383461
Dim MailOutLook As Outlook.MailItem

to

Dim MailOutLook As Object

Set remains the same.

Jim.
0
 
LVL 10

Author Comment

by:etech0
ID: 38383509
Done.

I still get the  error, however, on DoCmd.SearchForRecord. It tells me: "Compile Error: Method or data member not found"

Any ideas?
0
 
LVL 10

Author Comment

by:etech0
ID: 38386882
Any idea why DoCmd.SearchForRecord stopped working in Access 2003?
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1700 total points
ID: 38387209
SearchForRecord is not valid in 2003.  Should be find record.

Jim.
0
 
LVL 10

Author Comment

by:etech0
ID: 38387356
Aha!

Find record did not work here, so I'll use recordsets.

It turns out that there was only one instance of searchforrecord in all my code - it's from before I knew about recordsets.

Thanks for clearing that up!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question