Calling Macros in ASP

[artsapimp]

Is it possible to call a macro you have saved in an access database directly through ASP code? If not, how would I embed the macro into the code so it works?

Thanks for any help.

[mosquito]

yes it is possible. You can do it using "office automation". You have to create an msaccess instance.

    Dim msa 
        
    Set msa = CreateObject("Access.Application.8")
    
    msa.OpenCurrentDatabase "database.mdb"
    msa.DoCmd.RunMacro "macroname"
    msa.Quit

thats it.

[artsapimp]

Thanks. That is the easiest thing I've ever seen. I am going to use that a lot. Thanks again.

[artsapimp]

Can you do the same thing with calling a saved query?

[UncaDanno]

Pretty much.
Just use DoCmd.OpenQuery "queryname"

[artsapimp]

I would love to use those commands in this situation because I am lost. I have created a form which feeds into an access db. Once it's written to the database I redirect to a view.asp page which pulls the information out of the db and formats it in a print friendly format. I am stuck with this error...

error '80004005' 
Unspecified error 

/nhd/view1.asp, line 18

That is what I'm getting when trying to run a query from a database. The query runs fine in Access, I just import it out to ASP and call it my strSQL and now it's giving me this error. Here is my strSQL, please tell me what I'm doing wrong. Thanks.

strSQL = "SELECT Evaluations.RecordID, Evaluator.Evaluator, Member.Member, Title.Title, Evaluations.Evaluation_Date, Evaluations.Greeting, Evaluations.Verification, Evaluations.Probing, Evaluations.Control, Evaluations.Language, Evaluations.Courtesy, Evaluations.Accuracy, Evaluations.Problem, Evaluations.Transition, Evaluations.Retention, Evaluations.Ethics, Evaluations.Explain, Evaluations.Score"
strSQL = strSQL & " FROM Title RIGHT JOIN (Member RIGHT JOIN (Evaluator RIGHT JOIN Evaluations ON Evaluator.EvaluatorID = Evaluations.Evaluator) ON Member.MemberID = Evaluations.Member) ON Title.TitleID = Evaluations.Title"
strSQL = strSQL & " WHERE Evaluations.RecordID = " & Request.QueryString("Record")

I would use the codes shown in the previous posts, but I don't know how to make the query saved in access only pull the recordset for Request.QueryString("Record").

Thanks for any help.

[mosquito]

What are you using to run this query. ADO, DAO?

If you are using ADO, Your code should look something like this. Just make sure that "Evaluations.RecordID" is a numeric field (otherwise you have to fix quoting).

dim strSQL
dim cnn
dim cm
dim rs

set cnn = createobject("ADODB.connection")

cnn.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data\db.mdb"
cnn.open

if cnn.state = 1 then
    strSQL = "SELECT Evaluations.RecordID, Evaluator.Evaluator, Member.Member, Title.Title, "
    strSQL = strSQL & "Evaluations.Evaluation_Date, Evaluations.Greeting, Evaluations.Verification, Evaluations.Probing, "
    strSQL = strSQL & "Evaluations.Control, Evaluations.Language, Evaluations.Courtesy, Evaluations.Accuracy, "
    strSQL = strSQL & "Evaluations.Problem, Evaluations.Transition, Evaluations.Retention, Evaluations.Ethics, "
    strSQL = strSQL & "Evaluations.Explain, Evaluations.Score "
    strSQL = strSQL & "FROM Title "
    strSQL = strSQL & "RIGHT JOIN (Member "
    strSQL = strSQL & "  RIGHT JOIN (Evaluator "
    strSQL = strSQL & "    RIGHT JOIN Evaluations "
    strSQL = strSQL & "    ON Evaluator.EvaluatorID = Evaluations.Evaluator) "
    strSQL = strSQL & "  ON Member.MemberID = Evaluations.Member) "
    strSQL = strSQL & "ON Title.TitleID = Evaluations.Title "
    strSQL = strSQL & "WHERE Evaluations.RecordID = " & Request.QueryString("Record")

    set cm = createobject("ADODB.command")
    cm.commandtext = strSQL
    cm.commandtype = 1 'plain text
    
    set rs = createobject("ADODB.Recordset")
    set rs = cm.execute
    
    do while not rs.eof
        response.write("Data: " & rs.fields("RecordID").value & rs.fields("Evaluator").value  & vbcrlf) 
        rs.movenext
    loop
    
    rs.close
    set rs = nothing
    set cm = nothing
    conn.close
end if

set conn = nothing

If this does not help you, you have to post more info...

regards

[artsapimp]

I copied and pasted the code and it looks pretty simple to manipulate. I changed the connectionstring path to the correct location of my db and I got the following error. I see where it looks like it's opening, but it must not be happening...

Again I have never used the format you are showing so excuse me if I sound ignorant. Thanks.

ADODB.Command error '800a0e7d' 

The application requested an operation on an object with a reference to a closed or invalid Connection object. 

?

[mosquito]

Sorry, I forgot something, change the

    set cm = createobject("ADODB.command")
    cm.commandtext = strSQL

part to

    set cm = createobject("ADODB.command")
    cm.activeconnection = cnn
    cm.commandtext = strSQL

sorry.

[artsapimp]

Thank you very much for the response. It kind of makes me feel a little better about my coding now because I got the same error...

error '80004005'
Unspecified error

?

I have also posted this at http://www.vbforums.com and noone there has come up with anything either. I have posted it as a zip file there if anyone wants to accept this challenge.

Thanks again for the help.

[mosquito]

Have you tried this code on another machine. It looks more like a problem with MDAC. I suggest that you re-install Microsoft Data Access components and try again.

http://www.microsoft.com/data/

[artsapimp]

There is another person trying it today on their server. I will look into the update though. Thanks.

[UncaDanno]

For what it's worth, error 80004005 is usually tossed back because of a permissions conflict.
Just for grins, try putting a copy of the .mdb in a subfolder where your site is physically located.
Also, since you are trying to run a macro, you may need to go into IIS mangler and tell it to allow scripts AND executables.

[artsapimp]

The macro is on a different application. I will try to put it somewhere else and see what happens.

[artsapimp]

Same thing. Thanks for trying though.