Solved

Word 2010 VBA access reference issues

Posted on 2013-10-25
8
1,032 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 26

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

706 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

18 Experts available now in Live!

Get 1:1 Help Now