Setting Access database on website

[sunnywink]

Hi all,

I would like to create an ASP which will set an Access database onto the Web, where users can enter and add on records to the Access form.

I am new to asp and SQL language and like to confirm if the following steps are correct to open the database on the website.

- Open Notepad or other text editor
- Key in following commands:
<% Open &quot;name of DSN&quot; %>
- Save file under .asp extention

Are these steps sufficient? Will the queries set on the database work automatically or do I set further SQL statements to call up the queries? Will Access reports work too online?

Many thanks in advance.

 

[danvlas]

In this case, the Access file will be a back-end database.
In ASP you will have to set the connection:
<%
Set conn = server.createobject(&quot;adodb.connection&quot
myDSN=&quot;DRIVER={Microsoft Access Driver (*.mdb)}; &quot;
myDSN=myDSN & &quot;DBQ=&quot; & server.mappath(&quot;VirtualFolder\Databasename.mdb&quot
conn.Open myDSN
%>

The queries in the database will NOT work (afaik). You will have to re-define them as SQL statements in ASP:

Set RS = Conn.Execute(&quot;Select * from TableName ORDER BY DateField DESC&quot

Then you have to display the results on the page:

A. Table header (field names)
<table>
<tr>
<%
For Each fld in rst.Fields
Response.Write &quot;<td>&quot; & fld.Name & &quot;</td>&quot;
Next
%>
</tr>

B. Records (field values in each row)

<%
While Not rst.EOF
Response.Write &quot;<tr>&quot;
For Each fld in rst.Fields
Response.Write &quot;<td>&quot; & fld.Value & &quot;</td>&quot;
Next
Response.Write &quot;</tr>&quot;
rst.MoveNext
Wend
%>
</table>

Then release memory:
<%
Set rst = Nothing
Set conn = Nothing
%>

All actions are done on the IIS server, not in the Access environmnent. Reports...you will have to define their html version and build them as described above.

HTH

Dan


Daniel Vlas
Systems Consultant

 

[sunnywink]

Thanks. 4 more questions.

1. Will the users be able to update records on the web database with the above steps? Do I have to add more SQL statements?

2. Will the server end database reflect the latest updates and be linked to the website database automaticallly?

3. Besides asp and IIS, are there other methods to put up the Access database on the web?

4. Do you know of websites where i can know more of this topic?

 

[danvlas]

1. Users can view, add, edit and delete records all right.
2. The back end database is the same as the web database, so the changes are reflected immediately.
3. Access Data Pages (require users to have Access installed), PHP with some extra drivers installed.
4. The ASP forum on this site:
forum333 luck

Daniel Vlas
Systems Consultant

 

[sunnywink]

Thanks for the help! I will take a look at the forum

 

[sunnywink]

Hi

1 more question, don't mind..

On Acccess database on the web, once the above is implemented, how will the queries be called up. Is it similar to that in Access environment?

i.e by going to the query window and clicking OPEN option or double clicking on the query name.

Do I need to repeat the steps you mention above for every query I set? Pls refer below:

A. Table header (field names)
<table>
<tr>
<%
For Each fld in rst.Fields
Response.Write &quot;<td>&quot; & fld.Name & &quot;</td>&quot;
Next
%>
</tr>

B. Records (field values in each row)

<%
While Not rst.EOF
Response.Write &quot;<tr>&quot;
For Each fld in rst.Fields
Response.Write &quot;<td>&quot; & fld.Value & &quot;</td>&quot;
Next
Response.Write &quot;</tr>&quot;
rst.MoveNext
Wend
%>
</table>

Then release memory:
<%
Set rst = Nothing
Set conn = Nothing
%>

May I know what is the meaning of rst? It is a name given to refer to the query? Is it different for every query?

Thanks.