Access References

[ryguy7272]

I recently installed Office 2007 on my ThinkPad. I was using Office 2002 for
quite some timed and loved it. Not a huge fan of 2007 yet, but I’m getting
there. Now, I’ve got both office suites installed on my system (I use one at
a time).

I’ve encountered no problems with any apps, except for running Access 2002
VBA. Problems don’t occur with all of my files, but I’d say 90+% are
problematic. I’m guessing it has to do with the references under Tools >
References, but I can’t figure out what reference needs to be checked. I
have checked off the following:
Visual Basic for Applications
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft DAO 3.6 Object Library

That’s it for now.

I open a small database that searches for a person by last name. When I
open it in 2002, I get this error: ‘The expression On Click you entered as
the event property setting produced the following error: Object or class does
not support the set of events.’

If I open the file in 2007, then save as 2007, it works fine.


Here is the VBA (but I don’t think it matters; I’m pretty sure it really has
something to do with those references):
Option Compare Database
Option Explicit

Private Sub cboCompanyID_AfterUpdate()
Me!txtEmployer = Me!cboCompanyID.Column(1)
End Sub

Private Sub cmdPersonSearch_Click()
On Error Resume Next
Dim lngPersonID As Long
lngPersonID = GetPersonID

If lngPersonID <> 0 Then

Dim rs As Recordset
Dim db As Database
Dim criteria As String
Set db = CurrentDb()
Set rs = Me.RecordsetClone
criteria = "[PersonID] =" & lngPersonID
rs.FindFirst criteria
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "Record Not Found"
End If
Me.Refresh
End If

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

Exit Sub
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error Resume Next
Me!Updated = Now()
Me!User = CurrentUser()
Exit Sub
End Sub

Private Sub Form_Current()
Me.Caption = " " & Me![txtFirstName] & (" " + Me![txtMiddleName]) & " " &
Me![txtLastName]
End Sub


When Access 2002 loads, I see a reference to Microsoft Access 12.0 Object
Library. I think this should be Microsoft Access 10.0 Object Library, but
there is no option for that. Also, I see a reference for Microsoft Office
12.0 Object Library, but no reference for Microsoft Office 10.0 Object
Library. In both Word and excel 2002, I have a reference to Microsoft Office
10.0 Object Library. Any ideas as to what to do to make my older Access 2002
code work?

Thanks!
Ryan---

 

[Dirk Goldgar]

I recently installed Office 2007 on my ThinkPad. I was using Office 2002
for
quite some timed and loved it. Not a huge fan of 2007 yet, but I’m
getting
there. Now, I’ve got both office suites installed on my system (I use one
at
a time).

I’ve encountered no problems with any apps, except for running Access 2002
VBA. Problems don’t occur with all of my files, but I’d say 90+% are
problematic. I’m guessing it has to do with the references under Tools >
References, but I can’t figure out what reference needs to be checked. I
have checked off the following:
Visual Basic for Applications
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft DAO 3.6 Object Library

That’s it for now.

I open a small database that searches for a person by last name. When I
open it in 2002, I get this error: ‘The expression On Click you entered as
the event property setting produced the following error: Object or class
does
not support the set of events.’

If I open the file in 2007, then save as 2007, it works fine.

Are those references from your application when opened in Access 2002, or
2007?

Access 2002 is version 10, so it should be using "Microsoft Access 10.0
Object Library" instead of "Microsoft Access 12.0 Object Library". In a
standard install of Office 2002, the Access Object Library should be stored
in the Office10 folder, as "C:\Program Files\Microsoft
Office\Office10\msacc.olb".

I'm not sure that Access will let you un-check the Access 12 library to put
the Access 10 library in its place, but you can try that. However, Access
*should* adjust this reference automatically depending on which version of
Access you use to open the database. Did you install both versions of
Access in the default folders?

Note: you probably don't need the reference to the VBA Extensibility
library.

 

[ryguy7272]

Sorry for the confusion there: When I open 2007, I see a reference to
Microsoft Access 12.0 Object Library. When I open 2003, I see a reference to
Microsoft Access 12.0 Object Library; I think it should be Microsoft Access
10.0 Object Library.

