INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Multitable Insert with Access and DB2000

Multitable Insert with Access and DB2000

(OP)
I am working on my first Drumbeat project and have run into a problem. Using an Access database, I want to create basic update and insert pages. The database has a one to many relationship using a primary autonumbered key, CustomerID. I don't want to expose the Primary Key on the ASP forms (for obvious reasons) but need to pass the generated CustomerID from one form (with input for the one side
of the equation, Client Info), to a second form where additional, related record(s) (the many side of the
relationship, pet information) can be entered.

I have successfully constructed a SQL statement with the appropriate JOIN and have created a table (recordset) for use in the project. I also have a good ODBC connection. I am working on a IIS test system running on a NT 4.0 Server.


I have learned that you can pass session variables from one page to another, but that doesn't solve the problem. If I add the AutoNumbered CustomerID key to the first (Client information) ASP page as a hidden, and/or read only variable, I get ODBC error messages when submitting the form. Microsoft's Knowledge Base suggests the removal of Primary Key Fields from the submission page and this does indeed permit entry of data on the one (client) side of the relationship. But  leaves me unable to successfully insert records into the second (many side) related table.

Entry of data in a one to many relationship has to be a fairly common requirement.... I have worked through the DB tutorial and really like the program's overall design. But this issue really has me stumped. Any insight you might provide is most appreciated!

RE: Multitable Insert with Access and DB2000

If you are looking to update records, cross table then you will need to create additional recordsets and queries in the basement for each of the tables that you want to change / update / add.

Once you have done this, reference the new recordsets in your edit/change/add components, (i.e edit box) to the fields that you want to change.

Next, filter the new recordsets with your original query's recordset (i'm guessing the data form wizard created these pages) on the concurrent fields.

Finally, apply the interactions for updating between the individual recordsets, e.g to update USERS.USERNAME you would reference the recordset USERS (made from a new query, e.g select * from USERS) apply the update record interaction to the form button and bob should be your mothers brother.

If this doesn't work, let me know what errors you are getting and perhaps I will be able to help you out.

Simon

RE: Multitable Insert with Access and DB2000

(OP)
Simon, I appreciate your taking the time to reply to my post. Through much trial and error over the since placing my original post on this board, I have resolved the multitable update issue. (I used a hidden field which holds the primary key of Table 1 and then updates/inserts this field as a foreign key in Table 2. This method appears to work although your approach using filters may be more practical....) In any event, I really do appreciate your response. I haven't finished the project yet and new challenges continue to pop up. I may impose upon you for additional advise before it is over <s>.

One other quick question. Did you find the learning curve between DB2000 and UltraDev steep? Did you use DW before making the transition?

Thanks again!

RE: Multitable Insert with Access and DB2000

Hi,

I played with Ultradev for a while, never used dreamweaver really. Ultradev seemed very alien to me, and after I spoke to some guys on another forum they agreed and have also stuck with drumbeat.

I am not too sure about ultradev, esp, the differences between it and drumbeat, but it's on my drive somewhere, so who knows? One rainy day I may get into it.

Sy

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close