Can any help me with the following or point me to some examples of using Dreamweaver and Access to update the data in an Access database which has several related tables?

I want to -

Enter a details in to a table called Video Details - Video ID, video name, video number, video title etc in table_1
And in a second table called Video Location I want Video ID (linked as a one-to-many) video_location_id, location_name
And a third table called Video Category I want Video ID, Category name, category number.

The 3 tables are all linked, so a video can be in more than one category and stored in more than one location.

I am using .asp, dreamweaver and Access. I need some pages in Dreamweaver to do this, I have it all set up in Access, but no luck with dreamweaver so far...

Although you can create with Dreamweaver dynamic pages without writing one line of code this is quite difficult to handle and you have to write many code by hand. So if you do not have any experience in ASP you should maybe re-think of another solution.

When you do not have enough knowledge about ASP, SQL and the MS Access database then I'll sugest to make three different inserted with three different pages (to keep it simple to read and to understand what is happening).

1. Create a page with a standard Insert to Record behaviour;
2. Create a followup page that reads the last inserted Id and insert the rest of the form;
3. Create another followup page that reads again the Id and insert the rest in the form.

Good luck!

<hr><b>DMXZone support manager</b><br><a href="www.kousman.nl">Kousman web resellers</a>

I understood that you need to insert or update to multiple tables (or even do both from a single submit). If that's the case then it is possible, however you'll need to do the code manually. This example inserts into 2 different tables from one page… I used Oracle here but I don't see any reason why you can't do the same in Access. Take a look at the "Section Insert".
I created 2 tables and 2 pages:
Table_1 contains the fields ID1 as integer, TXT1 as text(varchar).
Table_2 contains the fields ID2 as integer, TXT2 as text(varchar).

___________________
Page1(insert.asp)

