View Single Post
  #4 (permalink)  
Old April 25th, 2008, 07:42 AM
mmcdonal mmcdonal is offline
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

Okay, I used a table to get the data, but you can use whatever method you are using to pull the data locally. I would pull it into your temp table, then pull the recordset, then delete it.

I was successful in getting this to work to open a form, but I am having problems with a report:

FORM On Load Event
Dim rsTable As ADODB.Recordset
Dim sSQLTable As String
Dim sDoc As String
Const cSnap = 2

sSQLTable = "SELECT * FROM MyTable"

Set rsTable = New ADODB.Recordset
rsTable.CursorLocation = adUseClient
rsTable.LockType = adLockBatchOptimistic
rsTable.CursorType = adOpenKeyset
rsTable.Open sSQLTable, CurrentProject.Connection

Set rsTable.ActiveConnection = Nothing

Me.RecordsetType = cSnap

Set Me.Recordset = rsTable

Access 2003 VBA says this is possible using shaped recordsets, but that is a weighty issue and still has problems that may throw errors if you do everything correctly anyway.

Here is my kludge:

Create a copy of your report and then unbind it from the query you used to run it from.
Then create a local table as a template that has all the fields that you will need on your report.

Take the user name from the local computer:

sUser = (Environ$("Username"))

Then, pull in your data to the local table (one column).

Then create a new table for the user to run the report from based on a template table:

Dim sTableName As String

sTableName = sUser & "_TempTable"

DoCmd.CopyObject, sTableName, acTable, "YourTemplateTableName"

Then create an update query string to move the compiled data from the two or more tables you used to run the report from, to your sTableName table. Hint, create the update query on the template table, and then copy the sql with necessary changes to point to the sTableName table, like:

sSQL = "INSERT INTO " & sTableName & " SELECT ..."

Then execute the SQL String:


So now the data should be compiled in the user's copy of the table, and you can go back and delete whatever is in the local table with the single column of data.

On the On Close event of the form, delete the user's table:

Dim sTableName As String

sTableName = Me.RecordSource

DoCmd.DeleteObject acTable, sTableName

Did you get all of that?


Look it up at:
Reply With Quote