exporting a query as a text file through VBA

[Knobbie]

I have a situation where I need to export a query into a text file to match ASCII standards.

I have the query created and I have the code to loop through the query one row at a time and make the appropriate adjustments to each row (needing to add '|' and '~' delimiters for labels and data so it will be mapped correctly.)

the problem I have is that I'm sure of the code for creating the export as a text file (not MS Word). If you are familar with this export code can you help me out?

Thanks in advance for any help on this one I appreciate it.


-Knobbie

 

[Knobbie]

well I did some research and found the following code in the help files (finally!) I figured I'd post it here if anyone else has had the same problem:

DoCmd.TransferText [transfertype][, specificationname], tablename, filename[, hasfieldnames][, HTMLtablename][, codepage]


unfortuneatley dont think this helps me, I have to find another work around Becuase it wants to use a table/query to export and I need to adjust the query/data with deliminators first before exporting.

 

[pcs]

Knobbie,
kind of hard to keep up when you post to 2 different forums...

do you absolutely need 2 different delimiters (~ and |) ? what application are you trying to export to?

hth,
al

 

[Knobbie]

Hi Thanks for responding, The reason I had the same question in 2 forums is that the problem kind of hit in both sides (queries and vba).

Anyway I'll give you a little background on what my situation that I have:

My company is a health care management Co. That manages and runs claims for hopitals, health insurance...etc...etc...(a microsoft soft 'shop' through and through) well we are starting to do business with a 'State' for medicare claims and they use 30 year old tecnology (AS400 mainframe, tape drives etc..etc..) Well we get there data in EPCDIC format and we then have to convert that into ASCII format so we can then import that data into our SQL Server DB system using an EDI process (Electronic Data Interchange) which is an MS Access Db.


SOOOOooooooo This is the situation that Im in now. I have a text file (EPCDIC) format it has two delimiters in it, and it goes something like this:

[Label]'|'[Data]'~'

so when I import this into Access I can only choose one deliminator (I cant choose 2 it's just the way access works). So I use the '~' as my delimiter and I am left with the something like this:

(field1) [Label]'|'Data (field2)[Label]'|'Data

and so on.

Now I have to create a new table by splitting the label and the data apart (I do this through vba - looping through each column and row using instr() function looking for the '|' delimiter so I can then split the [Label] being the column name and the [data] being the actual data into a new table.

(whew - with me so far?)

okay I've got this part down and it works. The trouble comes when we need to send data back to the 'State' we have to put it back into EPCDIC format with the '|' and '~' delimiters. This is where I came and posted before because I was looking for the code to do the export to a text file because I was going to create a vba loop for each column/row and splice it {label | Data ~) back to EPCDIC format.

I hope I clarified this, and anything you can suggest to make my life easier I'd appreciate!

-Knobbie

 

[Pat Hartman]

FYI that's EBCDIC - Extended Binary Coded Decimal Interchange Code.

I'm still confused by your description of the format. Try posting a couple of rows of actual data.

 

[Knobbie]

Hi Thanks for the correction on EBCDIC - Extended Binary Coded Decimal Interchange Code. Until a week ago I've never heard of EBCDIC (and never want to hear it again!)

Anyway, I have almost finished my little application, I was busy all day with it and got it to work - almost. Just a little more bug fixes and some code adjustment and I'll have it.

What I have done so far:

We recieve data via 'reel to reel' tape (didn't know anyone used this stuff anymore??) which gets made into a large text delimited file with the [label] | [data] ~ format as I said before. Now Since access only will import 1 delim, I do a quick find an replace on the text file - replacing all the bangs (|) with tildes (~). So now access will create a table with field1 being the label and field2 being the actual data. Today I created the code that loops through that table getting the fields that represent the labels and creating a table out of it, so now you have what the actual table should have been. Then the code loops through the table again this time getting the data fields and populating the new table with appropriate data in the appropriate column. - whew

The good part is that it works for the most part and I only need to tinker with it a little longer to get it working no sweat, then it will be a piece of cake to do the same in reverse when we need to send data

I'll post the code when I get it running, and explain what it's doing - maybe have some test data for you to try out.

-Knobbie