Update Record in a seperate Database

N

neshev

Hi,
I am trying to update a record in a different database to the current one
that I am using. I wish to update the Job status field to INSPECTION COMPLETE
in the other database. The problem that I have is that I only know how to
update information in my current database. code below, the update line is
suited here for current database, i do not know how to modify this to work
with the other database (LFilename). any help will be great
Neil

lfilename = "C:\MasterDB.mdb"

Set db = ws.OpenDatabase(lfilename)
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

If Not rs.EOF Then

strSQL = "UPDATE [Tbl_Inspection Schedule]"
strSQL = strSQL & " Set Job_Status= ' " & '"INSPECTION COMPLETE"
& "', "
strSQL = strSQL & " WHERE [Job ID]='" & Me.Job_ID.Text & "' "

End If
 
A

Allen Browne

Execute the Update query:

Dim db As DAO.Database
Dim strSql As String

strSQL = "UPDATE [Tbl_Inspection Schedule] " &_
"Set Job_Status= '"INSPECTION COMPLETE"" " & _
"WHERE [Job ID]= " & Me.Job_ID & ";"
'Debug.Print strSql

Set db = OpenDatabase("C:\MasterDB.mdb")
db.Execute strSql
db.Close

If the query does not work correctly, remove the single quote from the
debug.print line, and run it again. Then open the Immediate Window (Ctrl+G),
and see if you can track what's wrong with the statement.

For example, if Job ID is a Text type field (not a Number type), you do need
extra quotes:
"WHERE [Job ID]= """ & Me.Job_ID & """;"
 
N

neshev

Hi Allen,
works perfect now, I really apreciate your help with this,

Kind Regards,

Neil


Allen Browne said:
Execute the Update query:

Dim db As DAO.Database
Dim strSql As String

strSQL = "UPDATE [Tbl_Inspection Schedule] " &_
"Set Job_Status= '"INSPECTION COMPLETE"" " & _
"WHERE [Job ID]= " & Me.Job_ID & ";"
'Debug.Print strSql

Set db = OpenDatabase("C:\MasterDB.mdb")
db.Execute strSql
db.Close

If the query does not work correctly, remove the single quote from the
debug.print line, and run it again. Then open the Immediate Window (Ctrl+G),
and see if you can track what's wrong with the statement.

For example, if Job ID is a Text type field (not a Number type), you do need
extra quotes:
"WHERE [Job ID]= """ & Me.Job_ID & """;"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

neshev said:
Hi,
I am trying to update a record in a different database to the current one
that I am using. I wish to update the Job status field to INSPECTION
COMPLETE
in the other database. The problem that I have is that I only know how to
update information in my current database. code below, the update line is
suited here for current database, i do not know how to modify this to work
with the other database (LFilename). any help will be great
Neil

lfilename = "C:\MasterDB.mdb"

Set db = ws.OpenDatabase(lfilename)
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

If Not rs.EOF Then

strSQL = "UPDATE [Tbl_Inspection Schedule]"
strSQL = strSQL & " Set Job_Status= ' " & '"INSPECTION
COMPLETE"
& "', "
strSQL = strSQL & " WHERE [Job ID]='" & Me.Job_ID.Text & "' "

End If
Click to expand...
Click to expand...
 
Top