Solved

Erro r In MS access database

Posted on 2000-03-23
4
180 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

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

Suggested Solutions

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

810 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