When I go here:
C:\Program Files\Microsoft Office\Office12
I see a file named 'MSACC.OLB'

When I go here:
C:\Program Files\Microsoft Office\Office10
I also see the same file, named 'MSACC.OLB'

Is that the problem, Dirk?

 

[Tom Wickerath]

Hi Ryan,

See this article by Access MVP Allen Browne:

Errors using multiple versions of Access under Vista/Windows 7
http://allenbrowne.com/bug-17.html


FYI - on your note at the bottom of your posting, that reads:
If this information was helpful, please indicate this by clicking ''Yes''.

Please be aware that only users who are using the Web Interface will see
this button. Perhaps users that are using Windows Mail with the proper
configuration may see it, but I'm not sure about that. Other people, who use
a variety of news reader clients, including Outlook Express, Thunderbird,
etc. will not see this option.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Dirk Goldgar]

Sorry for the confusion there: When I open 2007, I see a reference to
Microsoft Access 12.0 Object Library. When I open 2003, I see a reference
to
Microsoft Access 12.0 Object Library; I think it should be Microsoft
Access
10.0 Object Library.

Is it 2003 or 2002? In your first post, you said 2002. Access 2003 is
Access 11.0, Access 2002 is Access 10.0.

Whichever, when you open the database in the earlier version, it *should*
reset the reference to the object library that is appropriate to that
version. However, to do this, it has to have the necessary permissions to
alter the registry, and under Vista, it usually doesn't. Allen Browne has a
good page on this: http://allenbrowne.com/bug-17.html .

 

[Tony Toews [MVP]]

Sorry for the confusion there: When I open 2007, I see a reference to
Microsoft Access 12.0 Object Library. When I open 2003, I see a reference to
Microsoft Access 12.0 Object Library; I think it should be Microsoft Access
10.0 Object Library.

You are correct. This is some kind of permissions problem but I
forget the solution. Ahh, found the page.
http://allenbrowne.com/bug-17.html

When I go here:
C:\Program Files\Microsoft Office\Office12
I see a file named 'MSACC.OLB'

When I go here:
C:\Program Files\Microsoft Office\Office10
I also see the same file, named 'MSACC.OLB'

That file being in both places is just fine.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[ryguy7272]

Thanks for the link Tom. I followed the steps in tip #1; everything is fine
there now. For tip #2, I went here:
C:\Documents and Settings\ThinkPad\Application
Data\Microsoft\Office\Shortcut Bar\Office
My Shortcuts are linked to the Shortcut bar. I found both offending Access
shortcuts, right-clicked, but didn't see any 'Run as Administrator box'. I
tried tip #3, double-clicked User Accounts, but didn't see anything called
'Turn User Account Control on or off'. I'm using XP, not Vista.

The problem persists...

On a side note, I've heard that not everyone can see those 'Yes' and 'No'
buttons, but I've always been able to see them. I put that message in there,
I guess, because I answer a lot of questions in the Excel Programming area
and Excel functions -- I got a silver medal from both.

 

[ryguy7272]

Thanks everyone. I'm having trouble with Access 2002 SP3. I think it should
have a reference to Access 10.0, but after installing Access 2007, the
default reference for Access 2002 and 2007, BOTH seem to be 12.0. I guess
this is causing the problems. I just opened Word 2002, excel 2002, and
PowerPoint 2002, and saw a reference to 10.0 for each!!

 

[Tom Wickerath]

Hi Ryan,

I'm using XP, not Vista.

In that case, the article does not apply.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Tom Wickerath]

Hi Ryan,

You might try initiating a repair of Office 2002 via the Help menu. I would
start by opening a program that is not giving you a problem (Excel, Word).
Then initiate the repair. You may need to have your installation disk handy.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[ryguy7272]

Ha! The most obvious answer is the correct one. I did what you suggested
and all is well now!

Thanks Tom!!

 

[Tom Wickerath]

Cool! Glad that you're back in business!


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________