|
Subject:
|
Report from disconnected recordset
|
|
Posted By:
|
DreamEagl
|
Post Date:
|
1/9/2006 11:42:53 AM
|
Hi,
I have a FE with all my forms and a BE with all my tables. The forms in the FE create disconnected recordsets from the BE for data entry and manipulation. How can I get a report that resides in the FE to use the local disconnected data, rather than having to copy the table from the BE and deleting it again after?
Thanks for your help.
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
1/9/2006 1:46:14 PM
|
It sounds like your backend is a .mdb, in which case you'd probably need to create a local, temporary table in the FE based on a stored temp table template for your report (you can do this in code), open an empty recordset based on your temp table, transfer the data from your disconnected recordset into the temp table, then use the temp table as the RecordSource property for your report. I don't think you can use a report object's Recordset property to bind to a recordset in a .mdb, but I could be wrong.
Bob
|
|
Reply By:
|
DreamEagl
|
Reply Date:
|
1/9/2006 2:10:38 PM
|
Thanks Bob,
you are correct, the back end is an .mdb. I will give your solution a try. Since I am not that proficient in my coding yet, it might take a bit. I will let you know how I made out.
Thanks.
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
1/9/2006 3:15:00 PM
|
You can create a query in the BE to limit the recordset that you need for your report, and then just transfer the results of the query. This will keep the wire cool.
You would do something like:
'---------------------------
DoCmd.SetWarnings False
DoCmd.RunQuery "qryDELETEfromYourTable"
DoCmd.TransferDatabase transfertype:=acImport, _
databasetype:="Microsoft Access", _
databasename:="Z:\Data\Database_be.mdb", _
objecttype:=acTable, Source:="qryYourQuery", _
Destination:="tblYourTable"
DoCmd.SetWarnings True
'---------------------------
DoCmd.SetWarnings False turns off your application warnings for the Delete query coming up.
The Delete query empties the local table for new data. You have to build the delete query yourself.
DoCmd.TransferDatabase takes the results of the query on your back end, and shoves it into your local table that you just cleaned out. This is the temp table, but not really since it is permanent.
DoCmd.SetWarnings True turns your warnings back on.
HTH
mmcdonal
|
|
Reply By:
|
DreamEagl
|
Reply Date:
|
1/9/2006 3:26:45 PM
|
Thanks for the great help. I will keep you posted on the results.
|
