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.

 

[james_lankford]

I'm pretty suere you need a
set
right here
oCm.ActiveConnection = cn
so
set oCm.ActiveConnection = cn

and I don't think your sql isn't correct
http://www.w3schools.com/sql/sql_insert.asp

oCm.CommandText = "Insert Into _ 
Community (Store) _ 
values ( '" & SalesRep & "' ) "

I also use single quotes to surround values instead of double quotes

its not an error to use DQs, but I think SQs are easier

 

[akar]

Thanks James.

I tried using what you suggested, but I am still getting the same error. Can that be because I am missing something in the references?

Following are the things I have in the references:

Visual Basic for Applications
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft ActiveXData Objects 2.8 Library

 

[akar]

Also, If i remove the line oCm.Execute iRecAffected
then I do not get the error. But, then the functionality is not achieved.

 

[boblarson]

Two things - According to the help file the records affected needs to be a LONG INTEGER not an INTEGER.

Second, your SQL statement being assigned to the command is not proper as mentioned by James.

 

[akar]

Thanks Bob. I have changed the SQL statement now.

Can you please elaborate a bit more on the LONG INTEGER part?


Thanks,
-Akar.

Two things - According to the help file the records affected needs to be a LONG INTEGER not an INTEGER.

Second, your SQL statement being assigned to the command is not proper as mentioned by James.

 

[boblarson]

Thanks Bob. I have changed the SQL statement now.

Can you please elaborate a bit more on the LONG INTEGER part?

You have this:

Dim iRecAffected As Integer


But it needs to be

Dim iRecAffected As Long


or probably of course the name changed too:

Dim lRecAffected As Long

and then the applicable name change in the code below the declaration where it is actually being used.

 

[akar]

Hi Bob,

Thank you for your help. But it still gives the same error. I did a bit of research on the command.execute method and I found that the parameters should be (recaffected, query string, execution type). I was just wondering if I am missing on anything relating to execute(). As when i comment that particular line as I mentioned before, ithe code compiles.

Currently my code is as follows:

Private Sub CommandButton1_Click()
Dim cn As ADODB.Connection
Dim oCm As ADODB.Command
Dim SalesRep As String
Dim lRecAffected As Long
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
Set oCm.ActiveConnection = cn
oCm.CommandText = "Insert Into Community (Store) values ( '" & SalesRep & "' ) "
oCm.Execute lRecAffected
If lRecAffected = 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


Thanks,
-Akar

 

[boblarson]

Your SQL String still does not look to be formatted properly. It needs to have some spaces removed. It should look like this:

oCm.CommandText = "Insert Into Community (Store) values ('" & SalesRep & "')"



Why not try the OLEDB provider instead of ODBC?

Try this and see if it helps:

    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 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=S:\Act\Planning & Reporting\Budgets\Ad-Hoc\Ameya\Forecast_Sales\Forecast Sales.accdb;Persist Security Info=False;"

    cn.ConnectionTimeout = 40
    cn.Open

    SalesRep = "Akar"

    Set oCm = New ADODB.Command

    oCm.ActiveConnection = cn

    oCm.CommandType = adCmdTable

    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

 

[Norie]

I've been able to get this to work using ODBC and OLEDB.

For the ODBC I used a shorter connection string with less arguments, some of the arguments the original code had I just didn't recognise.

Private Sub CommandButton1_Click()
Dim cn As ADODB.Connection
Dim oCm As ADODB.Command
Dim SalesRep As String
Dim lRecAffected As Long
Dim strPath As String
Dim strFilename As String
 
    Set cn = New ADODB.Connection
 
    strPath = "C:\"    ' path to database
 
    strDataSource = "TestPad.accdb"    ' name of database

    ' ODBC
    cn.ConnectionString = "DSN=MS Access Database;DBQ=" & strPath & strDataSource & ";DefaultDir=" & strPath & ";"
 
    'OLEDB
    'cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPath & strDataSource & ";Persist Security Info=False;"
 
    cn.Open
 
    SalesRep = "Akar"
     
    Set oCm = New ADODB.Command
 
    oCm.ActiveConnection = cn
 
    oCm.CommandText = "Insert Into Community (Store) values ( '" & SalesRep & "' ) "
    
    oCm.Execute lRecAffected
    
    If lRecAffected = 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

PS The Long/Integer doesn't really matter here but you should use Long anyway, for 2 reasons:

- Integer data type variables are converted to Long anyway
- Long can deal with larger nos >32767, which might not be crucial here but you never know