Solved

Word 2010 VBA access reference issues

Posted on 2013-10-25
8
1,074 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
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!

 

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
 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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 …
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

752 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