Vba – Error creating ” Access.Application” Object on Visual Basic with Office 2007

ms-accessms-access-2007vba

I have created a application that takes an Excel file and inserts it into my access database table.
I used Microsoft Access 15.0 Object Library in my computer . But when I executed the application it in an another computer with Access 2007 it stops immediately. This is the code:

Private Sub xlsTomdb()
    On Error GoTo err_handler
    Dim oAccess As Access.Application
    Set oAccess = CreateObject("Access.Application")
    oAccess.Visible = False
    oAccess.OpenCurrentDatabase App.Path + "\archivi.mdb", True
    oAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "EXCEL", App.Path + "\Export\Final.xls", True
    oAccess.Quit
err_handler:
      MsgBox "The code failed at line " & Erl, vbCritical
End Sub

The msgBox shows "The code failed as line 0". When I removed the Error Handler the program continues the execution normally.
While running the program on another PC with Office 2007 it terminates at this function with runtime error [-2147467259 (80004005) microsoft odbc microsoft access driver type mismatch in expression]

This function is executed on button click event.
Is there any reason not to open access 2007, because I used Access 2013 while building it?

Can anyone help?

Best Answer

EBalla:

"Microsoft Access 15.0 Object" or DAO 15 library belongs to office 2013. Earlier versions will not know about this library. if your target machines are using office 2007 you need to add the reference "Microsoft Access 12.0 Object library" into your project. You are experiencing compatibility issue rather than code issue.

also check this out: http://allenbrowne.com/ser-38.html

Related Topic