MS Access does it
VBA does it
VB6?
I am linked to a MS SQL Server Database and my vba knowledge is of no use. I try the 2 following methods but neither work
An update recordset which has more than one table linked but only updating a single field does not work
Set objRs = New ADODB.Recordset
sSql = "UPDATE TempCollectables INNER JOIN SourceDest ON TempCollectables.SourceId = SourceDest.SourceId SET TempCollectables.SourceDestTypeId = [SourceDest]![SourceDestCode] WHERE (((SourceDest.SourceDestGroup)='" & "Intercompany" & "'));"
objRs.Open sSql, objConn, adOpenStatic, adLockOptimistic
Set objRs = Nothing
A select recordset based on more than one table with a step through and then update on a single field does not work
Set objRs = New ADODB.Recordset
sSql = "SELECT SourceDestCode, SourceDestTypeId FROM TempCollectables INNER JOIN SourceDest ON TempCollectables.SourceId = SourceDest.SourceId WHERE (SourceDest.SourceDestGroup ='" & "Intercompany" & "');"
objRs.Open sSql, objConn, adOpenStatic, adLockOptimistic
If Not objRs.EOF Then
'objRs.MoveFirst
Do While Not objRs.EOF
objRs!SourceDestTypeId = objRs!SourceDestCode
objRs.Update
objRs.MoveNext
Loop
End If
Set objRs = Nothing
How do I get around this or am I missing something
Cheers, a really helpful site
VBA does it
VB6?
I am linked to a MS SQL Server Database and my vba knowledge is of no use. I try the 2 following methods but neither work
An update recordset which has more than one table linked but only updating a single field does not work
Set objRs = New ADODB.Recordset
sSql = "UPDATE TempCollectables INNER JOIN SourceDest ON TempCollectables.SourceId = SourceDest.SourceId SET TempCollectables.SourceDestTypeId = [SourceDest]![SourceDestCode] WHERE (((SourceDest.SourceDestGroup)='" & "Intercompany" & "'));"
objRs.Open sSql, objConn, adOpenStatic, adLockOptimistic
Set objRs = Nothing
A select recordset based on more than one table with a step through and then update on a single field does not work
Set objRs = New ADODB.Recordset
sSql = "SELECT SourceDestCode, SourceDestTypeId FROM TempCollectables INNER JOIN SourceDest ON TempCollectables.SourceId = SourceDest.SourceId WHERE (SourceDest.SourceDestGroup ='" & "Intercompany" & "');"
objRs.Open sSql, objConn, adOpenStatic, adLockOptimistic
If Not objRs.EOF Then
'objRs.MoveFirst
Do While Not objRs.EOF
objRs!SourceDestTypeId = objRs!SourceDestCode
objRs.Update
objRs.MoveNext
Loop
End If
Set objRs = Nothing
How do I get around this or am I missing something
Cheers, a really helpful site