Exporting Help

[mrnikeswsh]

I have done many access exports over the years, but they have all been csv or fixed with, with nothing too fancy.

I was wondering if access can export a special character between records or possibly a carriage return.

For example, I do not want this
1
2
3

I would like it to export
1
#
2
#
3
#


or even
record 1x
x
record 2x
x


record 3x
x

If someone knows if this is possible or can guide me in the right direction that would be great.

Thanks again,
Brian

[RuralGuy]

You can write your own export routine using Print # statements.

[mrnikeswsh]

Where exactly would I use that? Is it within VB or access itself I never heard of that.

I have a "query" that looks up my data, and I normally just "right click" and export. and setup my specs.

hate to be a pain, but can you give me a bit more detail

[RuralGuy]

In VBA Help (not Access Help) look up Open #, Write # and Print #. I will help you write the routine if you want but you need to know what those functions do first.

[mrnikeswsh]

ok, for not have much knowledge on vb, I was able to find some code in different places and make a small script. It works, but I only get the 1st record in my dataset. The code I found was for delimiting fields. So I know exactly where my problem is, I just don't know the syntax to make it work for each record and not each field. This script only exports my first record.

Any help would be great. thanks

Option Compare Database

Private Sub cmdExportDB_Click()

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim fld As DAO.Field
Dim strText As String
Dim track1 As String
Dim track2 As String
Dim track3 As String
Dim badge As String



Set db = CurrentDb


track1 = Chr(1)
track2 = Chr(2)
track3 = Chr(3)

Set rst = db.OpenRecordset("select * from export")

Open "c:\badges\here.txt" For Output As #1


Do While Not rst.EOF
For Each fld In rst.Fields
badge = track1 & vbNewLine & track2 & fld.Value
strText = badge & vbNewLine & track3 & vbNewLine & vbNewLine

Next

rst.MoveNext

Loop
Print #1, strText
Close #1

End Sub

[Bjorn]

Hi,
look at my solution to another problem (see link below). Maybe this helps you (depending on your purpose of exporting data). Your result will easily show up in Excel. Either that is your final destination and you are satisfied, or you can use export functionalities in Excel to create any txt-like table.

https://www.accessforums.net/import-...port-3439.html

[mrnikeswsh]

Sorry, but I don't see how the two are related. Thanks for you reply though.. This is just creating a txt file with special formating and characters that are not present in the actual table. Such as Chr (1) (2) and (3) With vbnewline and such.

The problem is this section
~~~~
Do While Not rst.EOF
For Each fld In rst.Fields
badge = track1 & vbNewLine & track2 & fld.Value
strText = badge & vbNewLine & track3 & vbNewLine & vbNewLine
~~~~~

The problem is the "for each fld in rst.fields" Since I only have 1 field in this table, with many records, I get the 1 record exported but then no others.

The export would look like this
chr (1)
chr (2) record 1
chr (3)
blank line
then repeat... I just don't get it to repeat for the next records. The chr (1) are binary characters I think (smiley face) which I need in my file.

[RuralGuy]

Try outputting the record *before* you move to the next input record.

Private Sub cmdExportDB_Click()
    Dim rst As DAO.Recordset
    Dim db As DAO.Database
    Dim fld As DAO.Field
    Dim strText As String
    Dim track1 As String
    Dim track2 As String
    Dim track3 As String
    Dim badge As String
    Set db = CurrentDb
    track1 = Chr(1)
    track2 = Chr(2)
    track3 = Chr(3)
    Set rst = db.OpenRecordset("select * from export")
    Open "c:\badges\here.txt" For Output As #1
    Do While Not rst.EOF
        For Each fld In rst.Fields
            badge = track1 & vbNewLine & track2 & fld.Value
            strText = badge & vbNewLine & track3 & vbNewLine & vbNewLine
        Next
        Print #1, strText
        rst.MoveNext
    Loop
    Close #1
    rst.Close
    Set rst = Nothing
End Sub

[mrnikeswsh]

YOU DA MAN!

I think that did it, thank you so much!

[RuralGuy]

Did you notice how much easier the code is to read when you use the code tags (which is the "#" on the toolbar)? Glad we could help. Are you ready to follow the link in my sig and mark this thread as Solved?

[mrnikeswsh]

not sure what you mean by easier to read with the #....but yes, I will mark this received.

Thank you again!

Brian

[RuralGuy]

If you post by going advanced instead of the quick post you will see a tool bar that has a "#" symbol on it. That is to insert code tags into your post.