Modifying Export specification with VBA 2

[startup]

Is there any way to modify an export specification from VBA? I have a certain table with X number of fields that change as I import a spreadsheet into it (it might be 2 fields or it might be 20). Then when I try to use a transfertext to export it in a different format, I need an export specification to tell it that I want it comma delimited, but without quote marks. However when I go through the manual process of creating and saving an export specification it also saves it with the current fields. The next time I import and there is a different number of fields now in the table with different field names, the export specification breaks and returns odd error messages.

Thanks for any suggestions.
Rico

 

[GingerR]

I just successfully added a record to the system table MSysIMEXSpecs using a DAO recordset. You can set the SpecName and SpecType (2 is Delimited). Also set the other fields as appropriate--you'd have to research. Then put the columns in to table MSysIMEXColumns. You'll have to fiddle around with what data goes where. What a good idea!! I'll have to use that some time!!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[MichaelRed]

GingerR,

I agree that the concept seems quite 'nice'. It seems like a more or less complete example would be an excellent FAQ.



MichaelRed

 

[startup]

GingerR,

Beautiful! What you suggested along with the information from this post

http://www.tek-tips.com/viewthread.cfm?qid=764888

gave me just what I needed to get my project working. All I ended up doing was deleting all the data in the MSysIMEXColumns table. The MSysIMEXSpecs already had the correct specifications (ie comma delimited with no quotes). Then when I ran the export with my export specification it would rebuild the MSysIMEXColumns table with the current information. Beautiful!

strSQL = "DELETE MSysIMEXColumns.* FROM MSysIMEXColumns;"
DoCmd.RunSQL strSQL

Again thank you very much.
Rico