Solved

SQL database accessing methods

Posted on 2008-10-06
7
220 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PowerPivot (or MS Access ) -- Opening & Closing Balance 21 58
Fields don't match on a query 9 45
Create a varying recordset 5 54
relocating SQL 2000 18 34
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

734 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