• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

SQL database accessing methods

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
RustyRazor
Asked:
RustyRazor
  • 3
  • 3
1 Solution
 
petr_hlucinCommented:
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
 
dportasCommented:
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
 
RustyRazorAuthor Commented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
dportasCommented:
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
 
RustyRazorAuthor Commented:
...and the JET provider which uses MS Access syntax is intrinsic to ASP?
0
 
dportasCommented:
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
 
RustyRazorAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now