Updating a closed workbook with an ADO connection problem.

[IndigoDragon]

Hi there,

I would like to update a record in an external (closed) workbook through an ADO connection. So far I haven't been able to update a record. Could anyone tell me if I'm using the right properties for the connectionstring and sql string?

ThanX!

    cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;ReadOnly=False;" & "DBQ=" & strSourceFile & ";"

    On Error GoTo 0
    
    If cn Is Nothing Then
        MsgBox "File can not be found!", vbExclamation, ThisWorkbook.Name
        Exit Sub
    End If
    
    ' Save data to archive.
    strSQL = "UPDATE [Mails$] SET [Read] = 'FALSE' WHERE [MailID] = '1'"
    
    ' Open the recordset.
    Set rs = New ADODB.Recordset
    On Error Resume Next

    rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic

 

[JerryKlmns]

From

http://www.connectionstrings.com

looks like you use ODBC driver. For OLE DB

cn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

From your strSQL looks like you just want to update a record so your rs should be better off been an adOpenForwardOnly, with server side cursor location
Set rs = New ADODB.Recordset
With rs
.ActiveConnection=Cn
.CursorLockation=adUseServer
.CursorType=adOpenForwardOnly
.LockType=adLockOptimistic
.Source=strSQL
.Open
.Close
End With
Set rs = Nothing

There is lot to explore about recordsets....

 

[IndigoDragon]

Hi Jerry,

Thanx for your reply. Indeed, there's a lot to explore about recordsets!

I guess your code should work but I get a "Datatype mismatch in criteria expresssion" error using the string:

    strSQL = "UPDATE [Mails$] SET [Read] = 'False' WHERE [MailID] = '1'"

For testing, I set the column (Read+MailID) formats to "General" Excel-file. This still gives the same error. And: how would I construct the string if the MailID param has to be an integer?

ThanX Again!
cYa.

 

[PHV]

strSQL = "UPDATE [Mails$] SET [Read] = 'False' WHERE [MailID] = 1"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[IndigoDragon]

Well,

with your code, PHV, I get a "Must be an updateable query" error. When including the apostrophes I get the 'old' error.

And: The workbook and sheet it's reading from are not protected.

Any suggestions?
ThanX

 

[JerryKlmns]

This ?

strSQL = "UPDATE [Mails$] SET [Read] = False WHERE [MailID] = 1"

 

[IndigoDragon]

Hi Jerry,

I don't get it either but that also return an error "No updateable query." The sheets name and column names are correct! There's nothing wrong with the connection because I can read data from it. And, like I said before, for testing I also set all column formats to 'general'.

???
cYa

 

[PHV]

Once the Connection is Open you may consider the Execute method instead of playing with a recordset.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[IndigoDragon]

Hi PHV,

You mean (?):

cn.Execute("UPDATE [Mails$] SET [Read] = 'False' WHERE [MailID] = '1'")

I get the same errors...?

 

[JerryKlmns]

From your posts I quess you are building a mail merge using excel + outlook. Why are you using excel for storing data and not access? Things would be faster and easier to accomplish.

 

[IndigoDragon]

Hi Jerry,

yep, a mail-merge it is. It actually grew from a small file, initially for 'a few' mails. There were already some modules written and I just started from there. I have been thinking about 'exporting' the whole project to Access or to set up a seperate data-file in Access. At first we had 'a-couple-a-hundred' mails to work through; by now it's p.m. 3thou of 'm...! So, yes, it would be a good thing to eventually migrate to Access. Also to make the data better available for the online component we're going add later on.
Is it possible to 'export' the project to Access? I guess so: VBA + Office, should make no difference, right?

However, back to 'the here and now'. I should be able to update the records in the external workbook through the connection! What's the F&#&*'in problem...!?!

ThanX for your replies so far!

 

[IndigoDragon]

Ofcourse, not without writing some of the code... But forms and most of the procedures should be the same... Or not?

 

[JerryKlmns]

In MS-Access this

cn.Execute "UPDATE [Mails$] SET [Read] = False WHERE [MailID] = 1"

would be enough. Sorry to say I 've come to a dead end with ADO and excel.
A different approach would be

For i= 2 to LastCellNo
If cell(i, ColumMailID).value = 1 then
cell(i, ColumnRead).Value = "False"
End If
Next i

-------------------------------------------------
About migrating to MS-Access: Don't be afraid, spent some extra time and start building in MS-Access! There are 7 fora for MS-Access plus a VB (Microsoft) Database in tek-tips.com. You 'll find both of us there and many others.
Feel free to join.

And a favor (not just from you):
Please do not use words like 'F&#&*'. I, among others, have a problem with them especially when used in public like these forums. Let 's keep it "professional" in tek-tips.com

 

[IndigoDragon]

Heya Jerry,

About the 'F'-word: ofcourse, you're right. However, it was just an expression and I didn't use it 'agressively'. These phantom-like problems while coding sometimes makes you wonder, in a 'Twilight Zone' kinda way, what ..... is going on. Well, enough about that, and 'thumbs-up' for the reminder, I'm with ya! My sincere apologies.

I have to finish this in Excel for the moment though. When that's up and running (almost finished) I'll start migrating everything to Access. I've worked with Access before but primarily using it as a database for a website. Haven't done much vba or used 'stored procedures' those times. And I always used UltraDev to do the db handling and punch out the code for that.
However, I learned a lot about vb/vba/office/etc. the last weeks so I'm certainly not afraid starting in Access. I guess you'll be seeing me there soon as I'm certainly no crack at this....yet. Haha.

About the ADO problem: So what you're saying is to forget updating the file with ADO for now and just open the file I want to read from. Right?

cYa.

 

[JerryKlmns]

Dear IndigoDragon,

I 'm afraid yes. I can 't be of more help. Unless somebody else shows up with a new idea. Folks! Any help?

----------------------
Looking forward to hearing from you in forum700, forum701, forum702, forum703, forum705, forum709, forum181

 

[IndigoDragon]

No problem, thanx for the help!

I guess I'll just open the file, for now. Why not anyway!?! And later on I'll use Access for stuff like this...

In the meantime, any suggestions from anyone are welcome ofcourse!

cYa.