Solved

Word 2010 VBA access reference issues

Posted on 2013-10-25
8
1,049 Views
Last Modified: 2013-10-28
The following Word VBA code works on my Word 2003 PC but fails on my Word 2010 PC.

The error message is:-

Run Time error 3706
Provider cannot be found. It may not be properly installed.

Both versions of Word have the following references ticked:-
VBA
Word (11.0 or 14.0) Object Library
OLE Automation
Microsoft Office(11.0 or 14.0) Object Library
Miccrosoft ActiveX Data Objects 2.7 Library
Microsoft Forms 2.0 Object Library

This is the code:

Sub WriteData2()

    Dim MyDatabase As String
    Dim MyID
    Set cn = New ADODB.Connection
    MyDatabase = "C:\psldata.mdb"
    
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & MyDatabase
    'cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & MyDatabase
    Set rs = New ADODB.Recordset
    'open the recordset
    rs.Open "People", cn, adOpenKeyset, adLockPessimistic, adCmdTable
        rs.AddNew     
        rs("peoSurname") = "Arney"
        rs("peoForenames") = "Richard Neil"
        rs.Update
        'MyID = rs.peopleID
       ' MsgBox MyID

    If rs.State <> 0 Then rs.Close   'Closes record
    cn.Close  'Closes the connection
End Sub

Open in new window


What have I not done on the 2010 PC to get it to run?  I have tried substituting the ACE connection for the JET connection but both trigger the same error message.

What do I need to install to get it to work please?

A supplemental question is why the following line does not work (remarked out in my code).  I get a not supported error message. How instead do I return the AutoNumber value of the ID for the record just created please?
MyID = rs.peopleID
0
Comment
Question by:LawyerLuddite
8 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39599919
Try using this Provider on the 2010 machine:

Provider=Microsoft.ACE.OLEDB.14.0

You refer to a Field in a Recordset like this:

MyID = rs("PeopleID")

or

MyID = rs!PeopleID

There are other ways to refer to it, but essentially everything is converted back to the first example, so just use that.

Also, you may need to refer to that ID BEFORE you issue the Update statement.
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39599929
Try installing the Access Database Engine and use the  "Microsoft.ACE.OLEDB.14.0" provider.
0
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39599933
I can help on the second part of the question.  You should change the MyID assignment to look like
myID = rs("PeopleID")

Open in new window

  I am not sure on the first part.  The code (Provider=Microsoft.ACE.OLEDB.12.0) works on my Word 2010.  Is Access install on the system?

Tom
0
 

Author Comment

by:LawyerLuddite
ID: 39600177
Installing the Access Database Engine (2007) and changing the Provider to ACE.OLEDB.14.0 has NOT made any difference.  I already had Acces 2010 installed anyway by the way.

The supplemental question has been fixed thank you. I had not noticed I had used an incorrect format there.

The database itself is a Word 2000/3 format if that makes a difference and was created on the Office 2003 PC.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 84
ID: 39600274
Your connection string should work, if you have Access or the Access Database Engine installed. The error is fairly clear in this case. Try repairing or reinstalling Access.

Can you try running this on another machine with Access or the ADE installed?
0
 

Author Comment

by:LawyerLuddite
ID: 39600719
I got it working on a different PC using both the JET and ACE 1.0 (but not 14.0)

However my cliernt is having exactly the same problem (which is my main worry) so I suspect it is going to be down to some local environmental settings.  I can seem to use Access fine on my  2010 PC.  I have Office Pro Plus 2010 and it appears that I can only uninstall the complete suite - which I really do not want to do. I have also put the access database engine on too.  Any other ideas.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39601295
Does the client machine have the full Office suite installed? Or is this running with the Access Runtime?

If the Runtime, are you certain it's correctly installed? Sounds almost like a "bit-wise" issue - that is, the wrong version is installed (i.e. a 64 bit version instead of a 32 bit, for example).
0
 

Author Closing Comment

by:LawyerLuddite
ID: 39605571
I think you have put me on the right track. It appears from a trawl round the internet that there are serious omissions in the functionality of the 64 bit versions of Office VBA. The answer seems to be to uninstall 64 bit and install 32 bit instead.  The PC that I have 32 bit on works fine, it was the 64 bit one that refused to play.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now