CODEWITHSUNDEEP
vbaexcelexcel-2010ms-access-2010
Nemo
Nemo

Reputation: 1111

Run time error Unrecognized database while exporting data from excel 2010 to Access table 2010 using excel vba

I have a table in Access 2010 db with same column names as the column names in excel sheet. I have to delete Access table data content before pumping data from my macro enalbed excel 2010 sheet into it.For now I m trying to see/test if I could pump my excel data to empty table in Access. Once I get this working, I could get 'delete content before dumping excel data' working.

Here's my excel vba macro code:

Sub ADOFromExcelToAccess()
'exports data from the active worksheet to a table in an Access database
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=C:\Users\shress2\Documents\TSS_Certification\TSS_Certification.accdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "t_certification_051512", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
    With rs
        .AddNew ' create a new record
        ' add values to each field in the record
        .Fields("Role") = Range("A" & r).Value
        .Fields("Geo Rank") = Range("B" & r).Value
        .Fields("Geo") = Range("C" & r).Value
        ' add more fields if necessary...
        .Update ' stores the new record
    End With
    r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

I added Tools--> References and selecting Microsoft ActiveX Data Objects 6.0 Object Library.

I m getting Run-Time error' -2147467259(80004005)': Unrecognized database format 'C:\Users\shress2\Documents\TSS_Certification\TSS_Certification.accdb

Any reasons why? And how do I fix it? Thanks.

Upvotes: 1

Views: 6975

Answers (1)

Steve
Steve

Reputation: 216363

You are connecting to an .accdb database file. It's a Access 2007/2010 format.
The Microsoft.Jet.OLEDB.4.0 provider has been built for mdb files from Access 2003 era.
I don't think you can connect with that provider (It fails to recognize the file format).

Try to change you connection string to use

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\shress2\Documents\TSS_Certification\TSS_Certification.accdb;"

Upvotes: 3

Related Questions