Solved

SQL database accessing methods

Posted on 2008-10-06
7
227 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

632 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