Originally Posted by
Gicu
You almost got it, you don't need the recordset, to run it you simply use the .Execute method of the QueryDef object:
Code:
qdf.SQL = "UPDATE tblYourTable SET Field=....."
qdf.ReturnsRecords = False 'your update queries don't return records
qdf.Execute
Cheers,
When I use qdf.execute I get ODBC connection failed even though I am using the same string as I was to connect to linked tables.
Code:
Public Function WTUpdate()
On Error GoTo Error_Connection
Call SetVars
Dim WTqdf As DAO.QueryDef, WTrst As DAO.Recordset, Number
Dim UpdateSQL(0 To 6) As String
UpdateSQL(0) = "UPDATE " & TableFound & " SET StatusCode = 'COM' WHERE " & StatusCriteria
UpdateSQL(1) = "UPDATE " & TableFound & " SET StatusTime = '" & Format(Time, "hhmm") & "' WHERE " & StatusCriteria
UpdateSQL(2) = "UPDATE " & TableFound & " SET CurrentStatusComment = 'T/T COMPLETED' WHERE " & StatusCriteria
UpdateSQL(3) = "UPDATE " & TableFound & " SET TimeUpdated = '" & CurrentTime() & "' WHERE " & StatusCriteria
UpdateSQL(4) = "UPDATE " & TableFound & " SET DateUpdated = '" & Date & "' WHERE " & StatusCriteria
UpdateSQL(5) = "UPDATE " & TableFound & " SET UserUpdatedKey = '" & sUserKey & "' WHERE " & StatusCriteria
UpdateSQL(6) = "UPDATE " & TableFound & " SET StatusDate = '" & Format(Date, "yyyy-mm-dd") & "' WHERE " & StatusCriteria
Set WTqdf = CurrentDb.CreateQueryDef("")
WTqdf.Connect = "ODBC;Driver={SQL Server Native Client 11.0};Server=server;Database=database;Network=DBNMPNTW;UID=UID;PWD=PWD;"
For Each Number In UpdateSQL
WTqdf.SQL = Number
WTqdf.ReturnsRecords = False
WTqdf.Execute
Next Number
Exit Function
Error_Connection:
Call Msgbox("Cannot mark step complete. Someone may be editing the record." & vbCrLf & "Error: " & Error & " " & Err.Number)
End Function