Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Thanked 0 Times in 0 Posts

    Best Method (Access97/SQL 2000)

    Forgive me, I am new to access forms but I have a lot of SQL experience. What I am looking for is the best method to make this work. This database is built around contact with individuals and their siblings

    Step 1. Pull up a person in a combo box, at the same time find the siblings names( if any )and pull them to a local access temp table. (works fine)
    Step 2. Insert the transaction information about the meeting with this person and retrieve the new record id and print.(works fine)
    Step3. For all siblings found - insert a record containing some information from the form and some information already stored in the database -- the insert fails because of some ambiguous maximum parameters error message. if I comment out one of these fields ( a comment box the is data type memo) it works.

    I also need to print the new records for the sibling(s) insert. Most of this programming was already in place but they had to change to an identity column in SQL so they could begin developing the web page project. Before they were just creating new records by finding dmax and adding 1 to it which has many inherent flaws. After I suggested we change to an identity column -- these issues have arrived.

    If you have any specific info -- I would appreciate it, but really I am looking for some genereal guidance to fix this problems. They do have a VBA programmer, that I believe if I get him started in the right direction -- we will get it working.

    Thanks in advance

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 58 Times in 58 Posts

    Re: Best Method (Access97/SQL 2000)

    These situations are always a challenge. You need to fix something in an existing product to make a new project work, and it breaks the old one. Do you redesign the old, or try to find a way to fix the problem with the old design? From your description, it sounds like there are some flaws with the original design, but it seemed to work. I would tend to stick with the old and fix the autonumber issue.

    The DMax approach has always been a problem - at least if you have a multi-user database, and it's also pretty slow. I would suggest you create a small 1-record table (in SQL for sure) that stores the next record ID for the table in question. Then when a person does an insert, you get that number and put it in your record, add 1 to it and resave it for the next insert. We use this technique exclusively where we need a record ID that is available immediately to Access, and where we want one that has some specific characteristics such as a year, month, date and serial number components. This should let you leave the rest of the design alone and not have to mess with things.

    I should also point out that your combination of Access97 and SQL2000 is not the best - there are a ton of new features in SQL2000 that you can't take advantage of in Access97. In addition, you may run into some problems with MDAC versions as SQL2000 wants to install version 2.7 which has some known "pecularities" that can cause unexpected results. One thing in particular is the use of memo fields - SQL 2000 supports large text fields, and Access97 doesn't know about that. I'm also curious why a temp table is being created in Access to store the siblings. One of the really powerful features of Access is the subform capability that lets you work with "child" records very easily. But now we are redesigning something that seems to work, so . . . . Hope this makes some sense and gives you some ideas.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts