Solved

SQL database accessing methods

Posted on 2008-10-06
7
182 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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 250 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

762 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

18 Experts available now in Live!

Get 1:1 Help Now