| Sub ADOTransactions() On Error GoTo ADOTransactions_Err Dim cnn As New ADODB.Connection Dim cat As New ADOX.Catalog Dim tbl As New ADOX.Table Dim bTrans As Boolean ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=.\NorthWind.mdb;" ' Begin the Transaction cnn.BeginTrans bTrans = True Set cat.ActiveConnection = cnn ' Create the Contacts table With tbl .Name = "Contacts" Set .ParentCatalog = cat .Columns.Append "ContactId", adInteger .Columns("ContactId").Properties("AutoIncrement") = True .Columns.Append "ContactName", adWChar .Columns.Append "ContactTitle", adWChar .Columns.Append "Phone", adWChar .Columns.Append "Notes", adLongVarWChar .Columns("Notes").Attributes = adColNullable End With cat.Tables.Append tbl ' Populate the Contacts table with information from the ' customers table cnn.Execute "INSERT INTO Contacts (ContactName, ContactTitle," & _ "Phone) SELECT DISTINCTROW Customers.ContactName," & _ "Customers.ContactTitle, Customers.Phone FROM Customers;" ' Add a ContactId field to the Customers Table Set tbl = cat.Tables("Customers") tbl.Columns.Append "ContactId", adInteger ' Populate the Customers table with the appropriate ContactId cnn.Execute "UPDATE DISTINCTROW Contacts INNER JOIN Customers " _ & "ON Contacts.ContactName = Customers.ContactName SET " & _ "Customers.ContactId = [Contacts].[ContactId];" ' Delete the ContactName, ContactTitle, and Phone columns ' from Customers tbl.Columns.Delete "ContactName" tbl.Columns.Delete "ContactTitle" tbl.Columns.Delete "Phone" ' Commit the transaction cnn.CommitTrans Exit Sub ADOTransactions_Err: If bTrans Then cnn.RollbackTrans Debug.Print cnn.Errors(0).Description Debug.Print cnn.Errors(0).Number Debug.Print cnn.Errors(0).SQLState End Sub |
Transactions |
India web developer web development India | Freelance web development ecommerce web developer | Prayagasoft - web designer India, Ecommerce developer india, Ecommerce design