Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL database accessing methods

Posted on 2008-10-06
7
Medium Priority
?
238 Views
Last Modified: 2010-04-21
I'm new to SQL database access and I have a question or two about access through ASP scripts.  Currently, I connect to my database with the following code:

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
DBname = Server.mappath("/data/ABC.mdb")
conn.Open DBname
set rs = Server.CreateObject("ADODB.recordset")

Databases are located in a folder in my web page space.  My understanding is that I am addressing an MS Access backend through ASP, although my host claims to be running SQL Server 2005.  MS Access database manipulation syntax works successfully, while SQL Server syntax does not.

1) My host says I should enter my databases into their SQL server and access them that way, which I  assume would then be subject to SQL Server syntax.  Why should I bother, if this method works?

2) Is MS Access then an intrinsic subset of Classic ASP?
0
Comment
Question by:RustyRazor
  • 3
  • 3
7 Comments
 
LVL 4

Expert Comment

by:petr_hlucin
ID: 22648906
I suggest you not to use MS Access and use MSSQL (or SQLExpress which i a free size limited version of MSSQL). See the code which should establish a connection to SQL server.
More parmeters of the connection string may be found e.g. at http://www.sqlstrings.com/SQL-Server-connection-strings.htm .
SqlConnection conn = new SqlConnection("Data Source=computer_name;Initial Catalog=database_name;Persist Security Info=True;User ID=username;Password=password;Network Library=dbmssocn;");

Open in new window

0
 
LVL 22

Expert Comment

by:dportas
ID: 22649014
You are connecting to an Access DB, not SQL Server.

1) Security, scalability, high availability, etc plus tons of extra features. All of the usual reasons for using an enterprise DBMS. What are your reasons for using Access for a web app if if SQL Server is available to you anyway?

2) Access has nothing to do with ASP. Access is an application development tool, typically used for desktop application development.
0
 

Author Comment

by:RustyRazor
ID: 22649324
dportas:

Thanks for your reply.

The Access syntax appears to work, which is simply why I use it.  Like I said, I'm new at SQL.  If I log into my host's SQL server, doesn't that limit my ability to use the DDL features of SQL to create a new DB from my ASP scripts like I've done thus far?  They say they would need to create or upload existing DB files for me.  I have no objection to this host, but the less they are involved in my programming efforts, the better.  It is way more convenient for me to work without their involvement.

Please forgive my naivte, but could you describe exactly what is happening with the code snippet I included in my first post to this thread?  As far as I understand, I'm using MS Access syntax through ASP to manipulate an SQL database (similar connect string is used to create the DB from scratch) and I'm not using my host's SQL server.  It is my understanding that creation and alteration of DB structures through the host's SQL server would involve logging into a database interface control panel and using a different connect string and syntax in my ASP scripts.  My host says they would have to do most of that stuff.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 22

Expert Comment

by:dportas
ID: 22649418
set conn=Server.CreateObject("ADODB.Connection")
-- Creates a connection

conn.Provider="Microsoft.Jet.OLEDB.4.0"
-- Sets the provider name (the Jet OLEDB provider )

DBname = Server.mappath("/data/ABC.mdb")
-- Sets the DB name

conn.Open DBname
-- Opens the DB

set rs = Server.CreateObject("ADODB.recordset")
-- Creates a recordset object.

You could do the same with a SQL database - just change the provider string. You can also run DDL commands the same way (assuming you have the ddl_admin role on the server). However, it's pretty unusual to embed DDL commands in application code.

Typically, the database schema is created at design time and then deployed at install time using SQL scripts. There are good reasons for this. Scripts are usually essential to test the deployment and carry out good configuration management practices. The decision about how you deploy and configure should involve whoever is responsible for service levels, eg. for backing up and for availability - it's not just a developer issue.
0
 

Author Comment

by:RustyRazor
ID: 22649469
...and the JET provider which uses MS Access syntax is intrinsic to ASP?
0
 
LVL 22

Accepted Solution

by:
dportas earned 1000 total points
ID: 22649638
Jet has nothing to do with ASP. It's just one of many possible OLEDB providers.

As to your database architecture choice, if you are new to databases and don't know the pros and cons of different DBMS architectures then maybe you should seek some additional advice. Hire a DB developer or architect who can look at your requirements. Jet mdbs are unsuitable for many web apps because it isn't feasible to achieve anything like 24x7 high availability or the kind of backup, recovery and security levels that are often needed.
0
 

Author Closing Comment

by:RustyRazor
ID: 31503357
Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

885 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