Solved

Using TransferText though VB w/o MS Access opening.

Posted on 1998-10-15
11
938 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

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…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

770 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