<pre id=code><font face=courier size=2 id=code>
&lt;%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%&gt;
&lt;!--#include file="../Connections/DBConnection.asp" --&gt;
&lt;%
var MM_editAction = Request.ServerVariables("SCRIPT_NAME";
if (Request.QueryString) {
MM_editAction += "?" + Server.HTMLEncode(Request.QueryString);
}

var MM_abortEdit = false;

var MM_editQuery= " ";
%&gt;


&lt;%
// Begin Section Insert:
// The code to insert into the 2 tables
var myID=Request.Form("ID";
var myTXT=Request.Form("TXT";
if (String(Request("MM_insert") == "form1"
{
var Command1 = Server.CreateObject("ADODB.Command";
Command1.ActiveConnection = MM_DBConnection_STRING;
Command1.CommandText = "INSERT INTO TABLE_1 (ID1, TXT1) VALUES (" + myID + ",'" + myTXT +"' ) ";
Command1.CommandType = 1;
Command1.CommandTimeout = 0;
Command1.Prepared = true;
Command1.Execute();

var Command2 = Server.CreateObject("ADODB.Command";
Command2.ActiveConnection = MM_DBConnection_STRING;
Command2.CommandText = "INSERT INTO TABLE_2 (ID2, TXT2) VALUES (" + myID + ",'" + myTXT +"' ) ";
Command2.CommandType = 1;
Command2.CommandTimeout = 0;
Command2.Prepared = true;
Command2.Execute();

var MM_editRedirectUrl = "display.asp";
if (MM_editRedirectUrl) {Response.Redirect(MM_editRedirectUrl);}
}
// End Section Insert
%&gt;

&lt;html&gt;
&lt;head&gt;
&lt;meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"&gt;
&lt;title&gt;Untitled Document&lt;/title&gt;
&lt;/head&gt;

&lt;body&gt;
&lt;form name="form1" method="post" action="&lt;%=MM_editAction%&gt;"&gt;
&lt;p&gt;ID:
&lt;input name="ID" type="text" id="ID"&gt;
&lt;/p&gt;
&lt;p&gt;TXT:
&lt;input name="TXT" type="text" id="TXT"&gt;
&lt;/p&gt;
&lt;p&gt;
&lt;input type="submit" name="Submit" value="Submit"&gt;
&lt;/p&gt;
&lt;input type="hidden" name="MM_insert" value="form1"&gt;
&lt;/form&gt;
&lt;/body&gt;
&lt;/html&gt;

</font id=code></pre id=code>

______________________
Page 2 (display.asp)

<pre id=code><font face=courier size=2 id=code>
&lt;%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%&gt;
&lt;!--#include file="../Connections/DBConnection.asp" --&gt;
&lt;%
var Recordset1 = Server.CreateObject("ADODB.Recordset";
Recordset1.ActiveConnection = MM_DBConnection_STRING;
Recordset1.Source = "SELECT * FROM TABLE_1 order by ID1";
Recordset1.CursorType = 0;
Recordset1.CursorLocation = 2;
Recordset1.LockType = 1;
Recordset1.Open();
var Recordset1_numRows = 0;
%&gt;
&lt;%
var Recordset2 = Server.CreateObject("ADODB.Recordset";
Recordset2.ActiveConnection = MM_DBConnection_STRING;
Recordset2.Source = "SELECT * FROM TABLE_2 order by ID2";
Recordset2.CursorType = 0;
Recordset2.CursorLocation = 2;
Recordset2.LockType = 1;
Recordset2.Open();
var Recordset2_numRows = 0;
%&gt;
&lt;%
var Repeat1__numRows = -1;
var Repeat1__index = 0;
Recordset1_numRows += Repeat1__numRows;
%&gt;
&lt;%
var Repeat2__numRows = -1;
var Repeat2__index = 0;
Recordset2_numRows += Repeat2__numRows;
%&gt;
&lt;html&gt;
&lt;head&gt;
&lt;meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"&gt;
&lt;title&gt;Untitled Document&lt;/title&gt;
&lt;/head&gt;

&lt;body&gt;
&lt;table border="1"&gt;
&lt;tr&gt;
&lt;td colspan="2"&gt;&lt;strong&gt;Table #1 &lt;/strong&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;ID1&lt;/td&gt;
&lt;td&gt;TXT1&lt;/td&gt;
&lt;/tr&gt;
&lt;% while ((Repeat1__numRows-- != 0) && (!Recordset1.EOF)) { %&gt;
&lt;tr&gt;
&lt;td&gt;&lt;%=(Recordset1.Fields.Item("ID1".Value)%&gt;&lt;/td&gt;
&lt;td&gt;&lt;%=(Recordset1.Fields.Item("TXT1".Value)%&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;%
Repeat1__index++;
Recordset1.MoveNext();
}
%&gt;
&lt;/table&gt;
&lt;p&gt; &lt;/p&gt;
&lt;table border="1"&gt;
&lt;tr&gt;
&lt;td colspan="2"&gt;&lt;strong&gt;Table #2 &lt;/strong&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;ID2&lt;/td&gt;
&lt;td&gt;TXT2&lt;/td&gt;
&lt;/tr&gt;
&lt;% while ((Repeat2__numRows-- != 0) && (!Recordset2.EOF)) { %&gt;
&lt;tr&gt;
&lt;td&gt;&lt;%=(Recordset2.Fields.Item("ID2".Value)%&gt;&lt;/td&gt;
&lt;td&gt;&lt;%=(Recordset2.Fields.Item("TXT2".Value)%&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;%
Repeat2__index++;
Recordset2.MoveNext();
}
%&gt;
&lt;/table&gt;
&lt;p&gt; &lt;/p&gt;
&lt;p&gt;&lt;a href="insert.asp"&gt;Insert More&lt;/a&gt; &lt;/p&gt;
&lt;/body&gt;
&lt;/html&gt;
&lt;%
Recordset1.Close();
%&gt;
&lt;%
Recordset2.Close();
%&gt;

</font id=code></pre id=code>

Regards,
Raafat

Edited by - Raafat on 29 Jul 2005 03:11:51