Solved

Word 2010 VBA access reference issues

Posted on 2013-10-25
8
1,054 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Microsoft Word is a program we have all encountered at some point, but very few of us have dug deep into its full scope of features, let alone customized it to suit our needs. Luckily making the ribbon (aka toolbar, first introduced in Word 2007) wo…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

776 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