Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 1st, 2006, 02:17 PM
Registered User
 
Join Date: Jun 2003
Location: , , USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default transfer data to xls with ADO not working

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
Reply With Quote
  #2 (permalink)  
Old March 1st, 2006, 04:09 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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



Reply With Quote
  #3 (permalink)  
Old March 1st, 2006, 04:30 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

Reply With Quote
  #4 (permalink)  
Old March 1st, 2006, 07:28 PM
Registered User
 
Join Date: Jun 2003
Location: , , USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old March 1st, 2006, 09:55 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Export data into the xls Sheet Directly without cl Kali Charan Tripathi Reporting Services 0 October 9th, 2008 06:40 AM
Transfer data mepancha SQL Server 2000 4 March 24th, 2005 04:29 PM
download db data to .xls with asp abbylee26 Classic ASP Databases 2 October 17th, 2003 02:18 PM
Server.Transfer Not Working sg48 ASP.NET 1.0 and 1.1 Basics 1 June 21st, 2003 10:24 AM
Data Transfer psingh SQL Server 2000 8 June 9th, 2003 06:25 PM



All times are GMT -4. The time now is 08:03 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.