Running Access Queries with ASP

[bitseek]

Can I trigger an Access Query with ASP? Instead of writing out the query in ASP, could asp run a query which is in Access already?

Could someone help with some code showing how...

Any help will be much appreciated...

Thanks

 

[swilliams]

If you use con.Execute you can execute your built SQL statement in your ASP page OR you can execute a stored query, by passing the query name as the first parameter, instead of the SQL statement.

Simon

 

[bitseek]

This is the ASP version that works:

<%
Dim Conn, RS
Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.open &quot;pp2&quot;

Dim strSQL

strSQL = &quot;DELETE * FROM Invoices;&quot;
Set RS = Conn.Execute(strSQL)
strSQL = &quot;INSERT INTO Invoices SELECT [XLink].* &quot;
strSQL = strSql&&quot;FROM [XLink] &quot;
strSQL = strSql&&quot;WHERE [XLink].[doc type]='I';&quot;
Set RS = Conn.Execute(strSQL)
strSQL = &quot;DELETE * FROM Stox;&quot;
Set RS = Conn.Execute(strSQL)
strSQL = &quot;INSERT INTO Stox SELECT [Invoices].* &quot;
strSQL = strSql&&quot;FROM [Invoices] &quot;
strSQL = strSql&&quot;WHERE [Invoices].[cust number]=222;&quot;
Set RS = Conn.Execute(strSQL)
set rs = nothing
conn.Close
set conn = nothing

%>

Now i would like to do the same with only calling the saved queries in Access, like you state above in your reply, therefore I assume the following would be the code:

<%
Dim Conn, RS
Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.open &quot;pp2&quot;

Dim strSQL

strSQL = &quot;DelInvoices&quot; 'name of query in Access
Set RS = Conn.Execute(strSQL)
strSQL = &quot;UpdInvoices&quot; 'name of query in Access
Set RS = Conn.Execute(strSQL)
strSQL = &quot;DelStox&quot; 'name of query in Access
Set RS = Conn.Execute(strSQL)
strSQL = &quot;UpdStox&quot; 'name of query in Access
Set RS = Conn.Execute(strSQL)
set rs = nothing
conn.Close
set conn = nothing

%>

This second code did not work, instead i got the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.


Help!!! What am i doing wrong?

 

[bitseek]

Sorry, I meant to say i got the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.


Thanks if you can help me!

 

[culshaja]

I would use a Command object.
Try:

set cmDelete = Server.CreateObject("ADODB.Command")
set cmDelete.ActiveConnection = Conn
cmDelete.CommandType = 4
cmDelete.CommandText = strSQL
cmDelete.Execute

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305