Error when creating new table in existing Access DB from excel VBA

[mpphelps]

Hi Everyone, I'm struggling to understand an error I get when trying to create a new table into an existing Access database using excel vba code. Any help in understand how to overcome this error would be appreciated. this is the error I get:

"Run-time error '-2147217900 (80040e14)': Automation error"

clicking help sends me here: Automation error (Error 440)

below is my code:

Private Sub CommandButton1_Click()
    Dim dbConnectStr As String
    Dim cnt As ADODB.Connection
    Dim dbPath As String
    Dim tblName As String
    'Set database name in the Excel Sheet
    dbPath = "C:\Program Files\SETROUTE 9.2.0\DATA\REPORT.MDB" 'Database Name
    tblName = "Cables721" 'Table Name
    dbConnectStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";"
    'Connect to database and insert a new table
    Set cnt = New ADODB.Connection
    With cnt
        .Open dbConnectStr
        .Execute "CREATE TABLE " & tblName & " ([BankName] text(50) WITH Compression, " & _
                 "[C_ID] text(9) WITH Compression, " & _
                 "[LENGTH] text(10) WITH Compression, " & _
                 "[ELEVATION] text(150) WITH Compression"
    End With
    Set cnt = Nothing
End Sub

 

[RoryA]

It looks like you're missing a closing parenthesis to me.

 

[mpphelps]

It looks like you're missing a closing parenthesis to me.

Good catch on the parenthesis however I still get the same error

 

[RoryA]

It works OK for me after correcting that. Is it the .Open line or the .Execute line giving you the error? Are you sure the table doesn't already exist? (that would cause that error)

 

[mpphelps]

It works OK for me after correcting that. Is it the .Open line or the .Execute line giving you the error? Are you sure the table doesn't already exist? (that would cause that error)

I created a test database to see if it had something to do with the database I was accessing and that appears to be the problem. The code works just fine when I apply to a new database. There must be some system permissions on the database I'm trying to work on that prevents me from running my code. I checked to see my table doesn't already exist and that's not the case, so my only guess is that the database has some setting preventing tables from being created which is weird since I can create tables just fine in the front end of that database.


thank you for your help, it was much appreciated!

 

[RoryA]

Do you have any passwords set on the db?

 

[mpphelps]

Do you have any passwords set on the db?

None that I know of. If I can import a table to the database from the frontend interface, I can't see why I couldn't do from code. I'll play with it some more tomorrow when I return to work and if I can figure out what the problem is, I'll report back.