Automation error: Connecting excel with Access

[akar]

I am trying to export table data from excel2007 to a table with same attributes in access2007. After using the following code in excel-VBA I am getting error - "Run Time Error - '-2147217900(80040e14)': Automation error"

Following is my code:

Private Sub CommandButton1_Click()
Dim cn As ADODB.Connection
Dim oCm As ADODB.Command
Dim SalesRep As String
Dim iRecAffected As Integer
Set cn = New ADODB.Connection
cn.ConnectionString = "DSN=MS Access Database;DBQ=S:\Act\Planning & Reporting\Budgets\Ad-Hoc\Ameya\Forecast_Sales\Forecast Sales.accdb;DefaultDir=S:\Act\Planning & Reporting\Budgets\Ad-Hoc\Ameya\Forecast_Sales;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"
cn.ConnectionTimeout = 40
cn.Open
SalesRep = "Akar"
Set oCm = New ADODB.Command
oCm.ActiveConnection = cn
oCm.CommandText = "Insert Into Community (Store),values(""&SalesRep&"")"
oCm.Execute iRecAffected
If iRecAffected = 0 Then
MsgBox "No Records Inserted"
End If
If cn.State <> adStateClosed Then
cn.Close
End If
Application.StatusBar = False
If Not oCm Is Nothing Then Set oCm = Nothing
If Not cn Is Nothing Then Set cn = Nothing


End Sub


System details: Microsoft XP Version 2002 SP3

Any help will be greatly appreciated.

Thanks,
-Akar.

 

[akar]

Thank you Bob, James and Noorie. I was able to run the program when i converted my database to .mdb format. Ideally, it should have worked .accdb format as well but there seems to be someproblem that our technical team is looking into. Thanks a lot for your help.

 

[xenou]

Check your connection string if you are using ODBC:
http://www.connectionstrings.com/access-2007

You also have to have updated drivers for 2007 - you might be out of date.

I generally use OLEDB.