Access 2010 ADODB Connection problem

Hi. I am using the following code to connect to a database but on the line marked "Error here" I am getting an error saying unregognized database format. I have attached a screenshot showing the error as well as the properties of my database

Sub getProjectDescFromAccess()
    Const projectIDColumn = "A"
    Const projectDescColumn = "J"
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sSQL As String, strConn
    Dim looper As Long
    Dim cellPointer As Variant
    Dim MYDOC_DIR As String
    Dim DATABASE_PATH As String
   
    MYDOC_DIR = Environ("userprofile") & "\Documents"
    DATABASE_PATH = MYDOC_DIR & "\Database1.accdb"
   
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DATABASE_PATH & ";Persist Security Info=False"
    Set cnn = New ADODB.Connection
    cnn.Open strConn  'Error here
   
    For X = 2 To Range(projectIDColumn & Rows.Count).End(xlUp).Row
    Set cellPointer = Worksheets("Charges Form").Range(projectIDColumn & X)
    'If you project number field is text use this sSQL
    sSQL = "SELECT tblBank.* FROM tblBank WHERE(((tblBank.NMBR)='" & cellPointer & "'));"
    Set rs = New ADODB.Recordset
   
    rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
    If Not IsNull(rs.Fields("NMBR").Value) Then
       Range(projectDescColumn & looper) = rs.Fields("NMBR").Value
    End If
    rs.Close
    Set rs = Nothing
    Next X
   
    cnn.Close
    Set cnn = Nothing
End Sub Screenshot
Murray BrownHead DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nike_golfCommented:
You might try this connect string... (Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;)

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;

NG,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rockiroadsCommented:
nike on the right lines. the connection string has changed though for access 2010 I believe it is 14.0

nike_golfCommented:
Yeah, not running it at the office... oops didn't realize the link was for 2007...

NG,
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Murray BrownHead DeveloperAuthor Commented:
I updated the code to the following but it said "provider cannot be found. It may not be properlty installed" - do I need to put a reference in>

Sub getProjectDescFromAccess()
    Const projectIDColumn = "A"
    Const projectDescColumn = "J"
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sSQL As String, strConn
    Dim looper As Long
    Dim cellPointer As Variant
    Dim MYDOC_DIR As String
    Dim DATABASE_PATH As String
   
    MYDOC_DIR = Environ("userprofile") & "\Documents"
    DATABASE_PATH = MYDOC_DIR & "\Database1.accdb"
   
   
   
    strConn = "Provider = Microsoft.ACE.OLEDB.14.0;Data Source=" & DATABASE_PATH & ";Persist Security Info=False;"

    Set cnn = New ADODB.Connection
    cnn.Open strConn
   
    For X = 2 To Range(projectIDColumn & Rows.Count).End(xlUp).Row
    Set cellPointer = Worksheets("Charges Form").Range(projectIDColumn & X)
    'If you project number field is text use this sSQL
    sSQL = "SELECT tblBank.* FROM tblBank WHERE(((tblBank.NMBR)='" & cellPointer & "'));"
    Set rs = New ADODB.Recordset
   
    rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
    If Not IsNull(rs.Fields("NMBR").Value) Then
       Range(projectDescColumn & looper) = rs.Fields("NMBR").Value
    End If
    rs.Close
    Set rs = Nothing
    Next X
   
    cnn.Close
    Set cnn = Nothing
End Sub
rockiroadsCommented:
are you using 32bit or 64bit windows?

reading up on this, although the version is now 14.0 some drivers may not be fully available. 64bit seems to suffer from that more. But did also read that 12.0 works. Try that, if it does then full credit to nike
nike_golfCommented:
You do need a reference to ADO.

NG,
Murray BrownHead DeveloperAuthor Commented:
I am using 64 bit Windows
rockiroadsCommented:
Just as I thought which is why I asked. In that case you need the access 2010 database engine. I suggested this to someone else a few days back also. nike already posted a link so no need to repeat it.

My bad about the provider string. For access leave as 12.0 but for Excel you need to put 14.0

Up until this database engine for Access 2010 there was no any 64 bit drivers.
Murray BrownHead DeveloperAuthor Commented:
Hi
I went to that link and tried to run the 64 bit one but the installation failed. Not sure what to do now. This is absurd. Microsoft need to pull their socks up
nike_golfCommented:
I think you awarded the answer to the wrong person...???

I actually posted the conection string..

From rockiroads himself..

"reading up on this, although the version is now 14.0 some drivers may not be fully available. 64bit seems to suffer from that more. But did also read that 12.0 works. Try that, if it does then full credit to nike"

NG,
Murray BrownHead DeveloperAuthor Commented:
Sorry about that. How do I undo this mistake
rockiroadsCommented:
yes points to nike. use 14.0 for excel, 12.0 for access
Murray BrownHead DeveloperAuthor Commented:
thanks very much Nike
nike_golfCommented:
No problem appreciate the modification...

NG,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.