Attaching Access Table To Recordset DTC

I have a web page set up and want to attach an Access table and get it set up working locally and then want to publish it and have it work for the world.  I have accomplished attaching to a database once but that was only playing and learning on my server.  What is the best route to take to accomplish this? (150 pts)
Along the same lines, I will need to use variables in a SQL statement to only display the requested data.  Do I do this using the ? and the variable tab in the recordset with an Access table? (additional 50 pts either separately or added to this)
LVL 23
slink9Asked:
Who is Participating?
 
raizonConnect With a Mentor Commented:
First you need to establish a connection to your database.

<%
Set con = Server.CreateObject("ADODB.CONNECTION")
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("relative path to your .mdb like /myDatabase.mdb)")
%>

Then once you have that established you can execute your various SQL statments.  To perform a query against a database you will want to create a record set.  There are various ways of doing this.  My preference is something along the line of

<%
strSQL = "SELECT * FROM Table where TableID = " & intID
Set myRst = con.Execute(strSQL)
%>

To execute a DELETE, INSERT, or UPDATE then you wouldn't need to create a recordset and could do it like

<%
strSQL = "INSERT INTO Table (field1, field2) VALUES ("
strSQL = strSQL & "'" & strField1 & "', "
strSQL = strSQL & "'" & strField2 & "'"

con.Execute(strSQL)
%>

in the sql statments I also show how you would use variables to query against.  

0
 
robbertCommented:
> What is the best route to take to accomplish this?

Do not grade this -- but the way that hurts the less is still not using DTCs (but learning at www.asp101.com), and FTPing instead of InterDev-publishing. -- Use a DSN-less connection with Server.MapPath().
The same for dynamically assigning an SQL statement (WHERE clause) to a recordset.

I'm sorry but in my eyes you've been cheated; InterDev makes starting easy but not maintaining. It will become worse with .net WebForms.
0
 
slink9Author Commented:
I am beginning to believe that myself.  There is so much coding to do setting up a data connection, though.  I am reading through Active Server Pages 3 and the author is using VI to create the data-connected web pages.  Isn't there an ADO Data Connection or Recordset that I could use instead of DTC?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
slink9Author Commented:
The problem comes in with the relative path.  I played around with the example in the book and finally got it to work.  Now I am trying to create something usable but am running into the problem again that the data is not accessible.  I have the table on my server in the root of the web.  Is there a better place to have it?  How can I point the recordset to it?
0
 
raizonCommented:
you need to map a path to the database in your connection.
By useing Server.MapPath you can do this.

If your db is at the root of the web then set your connection string like so

<%
Set con = Server.CreateObject("ADODB.CONNECTION")
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("/myDatabase.mdb)")
%>


0
 
slink9Author Commented:
The only problem with that is that I am doing this through the VI IDE, not in code.  I have it connected and open and can view the data.  I just can't use it on the web page.  I get 80004005 when going to the page.  I have checked quite a few things from a few MS articles but still can't get it to connect.
Is there a better thing to use other than a recordset DTC?
0
 
raizonCommented:
You need to do it in code.  As Robert suggested I wouldn't use DTC.  There is problems with supporting it once its created.  Use the examples that I've shown to create your ADODB Connection and Recordset.
0
 
slink9Author Commented:
Okay.  I am setting this up in code.  I have the RS set up.  I have the SQL statement set up and working properly as far as I know.
How do I display that info?  I can't use a label DTC, can I?  What do I use to display it?
0
 
robbertCommented:
You would mix HTML and ASP:

<table>
<%
Do While Not objRecordset.EOF
    %>
    <tr>
    <%
    For i = 0 To objRecordset.Fields.Count - 1
        Response.Write "<td>" & objRecordset.Fields(i) & "</td>" & vbCrLf
    Next
    %>
    </tr>
    <%
    objRecordset.MoveNext
Loop
%>
</table>

You can also address fields by, objRecordset("database_column_name")
0
 
slink9Author Commented:
I finally discovered that one myself.  I am working on it.  Thanks.
0
 
slink9Author Commented:
It works well locally (finally) but doesn't work when I deploy it.  It can't find the data file again.  I have it in as /shipstat/filenm to work with it locally and have tried it with that and /filenm on the published version unsuccessfully.  Any suggestions here?
0
 
