amar_d
asked on
Erro r In MS access database
I am using MS-Access database with vb6 frontend in multiuser enviornment. For inserting record i used transaction object. first i am inserting & updating a row in a table and setting bookmark (for lastupdated)property for this. Then with bookmark i retrieve ID for next table insertion & updation of row . Again i am setting bookmark (for lastupdated)for the new table retrieving autonumberid for 3rd table insertion and updation. I found some of the rscords with "#Error" in database. I am reparing the database and using it againg. I use Workspace(0) for tranction. Will the same workspace used by all users and creating problem in database or Will it open on different Machine?For reference I am giving code part as under.Please consider it as urgent
On Error GoTo Err_Save_Click
Set wrkSpace = DBEngine.Workspaces(0)
Set db = OpenDatabase(App.Path & "\mydatabase.mdb")
If txtLName = "" Then MsgBox "LName is not Entered ": Exit Sub 'check for Lname
If cmbCity = "" Then MsgBox "City is not Selected": Exit Sub 'check for City
' If cmbArea.Columns(1).Value = "" Then MsgBox "Area is not Selected": Exit Sub 'check for Area
If cmbArea.Text = "" Then MsgBox "Area is not Selected": Exit Sub 'check for Area
Set rst = db.OpenRecordset("Customer Details", dbOpenDynaset) 'Open Customerdetails table
wrkSpace.BeginTrans
With rst 'adding new record to Customerdetails
.AddNew
!Title = Trim(cmbTitle)
!FirstName = Trim(txtFName)
!MiddleName = Trim(txtMName)
!LastName = Trim(txtLName)
!Address = Trim(txtAddress)
!City = Trim(cmbCity)
!PinCode = Trim(txtPin)
!Landmark = Trim(txtLandmark)
!HomePhone = Trim(txtHomePh)
!WorkPhone = Trim(txtCompPh)
!MobilePhone = Trim(txtMobilePh)
If IsDate(Trim(txtPrfDate)) Then !PreferredContactDate = Trim(txtPrfDate)
If Not (StrComp(Trim(txtPrfTime), "_:_:_") = 1) Then !PreferredContactTime = Format(Trim(txtPrfTime), "hh:mm:ss")
If Not (Trim(txtAge) = "") Then !Age = txtAge
!Qualification = cmbQualification
!Occupation = cmbOccupation
!EmploymentStatus = cmbEmpStatus
!IdentifiedOn = txtCntIdentOn
If Not (Trim(txtApplnIncome) = "") Then !ApplicantIncome = txtApplnIncome
If Not (Trim(txtSpouseIncome) = "") Then !SpouseIncome = Trim(txtSpouseIncome)
If Not (Trim(txtOthLoanInsta) = "") Then !OOInstallment = Trim(txtOthLoanInsta)
If Not (Trim(txtBalInstal) = "") Then !OOBalance = Trim(txtBalInstal)
!CompanyName = Trim(txtCompName)
!DMA = cmbDMA.Text
' !AreaID = cmbArea.Columns(1).Value
!AreaID = dlbAreaId
.Update
.Bookmark = .LastModified 'adding bookmark for last record added in the customerdetails table
End With
With rst
txtCustID = !CustomerID
End With
'************************* ********** ****
'Add records to contacts
Set rst1 = db.OpenRecordset("Contacts ", dbOpenDynaset)
With rst1
.AddNew
!Source = Trim(cmbSorceLead)
!EnquiryPoint = Trim(cmbEnquiryPoint)
!IdentifiedOn = Trim(txtCntIdentOn)
If IsDate(Trim(txtDisbDate)) Then !DisbursementDate = Format(Trim(txtDisbDate), "mm/dd/yy")
If IsDate(Trim(txtSancDate)) Then !SanctionDate = Format(Trim(txtSancDate), "mm/dd/yy")
If IsDate(Trim(txtApplinDate) ) Then !ApplicationDate = Format(Trim(txtApplinDate) , "mm/dd/yy")
!ActivityStatus = Trim(cmbActivity)
!Remarks = Trim(txtRemark)
!FollowupAction = Trim(txtFollupAction)
!Product = Trim(cmbProduct)
!CustomerID = Trim(txtCustID)
.Update
.Bookmark = .LastModified
End With
With rst1
txtPID = !PID
End With
'************************* ********** ****
'Open loandetails and add records to it
Set rst2 = db.OpenRecordset("LoanDeta ils", dbOpenDynaset)
With rst2
.AddNew
!PID = txtPID
!TypeOfLoan = cmbTypeohLoan
If Not (Trim(txtLoanAmt) = "") Then !LoanRequired = Trim(txtLoanAmt)
![PropertyIdentified(Y/N)] = Trim(cmbPropertyIdent)
!PropertyLocality = Trim(txtLocality)
!AdditionalInformation = Trim(txtAddiInformation)
.Update
End With
wrkSpace.CommitTrans
rst.Close
rst1.Close
rst2.Close
'************************* ********** **
On Error GoTo Err_Save_Click
Set wrkSpace = DBEngine.Workspaces(0)
Set db = OpenDatabase(App.Path & "\mydatabase.mdb")
If txtLName = "" Then MsgBox "LName is not Entered ": Exit Sub 'check for Lname
If cmbCity = "" Then MsgBox "City is not Selected": Exit Sub 'check for City
' If cmbArea.Columns(1).Value = "" Then MsgBox "Area is not Selected": Exit Sub 'check for Area
If cmbArea.Text = "" Then MsgBox "Area is not Selected": Exit Sub 'check for Area
Set rst = db.OpenRecordset("Customer
wrkSpace.BeginTrans
With rst 'adding new record to Customerdetails
.AddNew
!Title = Trim(cmbTitle)
!FirstName = Trim(txtFName)
!MiddleName = Trim(txtMName)
!LastName = Trim(txtLName)
!Address = Trim(txtAddress)
!City = Trim(cmbCity)
!PinCode = Trim(txtPin)
!Landmark = Trim(txtLandmark)
!HomePhone = Trim(txtHomePh)
!WorkPhone = Trim(txtCompPh)
!MobilePhone = Trim(txtMobilePh)
If IsDate(Trim(txtPrfDate)) Then !PreferredContactDate = Trim(txtPrfDate)
If Not (StrComp(Trim(txtPrfTime),
If Not (Trim(txtAge) = "") Then !Age = txtAge
!Qualification = cmbQualification
!Occupation = cmbOccupation
!EmploymentStatus = cmbEmpStatus
!IdentifiedOn = txtCntIdentOn
If Not (Trim(txtApplnIncome) = "") Then !ApplicantIncome = txtApplnIncome
If Not (Trim(txtSpouseIncome) = "") Then !SpouseIncome = Trim(txtSpouseIncome)
If Not (Trim(txtOthLoanInsta) = "") Then !OOInstallment = Trim(txtOthLoanInsta)
If Not (Trim(txtBalInstal) = "") Then !OOBalance = Trim(txtBalInstal)
!CompanyName = Trim(txtCompName)
!DMA = cmbDMA.Text
' !AreaID = cmbArea.Columns(1).Value
!AreaID = dlbAreaId
.Update
.Bookmark = .LastModified 'adding bookmark for last record added in the customerdetails table
End With
With rst
txtCustID = !CustomerID
End With
'*************************
'Add records to contacts
Set rst1 = db.OpenRecordset("Contacts
With rst1
.AddNew
!Source = Trim(cmbSorceLead)
!EnquiryPoint = Trim(cmbEnquiryPoint)
!IdentifiedOn = Trim(txtCntIdentOn)
If IsDate(Trim(txtDisbDate)) Then !DisbursementDate = Format(Trim(txtDisbDate), "mm/dd/yy")
If IsDate(Trim(txtSancDate)) Then !SanctionDate = Format(Trim(txtSancDate), "mm/dd/yy")
If IsDate(Trim(txtApplinDate)
!ActivityStatus = Trim(cmbActivity)
!Remarks = Trim(txtRemark)
!FollowupAction = Trim(txtFollupAction)
!Product = Trim(cmbProduct)
!CustomerID = Trim(txtCustID)
.Update
.Bookmark = .LastModified
End With
With rst1
txtPID = !PID
End With
'*************************
'Open loandetails and add records to it
Set rst2 = db.OpenRecordset("LoanDeta
With rst2
.AddNew
!PID = txtPID
!TypeOfLoan = cmbTypeohLoan
If Not (Trim(txtLoanAmt) = "") Then !LoanRequired = Trim(txtLoanAmt)
![PropertyIdentified(Y/N)]
!PropertyLocality = Trim(txtLocality)
!AdditionalInformation = Trim(txtAddiInformation)
.Update
End With
wrkSpace.CommitTrans
rst.Close
rst1.Close
rst2.Close
'*************************
The only thing i can propose is to open and close the recordsets inside the transaction, for the rest there seems nothing to be wrong at first glance.
since you are using MSAccess as a db, I don't see why you need the bookmarks. If you place the lines of code that get the ID right above the .Update, you'll get the ID you need. As soon as you issue the ADDNEW method, MSAccess makes the row, and grabs the next number from autonumber.
Try it without the bookmarks and see what happens.
Try it without the bookmarks and see what happens.
"Will the same workspace used by all users and creating problem in database or Will it open on different Machine?"
The workspace is instantiated seperately for each client. It is unique for each user.
Which records are failing?
Wes
The workspace is instantiated seperately for each client. It is unique for each user.
Which records are failing?
Wes
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.