Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Erro r In MS access database

Posted on 2000-03-23
4
Medium Priority
?
186 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Suggested Courses

609 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