robbertCommented:
You need to post your code, the error number and description.

Response.Write Server.MapPath("/") to get orientation.

The anonymous user needs Read/Write permissions to the folder the database is in. Make sure that this is the case on the remote server; possibly ask the admins.
0
 
slink9Author Commented:
I believe the problem is that the table hasn't actually been written to the web.  I will FTP the table today when I get to work and see if that helps.
It is nice to finally be making progress on this, though.
0
 
raizonCommented:
if you still get an error after you ftp the db up to the server post the error number, description, and code as robert suggested and we can help you finish this.

Glad to see progress is being made.
0
 
slink9Author Commented:
I ditched VI and put the code in FP98.  All is working well locally but I am having problems getting it published.  I believe that is because of a router configuration.  I will play with that later and hopefully close out this question with a point split.  Thanks for the help.
0
 
raizonCommented:
glad to help.

Don't ditch VI6 quite yet tho.  It is a much better tool then FP98 with better publishing tools.  Just stay away from the Design Time Controls in VI.

0
 
slink9Author Commented:
This is true.  I can't ditch VI because, for some reason, FP will not publish.  I didn't like the inability to publish only changed pages in VI.  This is important because the table will change daily but that will be the only change.  I can't FTP to it because it is set up to be published with FP.
I thought my FP was messed up but I uninstalled it, reinstalled it, and repatched it it to the b ver.  I still can't publish or open the remote web.  Any suggestions as to where to look for that?
0
 
raizonCommented:
when you publish in VI uncheck "Copy Changed Files Only" and it will publish the entire project for you then.

What version of VI are you using?

I would also consider upgrading to FP2000 or FP2002
0
 
slink9Author Commented:
VI6 Professional.  I didn't see an option for changed files only.  I may have to do just that since I am having so many problems with FP.  How can I get VI to put my data table up?
0
 
raizonCommented:
if your database is a part of the VI project then it will publish it as well.  

When you publish it you should see a screen/window that asks you where you want to publish it to (Destination Web Server) and the name of the project (Web Project).

Underneath that you should have 4 options.

Copy Changed files only.
Add to existing web project.
Copy child webs.
Register Server Components.


Make sure that "Add to existing web project" is selected. If not then "Copy Changed files only" will not be an option.
0
 
slink9Author Commented:
I hate these questions that just go on ... and on ... and on ...
I have tried to include it by right-clicking on the root and adding it but I get a message stating that the server connection has been aborted.  Am I adding this properly or is there a better procedure to follow?
0
 
slink9Author Commented:
I have it appearing in the project.  I added a DATABASE directory and put it in there.  It still works locally.  When I try to Deploy (I believe is the proper method) I get UNABLE TO SEND REQUEST TO THE WEB SERVER returned and a failed deployment.
Is there another option to publish the site?  I don't see the things you mentioned.
0
 
raizonCommented:
do you have frontpage server extensions installed on the server?


What are the steps you are following to publish it to the server?
0
 
slink9Author Commented:
Frontpage extensions are on the server as far as I know.  We are using a web host.
I use Project-Deploy-Deploy to ... and it goes.  I have removed the table from the project and it deploys fine.  I wanted to make sure I got the command in there to tell the robots not to index it.
0
 
raizonCommented:
In your Project Explorer You have a series of icons.  The 4th icon which looks like 2 "batch file" icons overlayed is your copy web application button.  When you click on that one you will see the options that I described earlier.

0
 
slink9Author Commented:
I see, said the blind man.  I did that, got the options, and now it tells me that the FP Server extensions are not installed there.  I was talking to the support people who said it is.  I must be putting it in the wrong place on the web.
0
 
slink9Author Commented:
And the problem is solved!!
Our router won't allow me to publish to the web.  I guess that is a good thing since it pretty much proves the "clamped down tightness" of our network.
I pulled my laptop out and connected via modem.  I published with FP just because it is easier and quicker and it works well.  I will ask for a 2/3, 1/3 point split.  Thanks for the help.
0
 
raizonCommented:
my pleasure.

Good luck.
0
 
LunchyCommented:
Points reduced for split.  slink9, I think you will do the rest?
0
 
slink9Author Commented:
Thanks for the help.  Robbert, look for your points in the VI topic area.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.