?
Solved

Erro r In MS access database

Posted on 2000-03-23
4
Medium Priority
?
185 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 143

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 450 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses
Course of the Month13 days, 4 hours left to enroll

777 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