How to update report in access with bound dataset automatically?

Question

I have a single entry output from a paradox table which is imported into Access. Now I have created a report and done the databinding within the report to the dataset from Paradox. When work is done in Paradox the single entry output changes... How do I open up access and have the values from the report update automatically without having to reimport the table manually?

The objective is that when access is opened the report comes up immediately for inspection before printing.

Answer 1

Use a linked table instead of importing the data (assumes a provider for paradox is available, which seems likely).

Answer 2

Linking directly to the Paradox table should work. The way to do what you asked, automating the import, would be to run DoCmd.TransferDatabase in either an autoexec macro or in the OnLoad event of your report. You would probably want to start with a delete query to reset the table too.

Answer 3

Link table to Paradox file through GetData on menu. You select the file from the route directory where your db files are and select. Maintain a link and bind the date relationships to the dataset. As you update using the Paradox database the tables in Access will update also. You musn't run the db and access the same time otherwise jet engine won't work.

Answer 4

From the design view of the report, go to the property sheet and the Event tab. Find the On Load event and click the elipsis button. Select Code Builder if presented with the Choose Builder dialog. This will drop you into the VBA editor in the Report_Load event. Edit to look something like this:

Private Sub Report_Load()

DoCmd.TransferDatabase acImport, "Paradox 7.x", "c:\yourdata\yourfile.tbl", acTable, "SourceTable", "YourAccessTable"

End Sub

I'm not sure on the specifics for the Paradox version, the file naming convention, etc. (I only dabbled with Paradox briefly and don't remember it's specifics). Watch the intellisense or put the cursor on TransferData and press F1 to get the Help for the TransferDatabase method for reference.

Ahead of the TrasferDatabase statement you might want to run something like:

CurrentDb.Execute "DELETE FROM TableName"

That will blow out the destination table on the Access side ahead of the load from the Paradox side.