Solved

Erro r In MS access database

Posted on 2000-03-23
4
178 Views
Last Modified: 2010-05-02
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("CustomerDetails", 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("LoanDetails", 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
'*************************************
0
Comment
Question by:amar_d
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2648789
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.
0
 
LVL 6

Expert Comment

by:anthonyc
ID: 2649085
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.  
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2649541
"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
0
 
LVL 2

Accepted Solution

by:
JetScootr earned 150 total points
ID: 2676774
The problem may be with interactions between where you're getting/setting bookmark and the Transaction actions.  Strange note about "ADDNEW":  You can also READ every field (including autonumber fields) between ADDNEW and UPDATE.  BUT when UPDATE occurs, recordset is ni an "undefined" location, i.e., no guarantee it's still on the record you just created.  SO:  Store off  field [CustomerID] prior to the Update.
Added suggestion: Store in a correctly typed variable, i.e., if it's autonumber, store in variable DIM'med as LONG.  Generally, I use the text/caption properties of controls ONLY for user interaction, and not for internal data use.  Code change shown below will also execute faster.
Change the code as shown:

      !AreaID = dlbAreaId
       txtCustID = !CustomerID      ' ADD THIS LINE
      .Update

' Delete the following lines:
      .Bookmark = .LastModified   'adding bookmark for last record added in the customerdetails table
     End With
     With rst
      txtCustID = !CustomerID
      End With
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now