Update Query On External Table Without Linking

[chathag]

Is there any way of updating an external db table without using file-get external data - link table?

Thanks

GC

 

[pbaldy]

Yes; look at the IN clause in help.

 

[chathag]

Hi,

Using the IN clause I have compiled the following code:

Private Sub Btn_UpdateExternal_Click()

Dim dbPath As String
dbPath = "C:\Documents and Settings\cvgch220\My Documents\db2.mdb"

DoCmd.RunSQL "UPDATE Tbl_ChildName SET Tbl_ChildName.ChildName = ' A ', Tbl_ChildName.SchoolName = ' A '" & _
"WHERE Tbl_ChildName.SchoolName = 'School A' IN ('" & dbPath & "')"

End Sub

When I run the code the about to update rows message appears however it keeps advising 0 rows are about to be updated.

I have doubled checked the spellings for file paths, tables etc but cannot understand why no rows are being updated.

GC

 

[pbaldy]

I'm not sure of that syntax. I believe the IN clause needs to be right after the table name. I just tested this and it worked:

UPDATE TableName IN 'C:\FileName.mdb' SET FieldName = "blah"
WHERE ID = 1

 

[chathag]

Thanks for your help good buddy.