Solved

Using TransferText though VB w/o MS Access opening.

Posted on 1998-10-15
11
940 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 50 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

710 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