Solved

Word 2010 VBA access reference issues

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

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…
Suggested Courses
Course of the Month8 days, 14 hours left to enroll

615 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