Replies

I know, but when you call an export specification from VBA, you are using a separate set of variables … why can’t I see the export wizard wherein I can go to advanced and build a spec. ???

Yeah … I use the “advanced” button to access the specifications for an Import … I can’t find the Export … where do you get to the default EXPORT specification .. It’s as if I’m missing some wizards … I tried remove install on the CP and it swears I have everything. ??

Hans … I’m using ACC 2003, and I want to build an EXPORT specification for an ascii delinited file format (just tab delimited) ; two questions .. how do I get my arms around the “default” speciication and edit it… 2) the export “Wizard” doesn’t appear in the database, every time I try to export, the windows dialog pops up … I’m missing something here Wayne

Hans, I found the problem .. parsed by another procedure call .. My error in not seeing it earlier … nested in a mess of code. Thanks to both of you … Wayne ps I did learn something, though “a text field definition in MS Access, is treated a little different than a Memo field … that is, when importing Excel”

Peter, it’s just a “DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, szTarget, szPath, True, szRange” … comes in normally to a “cache table”, drfined as text columns or a Memo field (these people write paragraphs where two sentences would work fine). But, occasionally, it goes nuts, and jambs all the words together, .. than at other times it uses nomal spacing. I can’t put my finger on the trigger that’s causing the problem. Many Excel files, all the same Template, just different text in the cells. Regards

I changed the queries to “select” queries to get away from any “parameter’ issues .. I am unable to het a correct recordset count … one time it’s 598, the next zero, maybe even a ‘1’ … it’s all over the place. I’ve made sure to close the recordset between requests and set the rescordset object to nothing … there’s no consistency in this ??? Regards Wayne

I hope you are appreciated … I went too MS, in the meantime, to see if I could dig out the fault mechanism … sure enough …”Calling the Requery method of a form’s recordset (for example, Forms(0).Recordset.Requery) can cause the form to become unbound. To refresh the data in a form bound to a recordset, set the RecordSource property of the form to itself (Forms(0).RecordSource = Forms(0).RecordSource).” … That’s their way of saying it’s “unstable” ! LOL But the second sentence is interesting … we learn something every day. Thanks Hans

I realize that, Hans, but for sake of brevity I skipped over alot of code I have an open form, I have a user select a query name in a CBO, I take that name (a string) and “on update” for the CBO I want to count potential records, as if, that ‘parameterized’ query were actually run … I can place the query name in the form’s RecordSource, and do a requery .. but I get ‘iffy’ results … sometimes the correct count, else a “1” (that’s non-sense)… it’s flippin’ me out. Regards

Hmmm … let’s make this a little broader ! Hans, if the query is ‘parameterized’ with a question msgbox, for example [What store do you want ?] in the query criterion, why do I find bad recordsets (i’m counting records to post a count on the form) that compute Recordset.count = 1 … I tried the clone and it’s a mirror of the recordset.

Hey Hans … I owe you one, so here goes:

Excel CAN be imported by Access, and the user ‘thinks’ he has a short date … BUT … once brought into a table; the format may change (we all are aware of this cute little idiosyncrasy) however, IF the table is already existing AND they import what they think is a short date’ to a SHORT DATE column (defined pre-import); it’s pot luck, Buddy. Look at the import data, sometimes you get a ‘date-time” string, not a short date !!! Oh, and that date-time string (or other dat-formats) will not be found be a SQL or Query that is looking for a SHORT DATE …. you can chase a lot of rabbits with that little screw up …. MS needs to solve this’ not expect us to add alot of extraneous check code!! regards Wayne

Hi Rory … yeah, MS Access can create the Excel object and send the data … works great … but I have not had an interest in coding Excel to ‘pull’ the data … defeats the purpose of the exercise “the client is using access”, he doesn’t want to close access and open Excel just to get the data into a workbook … Nice thought, but it’s just now workable. Thanks again .. Wayne

Yeah, thanks Nick … I have a client database, they want to generate “what if” spreadsheets, so it’s a natural to ‘push’ a dataset out of MS Access. I’m not lazy, but when all is needed is a one time Excel WB with one tab, I see no sense in creating an Excel Object and all the code to make it. TransferSpreadsheet ‘ought’ to work Inasmuch as Excel will recognize a CSV formatted document and open it normally, that’s probably the way to go. I’m like you, I think the function call is not releasing .. I may play with the closing and release of the form object, just to see if that works. Regards Wayne

Yeah … I know it’s an issue … but ever since ACC2000, the product has been pretty stable. I don’t make CSV files (as a work around) because the clients want Excel; and it should WORK ! Not yelling, you understand, but this is pretty simple stuff Is there a way to “force” the O/S to delete the file outside of the explorer window ??? Kind Regards Wayne

Hey Hans, I looked at this post … see post 628,902. Looking at his code, he’s evoked an Access object, then used that object’s, internal, object methods to ‘close’ the database …. I’m no expert on that, but he still has an ‘open’ instance of Access on his hands, so that will screw up any later methods … That’s not exactly the same as my problem. Regards Wayne

Hans. it’s a local drive on a machine with XP sp-2 … so I’m stumped. There must be something still ‘clinging’ to the created file from MS Access, I suspect the MS Access DLL (whichever one) has not recognized a completion of the worksheet, and it’s waiting on a ‘return code’ … and the cleanup routine in Access does not ‘close’ the Excel file when Access is closed. MS knows of the problem, but I get zero interest solving the problem.