Solved

Using TransferText though VB w/o MS Access opening.

Posted on 1998-10-15
11
936 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
Comment Utility
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
Comment Utility
You probably would even prefer to write your own routine - transfer text is not flexible at all.
0
 

Author Comment

by:MatthewSky
Comment Utility
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
 
LVL 9

Expert Comment

by:cymbolic
Comment Utility
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
Comment Utility
I'm looking for an export to a flat file, not an import.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 9

Accepted Solution

by:
cymbolic earned 50 total points
Comment Utility
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
Comment Utility
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
Comment Utility
I'm looking for a generic function, without a dependence on the number of fields (and size).
0
 

Author Comment

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

Expert Comment

by:mkmccreary
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now