Running a SQL command

[Bre-x]

Hi,
I need to update a record on a ms access database from MS Excel (Office
2000)
I dont need nor i want to open the ms acesss db

This is the sql command

"UPDATE DB_ToolSheet SET DB_ToolSheet.HasChanged = 'Yes' WHERE
[DB_ToolSheet].[ToolSheetID]=" & Sheets("Data").Range("A1").Value;"

I hope someone can help me with this function I am new using MS Excel

Thank you all,

Bre-x

 

[Bre-x]

Sub InsertData()
Dim cnn As Connection
Dim ConStr As String, Sql As String
ConStr = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=E:\Access\CNC\CNC_Tables.mdb;" & _
"Uid=admin;" & _
"Pwd="
Sql = "UPDATE DB_ToolSheet SET DB_ToolSheet.HasChanged = 'Yes' WHERE
[DB_ToolSheet].[ToolSheetID]= " & Sheets("Data").Range("A3").Value
Set cnn = New ADODB.Connection
With cnn
.Open ConStr
.Execute (Sql)
.Close
End With
Set cnn = Nothing
End Sub

 

[Bre-x]

Actually some how this command is corrupting the all record.
please do not use.

Bre-x

 

[Francine Kubaka]

Bre-X, right?

Anyway, you may want to check the variable types - the ones you writing
back to the database.

Like this:
Dim vToolSheetID as Variant
Dim strSQL as String
vToolSheetID = Sheets("Data").Range("A3").Value
strSQL = "UPDATE DB_ToolSheet SET HasChanged = 'Yes' WHERE
ToolSheetID= " & CLng(vToolSheetID) &";"
........
Secondly, you may want to do it with DAO. DAO works much smoother with
Excel.

Thirdly, try and retrieve the records you need first and check if your SQL
statement retrieves them correctly.

Cheers,
A

Actually some how this command is corrupting the all record.
please do not use.

Bre-x

Sub InsertData()
Dim cnn As Connection
Dim ConStr As String, Sql As String
ConStr = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=E:\Access\CNC\CNC_Tables.mdb;" & _
"Uid=admin;" & _
"Pwd="
Sql = "UPDATE DB_ToolSheet SET DB_ToolSheet.HasChanged = 'Yes' WHERE
[DB_ToolSheet].[ToolSheetID]= " & Sheets("Data").Range("A3").Value
Set cnn = New ADODB.Connection
With cnn
.Open ConStr
.Execute (Sql)
.Close
End With
Set cnn = Nothing
End Sub