SQL procedure

[jabond007]

Could somebody tell me as to how to create a Procedure in Access using SQL.

I basically need to create a procedure to do the following in the same procedure
a) Insert values into a table
b) Commit the data
c) Drop table

Could u please give me the syntax for it. I tried the help but it wasnt helpful..... [sig][/sig]

 

[mdav2]

You will have to create a recordset of the data you want, I usually write this as a function. Make sure that it is a dynaset so it can be updated (see example below)

' This creates the team recordset, based on all teams in the team table
Dim dbs As Database
Dim sqlstr As String

' rstTeam is declared as a global variable
' The rstPerson recordset is a based on the team table details
Set dbs = CurrentDb
sqlstr = "SELECT * FROM t_team"
Set rstTeam = dbs.OpenRecordset(sqlstr, dbOpenDynaset)

you can then write some code that updates the recordset like:

With rstPerson
' Add new record
.AddNew
' Replace the fields with the values from the form
!PayrollNumber = Me!PayrollNumber
!Title = Me!Title
!FirstName = Me!FirstName
!Surname = Me!Surname
' Update the recordset
.Update
' Set the bookmark value to the added record
.Bookmark = .LastModified
End With

Try looking into the help for the following methods:

* BeginTrans
* CommitTrans
* Rollback

And Edit and AddNew methods.

Hope this is what you were after.