creating access reports using dynamic sql 1

[pms18066]

I need to generate a report in access using dynamic sql. The user will be prompted to enter a county name when generating the report. I need to use this county name to drive which database and table I will be using to retrieve the dataset from. the sql version of this query is as follows. note the county is hardcoded in this example. I know how to make the report prompt the user for the county, but I cant get the correct syntax that access likes. btw, these are all sql tables we are using through access. any help is appreciated...

DECLARE @COUNTY VARCHAR(25)
declare @dbase varchar(max)
DECLARE @TBLNAME VARCHAR(40)
DECLARE @sql VARCHAR(MAX)

set @COUNTY = 'RICHLAND'
SET @dbase ='SC_' + @COUNTY + '_SRC'
SET @TBLNAME = 'dbo.' + @COUNTY + '_TRANSLATION'


SET @sql='SELECT *
FROM ' + @dbase +'.'+@TBLNAME

EXECUTE(@sql)

 

[pms18066]

One further step along the patch...
I created a function to do the query and set it equal to a record set. t'aint working yet though.. here is the code..

how do I bind my report to this function??

Public Sub RS_FromSQL()
' Comments:
'

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim COUNTY As String
Dim dbase As String
Dim TBLNAME As String
Dim sql As String

COUNTY = "kershaw"
dbase = "SC_" + COUNTY + "_SRC"
TBLNAME = "dbo." + COUNTY + "_TRANSLATION"

strSQL = "SELECT * FROM " + dbase + "." + TBLNAME

' Open pointer to current database
Set dbs = CurrentDb()

' Create recordset based on SQL
Set rst = dbs.OpenRecordset(strSQL)

rst.Close
dbs.Close

End Sub

 

[dhookom]

I would use a pass-through query as the record source of the report. Then use a little DAO code to change the SQL property of the p-t query:

Public Sub RS_FromSQL()
  ' Comments:
  '
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim COUNTY As String
    Dim dbase As String
    Dim TBLNAME As String
    Dim sql As String
    Dim strQueryName As String
    strQueryName = "qsptYourQueryName"
    COUNTY = "kershaw"
    dbase = "SC_" & COUNTY & "_SRC"
    TBLNAME = "dbo." & COUNTY & "_TRANSLATION"
    strSQL = "SELECT * FROM " & dbase & "." & TBLNAME

  ' Open pointer to current database
    Set dbs = CurrentDb()
    set qdf = dbs.QueryDefs(strQueryName)
  ' update query SQL property
    Set qdf.SQL = strSQL
    Set qdf = Nothing
    Set dbs = Nothing
End Sub

Then just open the report.

Duane
Hook'D on Access
MS Access MVP

 

[pms18066]

ok I did something alittle bit different and still cant get it right. I created a pass through query and called a stored procedure which I created on the sql server. It takes the county code as a parameter. it works fine when I hardcode it in the PTQ, however I need to get it from the user, via a prompt. How do I pass a parameter to a pass through query??

 

[dhookom]

Again, you can use a little DAO code to change the SQL property of your saved pass-through query. My previous post had a few lines of code that did this.

Duane
Hook'D on Access
MS Access MVP

 

[pms18066]

Sorry it took a while to get a post back here. I did get it to work and it runs SOOOOO much faster. Thansk for the helpful code. Once you implement it correctly it is simple. I have done this for over ten reports that took about 2-3 minutes to generate. with the new procedure they all come up in less than 10 seconds. Much improved productivity from the analysts. Thanks again!!