Solved

Data from Access to Text and Excell with only distributable references and Components

Posted on 1998-09-09
7
132 Views
Last Modified: 2010-08-05
I need to get data from an Access table into a text file inside Visual Basic 5.0 without using any references that cannot be freely shipped.  Record by Record.  I have tried many things.  but nothing I have found seems complete enough to work.
0
Comment
Question by:pennymurray
7 Comments
 

Expert Comment

by:firefox032697
ID: 1433932
What references do you believe can not be freely shipped?

The laws may be different in your country and am I am not a lawyer nor am I connected with the legal profession in anyway, so please do not interpret this as legal advice. But - as I read the licence agreement shipped with VB Enterprise you can redistribute the runtime libraries for DAO and RDO but there is a fairly convoluted constraint re MS-Office - other editions of VB may have other constraints. Again, I am not a lawyer so you should list the references and seek a solicitors opinion.

To look at it another way if you couldn't ship the run time libaries to access an MS-Access table, what use is VB for building a commercial application?

0
 

Author Comment

by:pennymurray
ID: 1433933
I tried to use the Microsoft Access 8.0 Object library but you cannot redistribute those libraries.  That is what I meant I think I know what track I need to be on but I cannot find an example.  I believe I have to use the Open and Write or Put statements but I am not sure of the most efficient way to code those as I have never used them before and one of my tables has 110 fields and I hate to have to append those fields even in code.  so if Someone has some suggestion or an example I would love them forever ha ha.
0
 
LVL 9

Expert Comment

by:cymbolic
ID: 1433934
Penny, don't use the Access 8.0 library, but use DAO in VB to read your table and get it into a text file.  This is freely distributable with your vb application and will be included in your install by the setup wizard.  For something as trivial as what you want to do, using the Access Object library is like using a sledgehammer to drive tacks!  Pick up and modify one of the many examples in vb help (Opendatabase, etc) and it will get you on your way.
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

 

Author Comment

by:pennymurray
ID: 1433935
I know more than that.  I need to use the Open, put and Get statements.  I already have an answer and I will post the code tomorrow.
0
 

Author Comment

by:pennymurray
ID: 1433936
Private Type ClaimRecord 'Define user-defined Type
        ReportingPolicy As String * 2
        ClaimNo As String * 4
        ClaimGroupNo As String * 3
        ClaimGroupSequence As String * 2



End Type


lnRecord = 1
        lsSQL = "Select * from ClaimTable"
        Set lrsExport = gdbReportData.OpenRecordSet(lsSQL, dbOpenDynaset)
        lrsExport.MoveFirst
        Open "d:\reporting system\TestClaim.txt" For Random Access Read Write As #1 Len = 1125
        lrecRecord.ReportingPolicy = lrsExport.Fields(0).Name  ' trying to get headers somehow
       
        lrecRecord.ClaimNo = lrsExport.Fields(1).Name
        lrecRecord.ClaimGroupNo = lrsExport.Fields(2).Name
        lrecRecord.ClaimGroupSequence = lrsExport.Fields(3).Name

        Put #1, lnRecord, lrecRecord
       
        Do While Not lrsExport.EOF
            lnRecord = lnRecord + 1
            If IsNull(lrsExport.Fields(0)) Then
                lrecRecord.ReportingPolicy = ""
            Else
                lrecRecord.ReportingPolicy = lrsExport.Fields(0)
            End If
            If IsNull(lrsExport.Fields(1)) Then
                lrecRecord.ClaimNo = ""
            Else
                lrecRecord.ClaimNo = lrsExport.Fields(1)
            End If
            If IsNull(lrsExport.Fields(2)) Then
                lrecRecord.ClaimGroupNo = ""
            Else
                lrecRecord.ClaimGroupNo = lrsExport.Fields(2)
            End If
            If IsNull(lrsExport.Fields(3)) Then
                lrecRecord.ClaimGroupSequence = ""
            Else
                lrecRecord.ClaimGroupSequence = lrsExport.Fields(3)
            End If
            Put #1, , Chr(13) & Chr(10)
            Put #1, lnRecord, lrecRecord
            lrsExport.MoveNext
        Loop
        Close #1

This puts 1 line into a text file and appends each record to the end of that line This question is still worth full points if you can help me to get headers and and records on separate lines.

Thanks,
Penny
0
 

Author Comment

by:pennymurray
ID: 1433937
added a field to the Type called EndOfRecord and set it equal to VBKeyReturn.  I solved it myself so I will not give points to anyone else.  Thanks,Penny
0
 
LVL 7

Accepted Solution

by:
linda101698 earned 100 total points
ID: 1433938
I am posting the solution found by pennymurray so it can be saved in the previously asked questions. Penny, please see your customer service question for an explanation.

Linda Gardner
Customer Service @ Experts Exchange

Comment
     From: pennymurray
                                   Date: Friday, September 11 1998 - 08:23AM PDT

     Private Type ClaimRecord 'Define user-defined Type
             ReportingPolicy As String * 2
             ClaimNo As String * 4
             ClaimGroupNo As String * 3
             ClaimGroupSequence As String * 2



     End Type


     lnRecord = 1
             lsSQL = "Select * from ClaimTable"
             Set lrsExport = gdbReportData.OpenRecordSet(lsSQL,
     dbOpenDynaset)
             lrsExport.MoveFirst
             Open "d:\reporting system\TestClaim.txt" For Random Access Read
     Write As #1 Len = 1125
             lrecRecord.ReportingPolicy = lrsExport.Fields(0).Name  ' trying to get
     headers somehow
             
             lrecRecord.ClaimNo = lrsExport.Fields(1).Name
             lrecRecord.ClaimGroupNo = lrsExport.Fields(2).Name
             lrecRecord.ClaimGroupSequence = lrsExport.Fields(3).Name

             Put #1, lnRecord, lrecRecord
             
             Do While Not lrsExport.EOF
                 lnRecord = lnRecord + 1
                 If IsNull(lrsExport.Fields(0)) Then
                     lrecRecord.ReportingPolicy = "" 
                 Else
                     lrecRecord.ReportingPolicy = lrsExport.Fields(0)
                 End If
                 If IsNull(lrsExport.Fields(1)) Then
                     lrecRecord.ClaimNo = "" 
                 Else
                     lrecRecord.ClaimNo = lrsExport.Fields(1)
                 End If
                 If IsNull(lrsExport.Fields(2)) Then
                     lrecRecord.ClaimGroupNo = "" 
                 Else
                     lrecRecord.ClaimGroupNo = lrsExport.Fields(2)
                 End If
                 If IsNull(lrsExport.Fields(3)) Then
                     lrecRecord.ClaimGroupSequence = "" 
                 Else
                     lrecRecord.ClaimGroupSequence = lrsExport.Fields(3)
                 End If
                 Put #1, , Chr(13) & Chr(10)
                 Put #1, lnRecord, lrecRecord
                 lrsExport.MoveNext
             Loop
             Close #1

     This puts 1 line into a text file and appends each record to the end of that line
     This question is still worth full points if you can help me to get headers and
     and records on separate lines.

added a field to the Type called EndOfRecord and set it equal to
     VBKeyReturn.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

Suggested Solutions

Title # Comments Views Activity
Validating VB6 Function 19 65
vb6 connector to mongodb 2 122
SQL VB connection works in one PC and doesn't in another 15 68
How to compare ms sql hashbytes results within vb6 5 85
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
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…
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…

820 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