Access and Excel NOT playing Nice (1 Viewer)

Isaac

Lifelong Learner
Local time
Today, 01:19
Joined
Mar 14, 2017
Messages
8,738
I have had trouble with OutputTo vs. TransferSpreadsheet when working in accdr, accde, or otherwise navigation pane hidden - unfortunately I cant' remember which I had more trouble with at the moment, but the trouble was such that you had to have a line of code opening the source object [table or query] prior to running the export line of code. Which was very silly. I can't remember which one it was, outputto or transferspreadsheet, and not sure if it applies to your problem at hand, but one or the other of them barfs when trying to export the results of a non-open object in certain cases.
 

Minty

AWF VIP
Local time
Today, 08:19
Joined
Jul 26, 2013
Messages
10,353
TransferSpreadsheet is very fast and reliable in my opinion.
Significantly quicker than looping through a recordset.
 

Isaac

Lifelong Learner
Local time
Today, 01:19
Joined
Mar 14, 2017
Messages
8,738
the more I think about it the more I think I remember it being OutputTo that caused me the problem. For some reason in that db I am remembering, I had to Open the object before running the OutputTo, and I remember being frustrated that there was next to nothing in MS documentation on that particular requirement (or bug).

Edit: Oh and Minty, of course I agree ... either one is faster than looping, all else being equal.
 
Last edited:

gakiss2

Registered User.
Local time
Today, 01:19
Joined
Nov 21, 2018
Messages
168
These are just a few random tips on your current code.
  • You don't need to set sendstring to "". A zero length string is the default "starting out" value for a string variable.
  • If you SetWarnings off, don't forget to set them back on somewhere, including but not limited to an error handler. One could argue this, but it is good coding to return things to the state that they were if temporarily changing them.
  • Use Currentdb.Execute (or better yet, the .Execute method called against a database variable already SET to CurrentDb), which will eliminate the need to suppress warnings at all
thank you for the tips. right or wrong, I tend to get something working first then go back and clean up to best practices. Although I sometimes forget and something this this (a warning turned off) will com back to bite me later.
 

Isaac

Lifelong Learner
Local time
Today, 01:19
Joined
Mar 14, 2017
Messages
8,738
What still gets me sometimes is turning off EnableEvents in Excel programming. I have to laugh at myself, because it's funny trying to troubleshoot that and always takes me a few moments to remember what I've done. Which generally is, failed to include an error handler. ha ha.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:19
Joined
Sep 21, 2011
Messages
14,037
OK, you could try using TempVars for the criteria.?
I tend to do that if the query is being used as a source in more than one place?
Set a Tempvar from your form and use that as the criteria.

Code:
WHERE (((tblSubmitterInvoice.DateInvoiced)=Date()) AND ((tblSubmitterClient.SubmitterID)=[TempVars]![SubmitterID]) AND ((tblSubmitterInvoice.DatePaid) Is Null))
 

gakiss2

Registered User.
Local time
Today, 01:19
Joined
Nov 21, 2018
Messages
168
OK, you could try using TempVars for the criteria.?
I tend to do that if the query is being used as a source in more than one place?
Set a Tempvar from your form and use that as the criteria.

Code:
WHERE (((tblSubmitterInvoice.DateInvoiced)=Date()) AND ((tblSubmitterClient.SubmitterID)=[TempVars]![SubmitterID]) AND ((tblSubmitterInvoice.DatePaid) Is Null))
Thank you for your help. A LOT of progress was made today. Since I was only interested in the data that was on the active form I didn't really use the query function at all. I just built a string that contained the few items I wanted, used insertinto to put them in a table to hold them. I made a query from the table so I could select only the last one entered, then I exported that to the excel wb. I had some trouble with access finding the tale and query I made to hold the data for export but that one just magically self corrected. After that I did have to cut text string back apart and put the right data into the right cells but that wasn't so bad. I put a different separator between each one then used Excel's find function to pull out the one I wanted. The string might be T7ab003 @ The bid deviation ^ DEV-GK-20-104 # Gary Kissick(me) * billdeblasio@nycity.com. After surgery 'T7ab003' got put into he part number cell, Gary Kissick got translated an email address then put in a cell to be later picked up for an email function and etc. I got a lot of help from a lot of folks but I feel you were the most focused and productive. Thank You
 

Users who are viewing this thread

Top Bottom