HandsOn Using a Database Transaction to Insert Records

1. Insert a new module and, in the module's Code window, enter the Create_ Transaction procedure as shown below.

Sub Create_Transaction()

Dim conn As ADODB.Connection

On Error GoTo ErrorHandler

Set conn = New ADODB.Connection

With conn

.Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Data Source = " & _

CurrentProject.Path & "\Northwind.mdb"

.Open

.BeginTrans

' insert a new customer record .Execute "INSERT INTO Customers " & _

"Values ('GWIPO','Gwiazda Polarna'," &_

"'Marcin Garnia', 'Sales Manager', 'ul.Majewskiego 10'," &_

"'Warszawa', Null, '02-106', 'Poland', '0114822230445', Null)"

' insert the order for that customer .Execute "INSERT INTO Orders " & _

" (CustomerId, EmployeeId, OrderDate, RequiredDate) " & _ " Values ('GWIPO', 1, Date(), Date()+5)"

.CommitTrans .Close

MsgBox "Both inserts completed." End With

ExitHere:

Set conn = Nothing Exit Sub ErrorHandler:

If Err.Number = -2147467259 Then

Part II

MsgBox Err.Description Resume ExitHere

Else

MsgBox Err.Description With conn

.RollbackTrans .Close End With Resume ExitHere End If End Sub

The first SQL INSERT INTO statement inserts the customer data into the Customers table in the Northwind database. Before the customer can actually order specific products, a record must be added to the Orders table. The second SQL INSERT INTO statement takes care of this task. Because both inserts must occur prior to filling in order details, they are treated as a single transaction. If an error occurs anywhere (for example, the Orders table is open in the Design view), the entire transaction is rolled back.

Notice how the INSERT INTO statement is used in this procedure. If you do not specify the field names, you will need to include values for each field in the table.

1 Customers : Table

SEE

1 Customer ID

Company Name

Contact Name

Contad Title

Address

City

Region

Postal C *

+ GOURL

Gourmet Lanchonetes

André Fonseca

Sales Associate

Av. Brasil, 442

Campinas

SP

wa?6 701

* G REAL

Great Lakes Food Market

Howard Snyder

Marketing Manager

2732 Baker Bk)

Eugene

OR

97403

+ GROSR

G ROSE LIA- Restaura nt e

Manuel Pereira

Owner

5a Ave. Los Pafos Grandes

Caracas

DF

1031

- GWIPO

Gwiazda Polama

Marc in Gamia

Sales Manager

ul Majewskiego 10

Warszawa

02-106 I

| Order ID | Employee | Order Date | Required Date | Shipped Date | Ship Via | Freight |

Ship Name

t

I* 11078! Darolio, Nancy 11-Mar-2QQ4 16-Mar-2Q04

$0,00

mz

:oNurnbe.')

$O.GD

HAIMAR

Hanari Carnes

Mario Pontes

Accounting Manager

Rua do Paço, 67

Rio de Janeiro

RJ

D5JE4-S7E »

Ii R«ord: mur

34 [ ► II !►*] of 92

<.i

>

Figure 16-13: After running the procedure in Hands-On 16-6, a record for a new customer, "GWIPO," is added to the Customers and Orders tables.

Figure 16-13: After running the procedure in Hands-On 16-6, a record for a new customer, "GWIPO," is added to the Customers and Orders tables.

Try using transaction processing for archiving historical data. For instance, write a VBA procedure that selects orders that were placed prior to a specific date, append them to your archive table, and delete them from the current Orders table.

Creating and Manipulating Databases with ADO

0 0

Post a comment