using .execute to run SQL in a recordset 1

[pyroskliq]

I am retrieving a recordset that contains one field and between 10 and 20 records depending on criteria. The field that I'm retrieving is an SQL statement. I'm trying to loop through the records and execute each statement, which works if there are no variables in the statement.

Here's where I run into the problem. If I write out the statement it works:

AccDB.Execute "SELECT * INTO [Excel 8.0;DATABASE=" & batfolder & "\" & batdate & "_claims.xls].[FutureClaims] FROM [dbo_ViwClaims]"

If I substitute a field from my recordset for the statement it doesn't work:

AccDB.Execute SQLRs!FLDRptSql

I've discovered that if I take out the variables (batfolder and batdate) and code in a file system path, it will work. When I debug it, SQLrs!FLDRptSql has the value of the field in the record, but the 2 variables are not being substituted. The actual contents of the field in my database is:

"SELECT * INTO [Excel 8.0;DATABASE=c:\" & batdate & "_claims.xls].[FutureClaims] FROM [dbo_ViwClaims]"

Frome everything I can gather, it seems to either have something to do with the quotation marks and escaping at the right place, or maybe I'm just trying to do something that can't be done.

Thanks for any help.

Brian

 

[Schroeder]

If I understand the situation correctly, can you use the Replace function to, in a sense, give those variables the values you want them to have?

Save this as FLDRptSql

SELECT * INTO [Excel 8.0;DATABASE=c:\batdate_claims.xls].[FutureClaims] FROM [dbo_ViwClaims]"

Then say

AccDB.Execute Replace(SQLRs!FLDRptSql, "batdate", batdate)

 

[pyroskliq]

Thats exactly what I was looking for. I've used replace hundreds of times, but for some reason it just wasn't clicking to use it here.

Thanks so much.