stored procedures and MS Access

madhukp · August 23rd, 2004, 12:23 AM

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 ?

bmains · August 23rd, 2004, 07:47 AM

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

madhukp · August 25th, 2004, 03:16 AM

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.

madhukp · August 25th, 2004, 03:23 AM

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(" ")
 rst_test.MoveNext
 wend
 else
 Response.Write("There are no records to show ")
 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(" ")
 ' rst_test.MoveNext
 ' wend
 'else
 ' Response.Write("There are no records to show ")
 '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(" ")
 rst_test.MoveNext
 wend
 else
 Response.Write("There are no records to show ")
 end if
 rst_test.Close
 Set rst_test=nothing
 set cmd_test=nothing
%>

</body>
</html>

mat41 · August 25th, 2004, 07:00 PM

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

gokul_blr · August 26th, 2004, 12:22 AM

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