Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old August 23rd, 2004, 12:23 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: Cochin, Kerala, India.
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp
Default stored procedures and MS Access

I wonder whether I can use stored procedures with MS-Access 2000. Even if it is possible, whether it is possible to import all the data and stored procedures to SQL server 2000 directly ?

The situation is : I am to start develop a web site which is expected to have some big volume of data. But they do not want to use SQL server initially. But, if the site is successful, they want to move it completely to SQL server.

The porting from access to SQL server will take a long time when I don't use stored procedures. If I don't use stored procedures, the performance of SQL server will be affected.

I am confused. May I get a good advice as to how I should proceed ?
Reply With Quote
  #2 (permalink)  
Old August 23rd, 2004, 07:47 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Access doesn't use stored procedures, but you can do hard-coded parameterized queries. Access supports queries, although never used them in code. You can copy the data to SQL.

If you are talking a large volume of data, use SQL initially. Access will run very slow with a lot of data.

Brian
Reply With Quote
  #3 (permalink)  
Old August 25th, 2004, 03:16 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: Cochin, Kerala, India.
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp
Default

Hello Brain,

Thanks for the response. I have explored access and found out many useful features.

1) Access can have simple stored procedures like listing records, inserting / updating / deleting records etc. Eventhough, I could not get the list of stored procedures, they are getting created. I just ran a create procedure query. I am able to run that stored procedure.

2) The access queries can be used in the same way as tables if they don't use any parameters. However, I could not run a query with a parameter through ASP. But for such cases, I can use stored procedures.

3) I think we can use queries and stored procedures in access also. But complex stored procedures cannot be created. I am going to make this as a standard. Whether we are using Access or SQL server, we should use stored procedures. Then the conversion from Access to SQL server will be a lot more easy. After conversion, we can define indexes and more complex stored procedures in SQL server to make it more efficient.

I will post some sample codes also.
Reply With Quote
  #4 (permalink)  
Old August 25th, 2004, 03:23 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: Cochin, Kerala, India.
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp
Default

Here is the sample code. Maybe useful for some other persons.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
    <title>Stored procedures and queries in Access 2000</title>
</head>

<body>

<%
    dim cnn_test
    Set cnn_test=Server.CreateObject("ADODB.Connection")
    cnn_test.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0; DATA SOURCE=" & Server.MapPath(".") & "\Database.mdb"

    dim rst_test
    '----------------------------------------------------Plain query
    dim cmd_test
    Set cmd_test=Server.CreateObject("ADODB.Command")
    Set cmd_test.ActiveConnection=cnn_test
    cmd_test.CommandType=adCmdStoredProc
    cmd_test.CommandText="<query_with_no_parameter>"
    set rst_test=cmd_test.Execute()
    if(not(rst_test.Eof or rst_test.Bof)) then
        rst_test.MoveFirst
        while(not(rst_test.Eof))
            Response.Write(rst_test("field1") & " ---- ")
            Response.Write(rst_test("field2") & " ---- ")
            Response.Write(rst_test("field3") & " ---- ")
            Response.Write("<br><br>")
            rst_test.MoveNext
        wend
    else
        Response.Write("There are no records to show<br><br>")
    end if
    rst_test.Close
    Set rst_test=nothing
    set cmd_test=nothing

    '---------------------------------------------------Parametrized query
    ' this did not work
    'Set cmd_test=Server.CreateObject("ADODB.Command")
    'Set cmd_test.ActiveConnection=cnn_test
    'cmd_test.CommandType=adCmdStoredProc
    'cmd_test.CommandText="<query with parameter>"
    'cmd_test.Parameters.Append cmd_test.CreateParameter("ClientName", adVarchar, adParamInput, 1)
    'cmd_test.Parameters("ClientName")="A"
    'set rst_test=cmd_test.Execute()
    'if(not(rst_test.Eof or rst_test.Bof)) then
    ' rst_test.MoveFirst
    ' while(not(rst_test.Eof))
    ' Response.Write(rst_test("Field1") & " ---- ")
    ' Response.Write(rst_test("Field2") & " ---- ")
    ' Response.Write(rst_test("Field3") & " ---- ")
    ' Response.Write("<br><br>")
    ' rst_test.MoveNext
    ' wend
    'else
    ' Response.Write("There are no records to show<br><br>")
    'end if
    'rst_test.Close
    'Set rst_test=nothing
    'set cmd_test=nothing

    'drop a procedure . Comment this initially.
    cnn_test.Execute "DROP PROCEDURE proc_test"

    ' create a procedure
    cnn_test.Execute "CREATE PROCEDURE proc_test(@str_firstname_part VARCHAR(40)) AS SELECT * FROM [staff] WHERE [firstname] LIKE @str_firstname_part + '%'"

    Set cmd_test=Server.CreateObject("ADODB.Command")
    Set cmd_test.ActiveConnection=cnn_test
    cmd_test.CommandType=adCmdStoredProc
    cmd_test.CommandText="proc_test"
    cmd_test.Parameters.Append cmd_test.CreateParameter("str_firstname_part", adVarchar, adParamInput, 1)
    cmd_test.Parameters("str_firstname_part")="a"
    set rst_test=cmd_test.Execute()
    if(not(rst_test.Eof or rst_test.Bof)) then
        rst_test.MoveFirst
        while(not(rst_test.Eof))
            Response.Write(rst_test("UserName") & " ---- ")
            Response.Write(rst_test("firstname") & " ---- ")
            Response.Write(rst_test("surname") & " ---- ")
            Response.Write("<br><br>")
            rst_test.MoveNext
        wend
    else
        Response.Write("There are no records to show<br><br>")
    end if
    rst_test.Close
    Set rst_test=nothing
    set cmd_test=nothing
%>


</body>
</html>
Reply With Quote
  #5 (permalink)  
Old August 25th, 2004, 07:00 PM
Friend of Wrox
Points: 6,468, Level: 34
Points: 6,468, Level: 34 Points: 6,468, Level: 34 Points: 6,468, Level: 34
Activity: 10%
Activity: 10% Activity: 10% Activity: 10%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,835
Thanks: 12
Thanked 16 Times in 16 Posts
Send a message via AIM to mat41
Default

Wow, after reading this post I done some research. I had no idea these stored procs have been available in Access 'since 2000'

Thanks for bringing this up!
BTW: I didnt believe it till I saw it, there are limitations but they do work.

Wind is your friend
Matt
Reply With Quote
  #6 (permalink)  
Old August 26th, 2004, 12:22 AM
Friend of Wrox
 
Join Date: Jul 2004
Location: , , India.
Posts: 345
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to gokul_blr Send a message via Yahoo to gokul_blr
Default

Thanks, for information about the mdb performance and the Procedure sample codes.


Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Stored Procedures returning no results to access 50bmg_de SQL Server 2000 5 February 21st, 2006 03:58 AM
RecordSets vs Stored Procedures in Access Roy0 SQL Language 0 December 28th, 2005 01:02 PM
asp and ms sql stored procedures solomon_13000 Classic ASP Basics 1 July 11th, 2005 12:04 AM
Converting Access Queries Into Stored Procedures markw SQL Language 1 March 15th, 2005 10:49 AM
Access, SQL Server - Stored Procedures / DTS tcarnahan Access 5 September 1st, 2004 05:17 AM



All times are GMT -4. The time now is 11:56 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.