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:
Dim acApp As Access.Application
Call acApp.DoCmd.OpenReport(strReportName, vbViewPreview)
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.
strSQL = "SELECT blah blah blah ... WHERE blah='%s'"
strSQL = Replace(strSQL, "%s", intParamID)
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = OpenDatabase("C:\MyDB.mdb")
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.