Updating multiple table with single sql command

Fahad · Beginner User Joined: 16 Mar 2004 Posts: 16

hello,
I am developing a project in VB wich is using MS-Access as database. As we no it is a relational database system, can i update multiple releted table with a single sql query? or how can i take the benifit of this relational database system in updating related fields of a specific recod id?

Hoping to get a solution
fahad

Guest

As far as I know, it's not possbile to update several related tables by a single query in Access. As you know Access doesn't have trigger facility. At this position I can advice you to use Personal SQL server 2000(It's low according to price, though we have greater tendency to use pirate one). Now if you still like to use Access then I'd say you've to use multiple queries in a single VB procedure. You can make a function too and call it, but procedure is faster than function according to MS. e.g. Customer account.

Table1:Customer ID, Date, Type(Withdraw/deposite), Amount.
Table2:CustomerName, CustomerID, CurrentBalance.

Now If you use "insert query" in table 1 and create a trigger in your database(in this case you've to have RBMS), trigger will find the right customer in table2 and update current balance accroding to type(withdraw/deposite). In access you have to write 2 queries one insert query for Table1 and update query for table2. But you've to store the value of customerID and Type in two variables which will be needed in update query. There are lot of ways to do that. lolz. Welcome to the world of 32bit programming.

If you like to know how to write the code then you've to let us know the specific senario of your project. So, we can help.

Quote:

how can i take the benifit of this relational database system in updating related fields of a specific recod id?

It's not possible to do that with a single query in Access. You need multiple queries.

-DK.

dinangkur

Oh yes,
MSDE is a freely distributable version of SQL available from MS. You could emulate the triggers by using MSDE as SQL on the client machine and attaching tables into your MSAccess DB from MSDE.. or even use MSDE instead.

[http://www.microsoft.com/sql/msde/downloads/default.asp]

mis.bus.sfu.ca/tutorials/MSAccess/ tutorials/trigger.pdf

happy hacking.

-DK.

Fahad · Beginner User Joined: 16 Mar 2004 Posts: 16

Thank you for your informatic answers.

but can anybody tell me how can i make a trigger procedure in vb? or is it possible to do that? your example will be helpful for me.

Thanks
Fahad

dinangkur

It seems like you don't like to read. The link I provided contains all the information. If you want to be a programmer, get a habit of

studying. Though I'm providing the code.

Database:db1.mdb
VB6 project
Project:project1
form:form1
module:module1
Don't forget to add reference ADO data object 2.1 from project Menu->references

table1:Products (productID:number,ProductName:text, Quantity:number)
table2:Purchase (SerialNo:number, productID:number,quantity:number)

Add data to "Products" (1,Pepsi 550ml,0)
(2,Pepsi 350ml,0)

Form:form1
Textbox1:txtID
TextBox2:txtQuantity
Label1:lblSerialNo
Command button:cmdok

Check the code bellow:

module1

Code:

Option Explicit

Public cnn As ADODB.Connection 'adodb connection variable

'command object
Public Cmd1 As ADODB.Command
Public Cmd2 As ADODB.Command

Public ctrl As Control
Public Continue As Variant

Public rsProducts As ADODB.Recordset 'Table:Products
Public rsPurchase As ADODB.Recordset 'Table:Purchase

Public Sub Init()
Dim str As String

Set cnn = New ADODB.Connection

Set Cmd1 = New ADODB.Command
Set Cmd2 = New ADODB.Command

Set rsProducts = New ADODB.Recordset
Set rsPurchase = New ADODB.Recordset

'connected to the databse through connection string
str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb;Persist Security Info=False"
cnn.ConnectionString = str
cnn.Open

rsProducts.Open "Products", cnn, adOpenDynamic, adLockOptimistic
rsPurchase.Open "Purchase", cnn, adOpenDynamic, adLockOptimistic

Cmd1.ActiveConnection = cnn
Cmd2.ActiveConnection = cnn

End Sub

Public Sub Validation()
Continue = 1 'Set Continue value

For Each ctrl In Screen.ActiveForm.Controls
If TypeOf ctrl Is TextBox Then
If ctrl = "" Then
MsgBox "Valid Information is Required", vbExclamation
Continue = 0
ctrl.SetFocus: Exit Sub
End If
End If
Next
End Sub

form1

Code:

VERSION 5.00
Begin VB.Form Form1
Caption = "Form1"
ClientHeight = 2070
ClientLeft = 60
ClientTop = 345
ClientWidth = 2895
BeginProperty Font
Name = "Tahoma"
Size = 9.75
Charset = 0
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
LinkTopic = "Form1"
ScaleHeight = 2070
ScaleWidth = 2895
StartUpPosition = 3 'Windows Default
Begin VB.CommandButton cmdOk
Caption = "OK"
Height = 375
Left = 720
TabIndex = 6
Top = 1560
Width = 1455
End
Begin VB.TextBox txtQuantity
Height = 360
Left = 1200
TabIndex = 5
Top = 960
Width = 1575
End
Begin VB.TextBox txtID
Height = 360
Left = 1200
TabIndex = 4
Top = 480
Width = 1575
End
Begin VB.Label lblSerialNo
BorderStyle = 1 'Fixed Single
Height = 285
Left = 1200
TabIndex = 3
Top = 120
Width = 1575
End
Begin VB.Label Label3
AutoSize = -1 'True
Caption = "Quantity:"
Height = 240
Left = 315
TabIndex = 2
Top = 960
Width = 780
End
Begin VB.Label Label2
AutoSize = -1 'True
Caption = "Product ID:"
Height = 240
Left = 135
TabIndex = 1
Top = 480
Width = 960
End
Begin VB.Label Label1
AutoSize = -1 'True
Caption = "Serial No:"
Height = 240
Left = 240
TabIndex = 0
Top = 120
Width = 855
End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Dim x As Boolean

Private Sub cmdOk_Click()
Validation 'Call validation procedure
validate_productID 'check right product id

Dim entryStr As String
Dim updateStr As String

On Error GoTo eh

If Continue <> 0 And x = True Then
entryStr = "INSERT INTO Purchase VALUES(" & lblSerialNo & "," & txtID & "," & txtQuantity & ");"
Cmd1.CommandText = entryStr
Cmd1.Execute
rsPurchase.Requery

updateStr = "UPDATE Products SET Quantity = Quantity + " & txtQuantity & " WHERE ProductID = " & txtID & ";"
Cmd2.CommandText = updateStr
Cmd2.Execute
rsProducts.Requery

serialNo
txtID.Text = ""
txtQuantity.Text = ""
txtID.SetFocus

Else
MsgBox "Enter Valid Product ID", vbExclamation: Exit Sub
End If

eh:
If Err.Number <> 0 Then
MsgBox Err.Source & vbCrLf & Err.Description, vbExclamation: Exit Sub
End If
End Sub

Private Sub Form_Load()
Init 'call conncection module
serialNo 'call serialNo procedure to create auto serialno
End Sub

Public Sub serialNo()
Dim str As String
Dim n As Integer
rsPurchase.Requery
If rsPurchase.BOF Or rsPurchase.EOF Then
str = "1"
Else
rsPurchase.MoveLast
str = rsPurchase("serialNo")
n = Val(str)
n = n + 1
str = n
End If
lblSerialNo.Caption = str
End Sub

Private Sub txtID_KeyPress(KeyAscii As Integer)
If KeyAscii = 8 Then
Exit Sub
End If
If IsNumeric(VBA.Chr(KeyAscii)) = False Then
KeyAscii = 0
End If
End Sub

Private Sub txtQuantity_KeyPress(KeyAscii As Integer)
If KeyAscii = 8 Then
Exit Sub
End If
If IsNumeric(VBA.Chr(KeyAscii)) = False Then
KeyAscii = 0
End If
End Sub

Public Sub validate_productID()
With rsProducts
If .BOF And .EOF Then
Else
.MoveFirst
Do While Not .EOF
If txtID.Text = !productID Then
x = True: Exit Sub
End If
.MoveNext
Loop
End If
End With
End Sub

Happy Hacking.

-DK. :)
_________________
...we too are stardust...

Fahad · Beginner User Joined: 16 Mar 2004 Posts: 16

yes, it's very helpful. Thanks for the code and ur advice of habituating in studing.

Fahad