Solved

Attaching Access Table To Recordset DTC

Posted on 2001-09-10
30
196 Views
Last Modified: 2013-12-24
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)
0
Comment
Question by:slink9
  • 16
  • 10
  • 3
  • +1
30 Comments
 
LVL 15

Expert Comment

by:robbert
ID: 6491769
> 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
 
LVL 23

Author Comment

by:slink9
ID: 6491778
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
 
LVL 5

Accepted Solution

by:
raizon earned 100 total points
ID: 6491786
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
 
LVL 23

Author Comment

by:slink9
ID: 6491833
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
 
LVL 5

Expert Comment

by:raizon
ID: 6493550
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
 
LVL 23

Author Comment

by:slink9
ID: 6493583
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
 
LVL 5

Expert Comment

by:raizon
ID: 6493600
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
 
LVL 23

Author Comment

by:slink9
ID: 6494215
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
 
LVL 15

Expert Comment

by:robbert
ID: 6494663
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
 
LVL 23

Author Comment

by:slink9
ID: 6494674
I finally discovered that one myself.  I am working on it.  Thanks.
0
 
LVL 23

Author Comment

by:slink9
ID: 6494778
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
 
LVL 15

Expert Comment

by:robbert
ID: 6494823
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
 
LVL 23

Author Comment

by:slink9
ID: 6496139
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
 
LVL 5

Expert Comment

by:raizon
ID: 6496790
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
 
LVL 23

Author Comment

by:slink9
ID: 6496825
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 5

Expert Comment

by:raizon
ID: 6496856
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
 
LVL 23

Author Comment

by:slink9
ID: 6497534
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
 
LVL 5

Expert Comment

by:raizon
ID: 6497696
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
 
LVL 23

Author Comment

by:slink9
ID: 6497730
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
 
LVL 5

Expert Comment

by:raizon
ID: 6497771
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
 
LVL 23

Author Comment

by:slink9
ID: 6497821
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
 
LVL 23

Author Comment

by:slink9
ID: 6499731
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
 
LVL 5

Expert Comment

by:raizon
ID: 6499794
do you have frontpage server extensions installed on the server?


What are the steps you are following to publish it to the server?
0
 
LVL 23

Author Comment

by:slink9
ID: 6499847
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
 
LVL 5

Expert Comment

by:raizon
ID: 6500391
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
 
LVL 23

Author Comment

by:slink9
ID: 6500550
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
 
LVL 23

Author Comment

by:slink9
ID: 6500723
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
 
LVL 5

Expert Comment

by:raizon
ID: 6500808
my pleasure.

Good luck.
0
 
LVL 2

Expert Comment

by:Lunchy
ID: 6500909
Points reduced for split.  slink9, I think you will do the rest?
0
 
LVL 23

Author Comment

by:slink9
ID: 6500951
Thanks for the help.  Robbert, look for your points in the VI topic area.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Turn A Profile Picture Into A Cartoon Using Photoshop And Illustrator This tutorial will teach you how to make a cartoon style image out of a regular picture. I have tried to keep the tutorial as simple as possible. I used Adobe CS4 for this tuto…
When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
The purpose of this video is to demonstrate how to manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be IPage.com Log into your Hosting account. IPage will be used for demonstration : Locat…
The purpose of this video is to demonstrate how to integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now