pennymurray
asked on
Data from Access to Text and Excell with only distributable references and Components
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.
ASKER
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.
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.
ASKER
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.
ASKER
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.OpenRecordSe t(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.ClaimGroupSeque nce = 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.ClaimGroupSeque nce = ""
Else
lrecRecord.ClaimGroupSeque nce = 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
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.OpenRecordSe
lrsExport.MoveFirst
Open "d:\reporting system\TestClaim.txt" For Random Access Read Write As #1 Len = 1125
lrecRecord.ReportingPolicy
lrecRecord.ClaimNo = lrsExport.Fields(1).Name
lrecRecord.ClaimGroupNo = lrsExport.Fields(2).Name
lrecRecord.ClaimGroupSeque
Put #1, lnRecord, lrecRecord
Do While Not lrsExport.EOF
lnRecord = lnRecord + 1
If IsNull(lrsExport.Fields(0)
lrecRecord.ReportingPolicy
Else
lrecRecord.ReportingPolicy
End If
If IsNull(lrsExport.Fields(1)
lrecRecord.ClaimNo = ""
Else
lrecRecord.ClaimNo = lrsExport.Fields(1)
End If
If IsNull(lrsExport.Fields(2)
lrecRecord.ClaimGroupNo = ""
Else
lrecRecord.ClaimGroupNo = lrsExport.Fields(2)
End If
If IsNull(lrsExport.Fields(3)
lrecRecord.ClaimGroupSeque
Else
lrecRecord.ClaimGroupSeque
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
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?