Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Using TransferText though VB w/o MS Access opening.

Posted on 1998-10-15
11
Medium Priority
?
943 Views
Last Modified: 2013-12-25
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.
0
Comment
Question by:MatthewSky
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 2

Expert Comment

by:mkmccreary
ID: 1496985
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
 
LVL 12

Expert Comment

by:fulscher
ID: 1496986
You probably would even prefer to write your own routine - transfer text is not flexible at all.
0
 

Author Comment

by:MatthewSky
ID: 1496987
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:cymbolic
ID: 1496988
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
 

Author Comment

by:MatthewSky
ID: 1496989
I'm looking for an export to a flat file, not an import.
0
 
LVL 9

Accepted Solution

by:
cymbolic earned 150 total points
ID: 1496990
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
 
LVL 2

Expert Comment

by:mkmccreary
ID: 1496991
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
 

Author Comment

by:MatthewSky
ID: 1496992
I'm looking for a generic function, without a dependence on the number of fields (and size).
0
 

Author Comment

by:MatthewSky
ID: 1496993
I'm looking for a generic function, without a dependence on the number of fields (and size).
0
 
LVL 2

Expert Comment

by:mkmccreary
ID: 1496994
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
 

Author Comment

by:MatthewSky
ID: 1496995
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question