PDA

View Full Version : Problem with Update Statement



jwilder1
12-10-2008, 12:19 AM
I am having a problem creating a sql statement to update an excel database with information in row 7 of the attached file(Day.xls). I use ADO to retrieve info into row 3 of the attached file, process the information, and then return to the same record in the database file(dbYr.xls). This process may be repeated as many as 50 times per day. These attached files have been shortened to a few records and 9 fields, the final product, after I learn what I am doing, will have 2 years of information and 78 fields. I will attach the database file dbYr.xls in a seperate post. The procedure I am attempting to use is
Sub PushData()
Dim objRS As Object, dbPath As String, MyRange As String
Set objRS = CreateObject("ADODB.Recordset")
dbPath = ThisWorkbook.Path & "\dbYr.xls"
Dim Da As String
Da = ThisWorkbook.Sheets("Info").Range("B16").Value
Dim r As Range
Set r = [A7:I7]
MyRange = r.Name

objRS.Open "UPDATE [Sheet1$] WHERE DateNo= [Da] SELECT * FROM [MyRange] IN '" & ThisWorkbook.FullName & "' 'Excel 8.0;'", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";" & _
"Extended Properties=Excel 8.0;"

Set objRS = Nothing
End Sub
Thanks for any help, Jim

jwilder1
12-10-2008, 12:21 AM
Here is the database file

stanl
12-11-2008, 04:01 AM
SQL DML statement should be executed from either a Connection or Command Object. Even then, an UPDATE statment requires SET [one or more fields] = [one or more values].

If you will only be updating a certain worksheet row, I would consider keeping everything in VBA. .02 Stan