Using TransferText though VB w/o MS Access opening.

Through the MS Access 8.0 Object Library, you can do:
Access.DoCmd.TransferText to export data.  I need to know how to do this without MS Access opening up.  The reason being that the users will not have MS Access on their machines.
MatthewSkyAsked:
Who is Participating?
 
cymbolicConnect With a Mentor Commented:
even easier to do exports.  Create a udf to define your output record, using a recordset, get your records one by one and move into the udf, then use binary file i/o to write out the udf:

type orec
   fld1 as string *5
   ...
end type
dim rec as orec
open "outfile" for binary as #1
.do your dao recordset stuff here...
while not rs.eof  'assuming your recordset object is rs
 rec.fld1=rs!fld1
 put #1,,rec
 rs.movenext
wend
close #1
rs.close

If your clever, you don't need a udf, you can use the length and type properties of each field in the recordset or table to create an output record format.  IF you need crlf, then use print # statements instead of binary file i/o put # statements.
0
 
mkmccrearyCommented:
I think the Office Developers Edition is what you need.  Purchasing this allows you to distribute the Access runtime.  Your other option is to do your own TransferText like function.  It really isn't that difficult.  If you need any more help, just drop a comment
0
 
fulscherCommented:
You probably would even prefer to write your own routine - transfer text is not flexible at all.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
MatthewSkyAuthor Commented:
Any help would be appreciated.  I already had a routine but it was very slow (compared to TransferText) and wasn't very clean.  Thanks.
0
 
cymbolicCommented:
You are much better off writing your own import in VB.  Using the Access runtime in whatever means you employ is like using a sledge hammer to drive tacks.  VB's basic file functions ( Line Input, or Get #, etc) will read your text data, and simple parsing logic (mid$(inrec$,start,length)) will break down your input file for insertions.  Then just format an insert sql statement (Insert into targtable(fld1, fld2,fld3...) values('val1',num2,'val3'...) and use the .execute method to stuff the items into a table.  It's really trivial code, and much more flexible and capable for handling errors than the transfertext methods in Access.

If you need any more specifics or examples, just ask.
0
 
MatthewSkyAuthor Commented:
I'm looking for an export to a flat file, not an import.
0
 
mkmccrearyCommented:
cymbolic is on the right path.  You can go through the recordset and do what you want to do.  How you process the dataset is going to be based on whether you want a fixed-width or delimited export.

Good Luck,
Martin
0
 
MatthewSkyAuthor Commented:
I'm looking for a generic function, without a dependence on the number of fields (and size).
0
 
MatthewSkyAuthor Commented:
I'm looking for a generic function, without a dependence on the number of fields (and size).
0
 
mkmccrearyCommented:
This can be done.  We could write a function that you pass a table name, and it exports the data.  But, there is a major difference between doing fixed-width, or delimited.  I mean they are not difficult to write, it is just the way you that it is implemented.  cymbolic has the basic structure, all that needs to be done is the processing of the recordset.  Are you planning to import this file into something?  And if so, what?  This could be the key on the best way to export the data.

Let Me Know,
Martin
0
 
MatthewSkyAuthor Commented:
It will be going from Access --> Flat File --> Oracle.
I finished it using comma quote delimited, but it would have been nicer to have fixed length.  Problem example: A long integer in Access is length 4, but a long integer can be over 32000 which is 5 digits (I was using the String function to append the necessary spaces).  This is a problem for dates as well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.