Update Recordset Error Using ADO

[Cheryl3D]

Hello:

I'm getting an "Insufficient base table information for updating or refreshing" error when I attempt to update one field "ResourceAdvisor_ID" within a table called FundSites. I'm using a SQL Server 2000 back-end with an Access 2000 project (.adp) for the front-end.

I am using the following code when a command button is clicked:

Private Sub cmdChangeAdvisor_Click()
Dim strSQL As String, cnn As ADODB.Connection,
rst As ADODB.Recordset
Dim strRecordSource As String
Dim intCount As Integer, X As Integer
Dim lngNewRA As Long


strSQL = Me.OpenArgs
If Not IsNull(Me!NewRA) Then
lngNewRA = Me!NewRA
Set cnn = Application.CurrentProject.Connection
Set rst = CreateObject("ADODB.Recordset&quot
rst.CursorLocation = adUseServer
rst.Open strSQL, cnn, 2, 2
rst.MoveLast
intCount = rst.RecordCount
rst.MoveFirst
For X = 0 To intCount - 1
'rst.Edit
rst!ResourceAdvisor_ID = lngNewRA
rst.Update
rst.MoveNext
Next X
rst.Close

Set cnn = Nothing
MsgBox intCount & " records have been changed."
Else
MsgBox "Please select the New Advisor."
End If
End Sub

I trying to update the old value in rst!ResourceAdvisor_ID with the new value captured in variable lngNewRA.
The error occurs for the line stating -- rst.Update. The strSQL (SQL String) being read into Me.OpenArgs is:

strSQL = "SELECT * FROM qryfundciteView"

The object qryfundsiteView is a view.
Below is the SQL statement for the view, but I don't think it is updateable.

SELECT TOP 100 PERCENT dbo.FundSites.Appropriation_ID,
dbo.Appropriations.AppropriationSymbol_ID,
dbo.Appropriations.FundCode, dbo.FundSites.BookYear,
dbo.FundSites.FiscalYear, dbo.FundSites.OBAN_ID,
dbperatingBudgets.OperatingAgency_ID,
dbo.FundSites.PEC, dbo.FundSites.BAC, dbo.FundSites.Project,
dbo.FundSites.RCCC, dbo.FundSites.FMS_CC,
dbo.FundSites.FMS_Case, dbo.FundSites.FMS_Line,
dbo.AppropriatedOBANs.Title, dbo.FundSites.SiteDescription,
dbo.FundSites.SiteNumber, dbo.FundSites.Rollover_ID,
dbo.FundSites.Rollover, dbo.FundSites.NonRolloverJustify,
dbo.FundSites.BPAC, dbo.FundSites.OfficeSymbol,
dbo.FundSites.ResourceAdvisor_ID,
ISNULL(dbo.Contacts.Title + ' ' + dbo.Contacts.FirstName + ' ' + dbo.Contacts.LastName,
dbo.Contacts.FirstName + ' ' + dbo.Contacts.LastName)
AS ResourceAdvisor, dbrganizations.Organization_ID,
dbrganizations.Address, dbrganizations.City,
dbrganizations.State_Provence, dbrganizations.Zip,
dbrganizations.Country, dbo.Contacts.E_MAIL AS [E-Mail],
dbo.Contacts.Phone_Comm, dbo.Contacts.Fax_Comm,
dbo.Contacts.Phone_DSN, dbo.Contacts.Fax_DSN,
dbo.FundSites.FunctionalCategory,
dbo.FundSites.BudgetAnalyst_ID,
ISNULL(Contacts_1.Title + ' ' + Contacts_1.FirstName + ' ' + Contacts_1.LastName,
Contacts_1.FirstName + ' ' + Contacts_1.LastName)
AS BudgetAnalyst, dbo.FundSites.OPLOC_ADSN,
dbo.FundSites.Installation_ID,
dbo.Installations.Installation_Name + ' ' + dbo.Installations.Installation_Type
AS Installation, dbo.FundSites.OldLocationCode,
dbo.FundSites.TwoLtrOfficeSymbol,
dbo.FundSites.TwoLtrBudgetAnalyst_ID,
ISNULL(Contacts_2.Title + ' ' + Contacts_2.FirstName + ' ' + Contacts_2.LastName,
Contacts_2.FirstName + ' ' + Contacts_2.LastName)
AS TwoLtrBudgetAnalyst, dbo.FundSites.CommandCode,
dbo.FundSites.FunctionalAccountCode, dbo.FundSites.EEIC,
dbo.FundSites.SalesCode, dbo.FundSites.FMS_Status,
dbo.FundSites.ESPC, dbo.FundSites.PAS,
dbo.FundSites.MaterielCode, dbo.FundSites._1PY AS [1PY],
dbo.FundSites.ResourceManager_ID,
ISNULL(Contacts_3.Title + ' ' + Contacts_3.FirstName + ' ' + Contacts_3.LastName,
Contacts_3.FirstName + ' ' + Contacts_3.LastName)
AS ResourceManager,
dbo.FundSites.ResourceMgrOfficeSymbol,
dbo.FundSites.CostCenterManager_ID,
ISNULL(Contacts_4.Title + ' ' + Contacts_4.FirstName + ' ' + Contacts_4.LastName,
Contacts_4.FirstName + ' ' + Contacts_4.LastName)
AS CostCenterManager,
dbo.FundSites.CostCtrMgrOfficeSymbol,
dbo.FundSites.Bluebook, dbo.FundSites.FAST,
dbo.FundSites.Payroll, dbo.FundSites.CivOBAN,
dbo.FundSites.BAAN, dbo.FundSites.SystemsMgtCode,
dbo.Agencies.Agency_ID, dbo.FundSites.EntryDate,
dbo.FundSites.AdminID, dbo.FundSites.FundSite_ID,
dbo.FundSites.BudgtAnalystOfficeSymbol,
dbo.FundSites.ResAdvisorOfficeSymbol,
dbo.FundSites.MajorForceProgram_ID,
dbo.FundSites.SAG
FROM dbo.SubActivityGroups RIGHT OUTER JOIN
dbperatingBudgets INNER JOIN
dbo.Appropriations INNER JOIN
dbo.Agencies ON
dbo.Appropriations.Agency_ID = dbo.Agencies.Agency_ID INNER
JOIN
dbo.FundSites INNER JOIN
dbo.AppropriatedOBANs ON
dbo.FundSites.OBAN_ID = dbo.AppropriatedOBANs.OBAN_ID AND
dbo.FundSites.Appropriation_ID = dbo.AppropriatedOBANs.Appropriation_ID
LEFT OUTER JOIN
dbo.ProgramElemen dbo.Installations ON
dbo.FundSites.Installation_ID = dbo.Installations.Installation_ID LEFT
OUTER JOIN
dbo.Contacts ON
dbo.FundSites.ResourceAdvisor_ID = dbo.Contacts.Contact_ID LEFT
OUTER JOIN
dbo.Contacts Contacts_1 ON
dbo.FundSites.BudgetAnalyst_ID = Contacts_1.Contact_ID LEFT OUTER
JOIN
dbrganizations ON
dbo.Contacts.Organization_ID = dbrganizations.Organization_ID
LEFT OUTER JOIN
dbrganizations Organizations_1 ON
Contacts_1.Organization_ID = Organizations_1.Organization_ID LEFT
OUTER JOIN
dbo.Contacts Contacts_2 ON
dbo.FundSites.TwoLtrBudgetAnalyst_ID = Contacts_2.Contact_ID
LEFT OUTER JOIN
dbrganizations Organizations_2 ON
dbo.FundSites.TwoLtrOfficeSymbol = Organizations_2.Organization_ID
LEFT OUTER JOIN
dbo.Contacts Contacts_3 ON
dbo.FundSites.ResourceManager_ID = Contacts_3.Contact_ID LEFT
OUTER JOIN
dbrganizations Organizations_3 ON
dbo.FundSites.ResourceMgrOfficeSymbol = Organizations_3.Organization_ID
LEFT OUTER JOIN
dbo.Contacts Contacts_4 ON
dbo.FundSites.CostCenterManager_ID = Contacts_4.Contact_ID LEFT
OUTER JOIN
dbrganizations Organizations_4 ON
dbo.FundSites.CostCtrMgrOfficeSymbol = Organizations_4.Organization_ID
LEFT OUTER JOIN
dbrganizations Organizations_5 ON
dbo.FundSites.ResAdvisorOfficeSymbol = Organizations_5.Organization_ID
LEFT OUTER JOIN
dbrganizations Organizations_6 ON
dbo.FundSites.BudgtAnalystOfficeSymbol = Organizations_6.Organization_ID
ON
dbo.Appropriations.Appropriation_ID = dbo.AppropriatedOBANs.Appropriation_ID
ON
dbperatingBudgets.OBAN_ID = dbo.AppropriatedOBANs.OBAN_ID
ON
dbo.SubActivityGroups.SAG_ID = dbo.ProgramElements.SAG_ID


As you can see the select statement is quite complex. The field I'm attempting to update is -- ResourceAdvisor_ID within the FundSites table (dbo.FundSites). Given the code above for the Click event, how can I make this update the one field? Is there a possible work around? Is there any way that I can identify the FundSites table as the unique base table so that the code will perform the update?

Any help would be greatly appreciated.
Thank you,
Cheryl3D

 

[Nikita6003]

Hi Cheryl,

I think your problem is due partly to the fact that dbo.FundSites.ResourceAdvisor_ID is used in a join on dbo.Contacts.Contact_ID

If all the code's doing is to update the ResourceAdvisor_ID could you not run two update SQls to update both RecourceAdvisor_ID and Contact_ID?

Cheers
Nikki

 

[Cheryl3D]

Thanks Nikita6003:

I'm kind of new to SQL. So how would the update SQL statement look for both ResourceAdvisor_ID and Contact_ID? Please advise, if you don't mind.

Cheers
Cheryl3D