-
Problem with Update Statement
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
[VBA]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[/VBA]
Thanks for any help, Jim
-
database file
Here is the database file
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules