Moving application from Access to SQL

[chapm4]

Hi,

I am fairly new to SQL and ASP, but an old client of mine(I do websites in PHP and mySQL) asked me to switch an application with an Access database and ASP front-end to SQL. He puchased SQL Server 2005. I have moved the database from Access to SQL with no trouble. Now I am looking at the ASP and really don't understand ASP but believe that I can just change the connection strings in the files and that is all that will be required. Can someone do some handholding and help me?

Here is the connection information as it stands:

set cn=server.createobject("ADODB.connection")
cn.open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="&server.mapPath("/_database/Database.mdb")
set r=server.createobject("adodb.recordset")
sql="select * from CommentCards"
total=0
r.open sql,cn
do until r.EOF
total=total+1
r.movenext
loop
r.close
cn.close

 

[gmmastros]

check out...

http://www.connectionstrings.com

There, you will learn how to build connection strings.

Also, you will need to test the entire application because not all queries are structured the same way and some will likely fail.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

set cn=server.createobject("ADODB.connection")
cn.open "Provider=SQLNCLI;Server=XXXXXXXX;Database=DATABASE;UID=USERNAME;PWD=PASSWORD;"
....

for more info check

http://www.connectionstrings.com/

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLSister]

You may also need to change some or even many of the queries as T_SQL and Access SQL are not the same. For instance how they refer to dates is different.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[chapm4]

Thanks guy's I really appreciate the help. I will go get busy now. I am sure I will have some questions later on, but this will get me started.

 

[chapm4]

And now let the confusion and questions begin:

The old app was running on a hosting service...again the Access database and the ASP files were in the same directory on the hosting service. The user went to the index page, it pulled user names from an access table, the user clicked one of those users, entered the password, if that password matched what was in the table, that user was granted access to the rest of the asp pages which were mainly reports and such.

Now I have installed SQL Server 2005 Standard Edition, created a database, upsized the old database from Access to SQL and now I want to edit the ASP files to hit this database and do the same functionality. Don't think it is a difficult process as the database isn't that complicated, but I am a bit lost here.

The client I am doing this for will install SQL server on their machine and I will need to move the database and ASP files I have done to that machine. The data entry people will be in that office, and the people who view the reports will be on another network and connecting into this.

So question number one is: Can someone break this process down for me in steps that I need to accomplish?

Question 2: Can you point me toward learnings to accomplish these steps?

Question 3: I am running on my machine xp pro. Can't I run these ASP files on my machine or do I have to do something so they will run?

Question 4: Will I be able to do the setup on the clients machine remotely or be there in person?

This is enough for now. Thanks for the help.

 

[SQLSister]

First, you do NOT want to run the ASP and the SQL Server on the same machine. SQL server should be on a machine that has nothing else at all running on it. SQl server wants all the memory of the machine you are running if at all possible. It doesn't usually play well with others.

As for how to do what you want from ASP, the ASP forum might be a better place to ask this question.

Personally I would do the setup in person if at all possible. I'm sure given the proper rights and the proper credetials to pass through the firewall that it could be done remotely, but it may take more time and effort.

to accomplish the password validation task, you will need the user to have rights to the database. What we often do is create a user account for the web user and then do as you did and have look in the password table and find out if the person matches and then have a table which grants various page rights based on that. You would need another table to specify which pages the person who has passing credentials can see and your ASP code would have to account for that. Alternatively, if all the users will be employees of the company, simply grant them SQL server accounts and put them in a group and grant the rights you need to that group. This will then use their windows login to get them into the system. You will need to read up in Bookd online about database rights, this works somewhat differnetly than access did. This is informaion you need to understand in detail, so read up on it.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief