Update Mutiple Tables from One Form Using ADP

[Cheryl3D]

Hello:

I'm working on a Microsoft Access Project ADP in using Access 2000 and SQL Server 2000. How can I update (edit) existing records in several tables via one form in an ADP?

I have a form that is based on a complex view, with at least 4 or 5 different tables joined together for one view. I know that you can't update more than one table in an UPDATE SQL statement, but can't you put several UPDATE statements into one stored procedure?

To make sure that I properly update the correct record wouldn't I have to take the primary key (FundSite_ID) of the primary table for the view and use it in a where clause?

I thought that I could use a UPDATE SQL statement as follows:

CREATE PROCEDURE UpdateFundSites

@FundSite_ID int = NULL,
@Appropriation_ID varchar(30) = NULL,
@FundCode varchar(10) = NULL,
@FiscalYear varchar(4) = NULL,
@OBAN_ID varchar(15) = NULL,
@RCCC varchar(15) = NULL,
@BAC varchar(6) = NULL,
@SAG varchar(6) = NULL,
@PEC varchar(15) = NULL,
@MajorForceProgram_ID varchar(6) = NULL,
@FunctionalCatgeory varchar (15) = NULL,
@BPAC varchar (6) = NULL,
@Project varchar(15) = NULL,
@Bluebook smallint = NULL,
@FAST smallint = NULL,
@Payroll smallint = NULL,
@Rollover bit = NULL,
@Title varchar(75) = NULL,
@SiteDescription varchar(75) = NULL,
@ResourceAdvisor ??
@Organization_ID varchar(40) = NULL,
@BudgetAnalyst ??
@OPLOC_ADSN varchar(15) = NULL,
@OfficeSymbol varchar(40) = NULL,
@Installation ??
@FMS_CC varchar(6) = NULL,
@FMS_Case varchar(6) = NULL,
@FMS_Line varchar(6) = NULL,
@FMS_Status varchar(15) = NULL,
@SiteNumber varchar(50) = NULL,
@CommandCode varchar(10) = NULL,
@FunctionalAccountCode varchar (10) = NULL,
@PAS varchar(10) = NULL,
@CivOBAN varchar(15) = NULL,
@_1PY varchar(10) = NULL,
@ESPC varchar(6) = NULL,
@EEIC varchar(15) = NULL,
@SalesCode varchar(6) = NULL,
@MaterielCode varchar(10) = NULL,
@BAAN varchar(15) = NULL,
@SystemsMgtCode varchar(10) = NULL,
@OldLocationCode varchar(10) = NULL

AS
UPDATE dbo.FundSites
SET Appropriation_ID = @Appropriation_ID,
FiscalYear = @FiscalYear,
OBAN_ID = @OBAN_ID,
RCCC = @RCCC,
BAC = @BAC,
SAG = @SAG,
PEC = @PEC,
MajorForceProgram_ID = @MajorForceProgram_ID,
FunctionalCategory = @FunctionalCategory,
BPAC = @BPAC,
Project = @Project,
Bluebook = @Bluebook,
FAST = @FAST,
Payroll = @Payroll,
Title = @Title,
SiteDescription = @SiteDescription,
OPLOC_ADSN = @OPLOC_ADSN,
OfficeSymbol = @OfficeSymbol,
FMS_CC = @FMS_CC,
FMS_Case = @FMS_Case,
FMS_Line = @FMS_Line,
FMS_Status = @FMS_Status,
SiteNumber = @SiteNumber,
CommandCode = @CommandCode,
FunctionalAccountCode = @FunctionalAccountCode,
PAS = @PAS,
CivOBAN = @CivOBAN,
_1PY = @_1PY,
ESPC = @ESPC,
EEIC = @EEIC,
SalesCode = @SalesCode,
MaterielCode = @MaterielCode,
BAAN = @BAAN,
SystemsMgtCode = @SystemsMgtCode,
OldLocationCode = @OldLocationCode
WHERE dbo.FundSites.FundSite_ID = @FundSite_ID

GO


UPDATE dbo.AppropriatedOBAN
SET Title = @Title

GO
UPDATE dbo.Appropriations
SET FundCode = @FundCode

GO

The input parameter fields (@....) represents the textbox fields on the data entry form that is bound to the complex view. Should I make this form an unbound form since the records that appear on the form are feed from another form with Me.OpenArgs? (Me.OpenArgs is based on the same complex SQL view mentioned earlier called 'qryfundciteView'.)


I believe that I need to call the stored procedure from the forms 'AfterUpdate' event to get any changes that the user typed on the form committed.


The ADO code would look somthing like this:

Private Sub Form_AfterUpdate()

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim Parm As Parameter
Dim rst As ADODB.Recordset

Set cnn = Application.CurrentProject.Connection

Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "UpdateFundSites"
cmd.CommandType = adCmdStoredProc

Me.Appropriation_ID = Me.Appropriation_ID
Me.BookYear = Me.BookYear
Me.FiscalYear = Me.FiscalYear
Me.OBAN_ID = Me.OBAN_ID
Me.BAC = Me.BAC
....

Set Parm = cmd.CreateParameter("Appropriaton_ID", adVarChar, adParamInput, 30, Appropriation_ID)
cmd.Parameters.Append Parm

Set Parm = cmd.CreateParameter("BookYear", adVarChar, adParamInput, 4, BookYear)
cmd.Parameters.Append Parm

Set Parm = cmd.CreateParameter("FiscalYear", adVarChar, adParamInput, 4, FiscalYear)
cmd.Parameters.Append Parm
...

'Execute command object.
cmd.Execute

'Close objects
Set Parm = Nothing
Set cmd = Nothing
Set cnn = Nothing

End Sub

Am I on the right track? Shouldn't this work?

Thanks,
Cheryl3D

 

[pharcyder]

Hi Cheryl3D !

Well, for my part I'm using sub-forms.

Because this is the easier version of using different tables in ONE main form and update them as you like (in an .adp)
(just camouflage them by disabling the borders, recordset-navigators and so on... and link them to the "main" table).

Then later you don't have trouble in changing/adding some fields.

Just a suggestion.

Greetings,
Jens

 

[cmmrfrds]

You can put multiple update statements in a stored procedure. You can also string multiple sql statements in a single batch.

dim sql as string.

sql = "update tab set xx=1; update tab2 set yy=2; etc...."

cn.execute sql