transfer data to xls with ADO not working

nebbish · March 1st, 2006, 02:17 PM

i was looking for a way to update an excel file with ADO and found the previous topic #38914 that started on 1/18/2006 which has TWO very clear and thorough examples of how to achieve this.

topic url: http://p2p.wrox.com/topic.asp?TOPIC_ID=38914

i've been beating my head against a wall for 2 days now trying to get the first method described to work. i even found a whole example project on the microsoft website entitled "ExcelADO demonstrates how to use ADO to read and write data in Excel workbooks" which should be available under this url: http://support.microsoft.com/default...b;en-us;278973

that code is very similar to the example Bob described in topic #38914.

only problem is....the code never works in my access VBA project. i get an 0x80040e09 error ("Cannot update. Database or object is read-only") on the line where '.AddNew' gets called on the excel recordset.

after experimenting: the coding technique seems to work only when in a VB6 project, and always errors when in a VBA project (the code is practically identical).

please, any help/ideas are appreciated.

*** * ** *** * * newsflash: while i was writing this post i checked out the microsoft article entitled "Much ADO About Text Files" and discovered the the error message can be related to the Jet (excel) engine registry key called 'DisabledExtensions' - but lo, the value in my registry seems to be what it should be: "!xls".

so that's not the problem.

once again, thanks in advance for any help/ideas,
nebbish

Bob Bedell · March 1st, 2006, 04:09 PM

Hi nebbish,

I got all 5 of the VB samples (and my version) running fine in VBA.

Your problem is obviously with the Excel connections.

The only ways I'm able to reproduce your error are by:

1. Removing Write permissions from a folder the Excel source workbooks are stored in.

2. Removing Write permissions from the Excel source workbook files themselves.

Have you checked permissions settings on both the source workbooks (the workbooks the Excel connections get opened on) and the folder they are stored in?

Bob

Bob Bedell · March 1st, 2006, 04:30 PM

The other culprit might be the permissions on the connection object itself. This will also reproduce your error:

Code:

Set cnn = New ADODB.Connection
    cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cnn.ConnectionString = "Data Source=C:\Permissions\Book1.xls;" & _
                           "Extended Properties=""Excel 8.0"""
    cnn.Mode = adModeRead
    cnn.Open

Setting the connection mode to cnn.Mode = adModeReadWrite (as I explicitly do in my code) would be the solution here. The Microsoft VB samples don't explicitly set the connection mode to adModeReadWrite, which means they default to adModeUnknown (indicating that the permissions have not yet been set or cannot be determined). Still, they run fine in my VBA project. Just wanted to mention another permissioins-related way to throw your error.

Bob

nebbish · March 1st, 2006, 07:28 PM

Hi Bob

first thanks for replying - i appreciate your input

ok, sooo

i just got finished checking permissions on both the folder and the workbook inside. i granted full permissions to 'Everyone' for both the folder and the file. still no change.

also i am setting the mode to adModeReadWrite (since the ExcelADO sample didn't do that it was one of the first things i tried after reading the previous topic.

i'm going to put the code of my function down below so you can see exactly what i'm doing. you'll notice that i've got a passed in parameter that i'm not using yet, 'rs', which will be a recordset against the access database once i get the excel recordset to work.

let me show you my Jet settings for the excel engine:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Excel]
"win32"="C:\\Program Files\\Microsoft Office\\Office10\\msaexp30.dll"
"DisabledExtensions"="!xls"
"FirstRowHasNames"=hex:01
"AppendBlankRows"=dword:00000001
"win32old"="C:\\WINDOWS\\System32\\msexcl40.dl l"
"ImportMixedTypes"="Text"
"TypeGuessRows"=dword:00000000

are yours different?

you'll also notice that i create the file fresh every time and execute an sql statement to create the table before opening the recordset against the xls file - but before posting this reply i tried with a pre-existing file and still no joy. i did get different behavior based on the string i passed into the 'table' parameter: the pre-existing file required me to use '[....$]' around the sheet name, but when i create the file and sheet fresh it accepts an undecorated value (i'm using "newsheet").

Code:

Public Sub WriteXlsFileADO(rs As ADODB.Recordset, fullpath As String, table As String)
On Error GoTo WriteXlsFileADOError

    If Dir(fullpath) <> "" Then Kill fullpath

    Dim sql As String
    Dim conn As ADODB.Connection
    Dim rsExcel As ADODB.Recordset

    Set conn = New ADODB.Connection
    With conn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
        .Properties("Data Source") = fullpath
        .Properties("Extended Properties") = "Excel 8.0;HDR=NO;"
        .Mode = adModeReadWrite
        .Open
    End With

    sql = "CREATE TABLE " & table & " (F1 char(255));"
    Call conn.Execute(sql)
    Set rsExcel = New ADODB.Recordset
    rsExcel.Open "Select * from " & table, conn, adOpenKeyset, adLockOptimistic

    rsExcel.AddNew
    rsExcel.Fields(0) = "Test0"
    rsExcel.Update

WriteXlsFileADODone:
    Exit Sub
WriteXlsFileADOError:
    Err.Description = "ERROR in WriteXlsFileADO" & vbCrLf & _
                      " fullpath = " & fullpath & vbCrLf & _
                      "    table = " & table & vbCrLf & _
                      "      err = " & Err.Description & vbCrLf
    Err.Raise Err.Number
    Resume WriteXlsFileADODone
End Sub

Bob Bedell · March 1st, 2006, 09:55 PM

Well, the bad news is your code works fine on my end (very cool, by the way: didn't know you could use the Excel 8.0 source database type string to create a new workbook that way, or use CREATE TABLE as you are).

Have a peak at this link:

http://www.xtremevbtalk.com/archive/.../t-250365.html

My registry settings are:

"win32"="C:\\Program Files\\Microsoft Office\\Office10\\msexcl40.dll"
"DisabledExtensions"="!xls"
"FirstRowHasNames"=hex:01
"AppendBlankRows"=dword:00000001
"ImportMixedTypes"="Text"
"TypeGuessRows"=dword:00000008

I don't show a "win32old" key. Running Office XP Pro SP3.

The link discusses msexcl40.dll vs. your msaexp30.dll. Some reports of success setting the key value back to msexcl40.dll. I guess your running 2K3? msaexp30.dll, Microsoft Access Expression Builder, is apparently installed with XP SP3 too, but apparently my "win32" key wasn't affected. Could be it. I have joy. :)

Bob