Member Avatar for Pluribus

Hi everyone

I was wondering if it was possible to update two tables in an access database with the same data input from a single form without doing anything to the actual database itself ie linking tables etc?

I have asked this in other forums but have been shouted down in every one saying its not the right way to do it, your database is obviously so badly written it will be unuseable etc etc.

My answer to that is this: I don't care, I want to learn how to do it (if its possible) anyway and my website works fine with the database written just the way it is thank you very much!

This is not a "real" website, it is just a few pages I have written to enable me to learn asp and for that reason, its constantly evolving. It is because of this evolution that I want to store data in 2 tables. Can someone help?

Here is the code I am using to process the form data into Table1, I would like it to also process it into Table2 if that's possible. I have already set up Table2 to have the same fieldnames as Table1.

<%
Dim adoCon          
Dim rsAddComments       
Dim strSQL          

Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database.mdb")

Set rsAddComments = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT Table1.Title, Table1.Details FROM Table1;"

rsAddComments.CursorType = 2

rsAddComments.LockType = 3

rsAddComments.Open strSQL, adoCon

rsAddComments.AddNew

rsAddComments.Fields("Title") = Request.Form("title")
rsAddComments.Fields("Details") = Request.Form("details")

rsAddComments.Update

rsAddComments.Close
Set rsAddComments = Nothing
Set adoCon = Nothing

Response.Redirect "success.html"
%>

I really appreciate any help you can give but, and I don't mean to sound rude by my next statement, if all you are going to say is "that's not how you should use a database", "you don't know what you're doing" etc, please don't reply.

If its impossible, I will accept that, but if its not, even if its a bad way of doing things, please let me know how!

Many thanks

Ian

  • 2 Contributors
  • 2 Replies
  • 480 Views
  • 43 Minutes Discussion Span
  • Latest Post Latest Post by Pluribus
Member Avatar for JorgeM

I would say that if you take a slightly different approach, you can easily update two different tables. While the connection is open just execute the Update SQL statement. For example,

 adoCon.Execute sqlUpdate_Statement.

If you create two variables to store each update then use that variable on the Execute. Run that twice, once for each statement before you close the connection.

I have an example that may help you.
SQL Update - ADO

Edited by JorgeM
Member Avatar for Pluribus

Thank you Jorge, I appreciate the assistance. I will check out the link and give the update statement a try.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.