Access Reporting from a VB app

K

Kelly C

I have an app written in VB6 using an MDB as a database. What I want to do
is use access as a report designer and report engine however I'm having
difficulties with it.

Basically I'm launching reports with the following code:

Code:
Dim acApp         As Access.Application

acApp.OpenCurrentDatabase strDB_PATH
Call acApp.DoCmd.OpenReport(strReportName, vbViewPreview)
acApp.DoCmd.Maximize
acApp.Visible = True

Now this works although there are a few issues. Issue #1, It just runs the
query that the report is linked to. you can pass in a query name if you like
but it does not handle paramaters (which 99% of my query's have).

So as a work around ... I use this code to overwrite the query text each
time.

Code:
strSQL = "SELECT blah blah blah ... WHERE blah='%s'"
strSQL = Replace(strSQL, "%s", intParamID)

strDataPath = gConfig.ReportDBPath

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = OpenDatabase(strDataPath)
Set qd = db.QueryDefs("QueryName")

Now some of you might be shaking your head muttering tsk tsk .. trust me
i've done this countless times ... this solution works, but not very well.

Basically I'm finding that if data is being updated while someone prints a
report, it causes a corruption of the MDB. users opening new connections
receive an "Unrecognized format" exception... at which point I open the mdb,
repair and it's ready to go. doing this a couple times a week is obviously
unacceptable .. so i'm looking for a better solution.

Any suggestions?
 
S

SA

Kelly:

There's a number of alternatives to the method you are using of updating the
query def directly.

1.) The first one, (which may be obvious to rule out if you are you are
currently using a total SQL replacement) is to use the SQL WHERE clause
option of the open report method. Use this if you are only filtering the
report based on the same core recordset.

2.) If you need to use total separate SQL, i.e. the fields change etc. Then
one method to do this is to create a module in the db with two function and
a module level variable. The first function would receive the SQL string
and set the module level variable, the second would be called by the report
and retrieve the SQL from the module level variable and set it as the record
source for the report in the report's on open event.

To call the first function using automation, you'd use the Application.Run
command for Access, passing the name of the function that receives the SQL
and then the SQL string as the first parameter.

HTH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top