run function that is specified in a variable

[PiB311]

Hello all,

Hope someone can help me. I created a table with different procedures that
run during a nightly update process. I wanted to be able to skip one part if
something failed.

To do this, I created a table that specifies a process number and the script
that is run to complete that process.

I need to be able to loop through records and run specific scripts that
should be completed. When I try the call method of the variable, I get
"Compile error: expected: . or (".

Here is my code.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim str_sql As String


Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * " & _
"FROM tbl_processes " & _
"INNER JOIN tbl_scripts " & _
"WHERE tbl_processes.task_id = " & int_task &
"")

rst.MoveFirst

Do Until rst.EOF

str_sql = "INSERT INTO tbl_process_log ( process_id , status_id ,
error_message , error_action ) " & _
"SELECT " & rst!process_id & ", 1, '','' "

DoCmd.SetWarnings False
DoCmd.RunSQL str_sql
DoCmd.SetWarnings True

call rst!script

Please assist. Any help is appreciated!

 

[John Spencer MVP]

Set rst = db.OpenRecordset("SELECT * " & _
"FROM tbl_processes " & _
"INNER JOIN tbl_scripts " & _
"WHERE tbl_processes.task_id = " & int_task & "")
That does not compute. You have a JOIN with no ON clause.

Use alternative insert syntax for this.

str_sql = "INSERT INTO tbl_process_log ( process_id , status_id ,
error_message , error_action ) " & _
"Values(" & rst!process_id & ", 1, '','' )"

This could fail if error_Message and Error_action are not set up to accept
zero-lenght strings. In that case try null or not inserting anything into
those fields
str_sql = "INSERT INTO tbl_process_log ( process_id , status_id ) " & _
"Values(" & rst!process_id & ", 1 )"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[PiB311]

John,

Thanks for your help. For some reason, the whole Select statement did not
come through.

I used the Eval function to run the prodedure stored in my variable. This
has so far produced the results I wanted. This is depicted below:

str_sql = Eval(rst!script)

Where rst!script is created in a recordset and holds the procedure that is
to be run.

Is this the way you would accomplish this? Please advise.

Again thanks for any help.

 

[John Spencer MVP]

If it works then it works.

I'm not sure what you are doing so I don't know if I would do it that way or not.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County