Full Version: TransferSpreadsheet vs OutputTo
UtterAccess Forums > Microsoft® Access > Access Forms
Maxer
What exactly is the differnce between the two?
I have been using OutputTo as I like that it lets me open the spreadsheet as soon as it is exported. I'm using OutputTo to take the contents of a form datasheet and dump it into Excel
However, is there any difference between this and transferspreadsheet?
Is there a limit to the max # of rows or any other oddities I should be aware of?
Basically is there any reason to use one vs the other?
freakazeud
Hi,
the major difference is that outputto does not allow you to append data to existing workbooks, but rather either overwrites or creates a new file with the output. Transferspreadsheet lets you append to an exisiting file without overwriting anything. There is also a difference in fileformats (version) the two methods use which is then tied to the row limit and truncating of data.
HTH
Good luck
Maxer
So it seems that OutputTo uses Excel 95 as its native format, is there anyway to change that to a newer version of Excel?

Oreally don't like the idea of having row limits... <img src="/forums/images/icons/frown.gif">

It seems that TransferSpreadsheet will let me play with cell ranges and the such.

However, I don't see a way to export data from a recordset or datasheet view of a form using TransferSpreadsheet.

Is that correct, does that mean I must use OutputTo for my dataview form?

However, that would mean I'm still limited to 16K or so rows then in Excel?
Edited by: Maxer on Tue May 30 10:59:49 EDT 2006.
freakazeud
You would use the TransferSpreadsheet method in that case.
HTH
Good luck
Maxer
But if I use transferspreadsheet how can I transfer out a recordset or a datasheet view form?
don't see a way to tell it to take the data in my recordset as it's source for the export.
freakazeud
You would transfer a query which holds the filtered out data. The form would be based on the same query. The only reason to output reports and forms are for graphical purposes if you need to keep the formatting e.g. snapshot or pdf format.
HTH
Good luck
Maxer
Ah, so in THAT case I would have to have an actual stored query in Access.
o I couldn't transfer:
Dim rstResults As New ADODB.Recordset
Dim strSQL as String
strSQL="SELECT * From TableThatHasData Where City=" & Me.cboCitySelected & " ;"
rstResults.open strSQL, ..etc...
I would have to open an actual query that had that same SQL code, and then export that?
SO: I would have to use DAO queryDefs (since I've basically given up on ADO commandtext stuff) to modify a query to fit the current needs of the user, and then export that?
To take it another step:
IF this were a split database for multiple users, then I wouldn't want to actually MODIFY an EXISTING query because that could cause issues for someone else using qryDynamicResults.
So I would have to either:
Split the database, but leave a copy of qryDynamicResults on the user side of the databse, and NOT the back end.
OR:
create a temp query called qryDynamicResults_xyz that the VBA generates for that one use, then deletes when done
Is that correct?
I just want to make sure I fully understand what I'm looking at here for both options.
Right now I'm thinking maybe my users will just have to live with not being able to export query results (well recordsets) with more than 16K rows... but then I don't think I'd ever have any query results with that many rows..
but then 640K should be enough for anybody... which tells me that perhaps I need to just write the proper code to allow for more than 16K rows...
Although honestly 640K wasn't THAT bad with a bit of memory manager tweaking and a proper config and autoexec.bat file with a Menu for loading X-Wing or Windows 3.11... but I digress.
freakazeud
In a multi user environment every user should get a copy of the frontend to link to the one backend. Frontends hold queries, the only thing in the backend is tables.
If the sql doesn't change you could create a dummy query and then assign the dynamic sql to that e.g.:

CurrentDb.QueryDefs("YourQuery").sql = sql

Then you can use YourQuery in the transferspreadsheet method. You can also just create temp queries if you want e.g.:

sql = "..."
db.CreateQueryDef "temp", sql

Then use that and get rid of the temp query after you are done.
If you want complete control of everything...then maybe you should use automation to create the workbook and add the data through that.
HTH
Good luck
Maxer
Yeah I was afraid you would suggest using automation...
It does take it a LONG time to move cell by cell or table by table... however, I suppose that would let me do fun things like Auto Filter rows and the such.
HAs for the query you make a good point that I overlooked, however, let me be sure I understood it correctly:
As each front end will have its OWN copies of the queries I could have a query: qryYourQuery
Then when the user opens their FE and then opens the form "frmExportToExcel"
The VBA in that form would take the query: qryYourQuery and update the SQL with new SQL based off the data they input into the form.
Then when the qryYourQuery's SQL is updated with a querydef it would ONLY update the USER'S LOCAL COPY of qryYourQuery.
It would NOT impact the query of any other users.
Is that correct?
--
Now that would mean, however, that if the database was NOT split, then it would cause serious issues if I were to change the SQL of a query on the fly.
(Of course if I have multiple users then I should have split the database into a FE/BE in the first place, or that is my understanding).
freakazeud
Yes that is correct and as you stated yourself in a multi user environment the application HAS TO be split.
HTH
Good luck
Maxer
Thanks, I'll add that to my "List of things I know are good for me, but I really don't want to have to do" ... somewhere right above stop drinking pop, and below Exercise 45 minutes every day
wink.gif
It is good to know, and I'll test it out in my next project (current one is single user only).
Thanks again.
freakazeud
You're welcome.
Good luck on future projects!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.