Hello everyone, im creating a website, in which the site links to a database (Microsoft Access) using ASP.
When i need to update a table in the database i have to upload the entire database. Is there anyway i can just update a table.
Should i use a multple database eg. if i have three tables, should i have three database for each table? - i know this is totally unefficent and probably stupied but i don't hav the money to buy "lovely" MS SQL or coldfusion, and my host doesn't suppport MySQL but supports nearly everything else i.e. coldfusion, ASP, MS SQL
Nope single database, multiple tables works fine in all SQL databases.
If you are loading a table then you are doing something odd with your SQL.
To update you should be using either a single recordset or iterate through a recordset if there is no common way of identifying the rows to be updated.
The nature of JET means that you will always tend to read many records to find the ones required, you can reduce the amount of reading my careful selection of indexing criteria - ie if you know you are going to use a column as a means of updating then index it, but don't be tempted to index everything!. EG if you are going to use date as a selection criteria (in a report, form or query) then it is reasonable to index it, but there would be little point in indexing say and address column.
standard SQL update is something similar to:-
set <column1> = <value1>
<column2> = <value2>
set OrderDate=now(),CustName="Haresh Kanith"
Where OrderNo = 99980;
..this will update all orderno with a value of 99980
..this will set a flag IsShipped for all orders with a date of 25 Nov 2004 the # symbol forces the SQL engine to handle it as a date.
Final point though is this a question for the Access thread or the ASP thread?