Exporting query data

[snowmantle]

Hi,

I have an Access project file linked to an sql server database.

At the moment i have a form that you can use with combo boxes which will create a custom sql statement depending on what the user picks so that the sql can be used as the filter for an Access report.

The code for exporting the info is this:

CmdRunReport_Click
DoCmd.OutputTo acOutputReport, "Report1", acFormatXLS, "File location" & Format(Now(), "ddmmyyyy") & Format(Time(), "hhmmss") & ".csv", True
DoCmd.Close acReport, "Report1"

So all i am doing is:

running the code to get the sql filter for the report which also opens the report

then a command to output the report to a .csv file

and then close the report after it is done.


The problem i have got at the moment is that a field in my report has data in it greater than 255 characters and when viewing the .csv in Excel you dont see more than 255 characters in the cells of that field.

Viewing the .csv using notepad also doesnt work well because Access has added lots of ascii characters around the information, which just makes it un-readable.

I was thinking of ways around this and one way when using a .mdb would of been to export the query directly instead of the report, as far as i am aware though you cant do it with a view in an sql database when using a .adp.

If i am wrong or someone knows a different way, please let me know.

 

[flyover789]

have you tried to output the data as tab-delimited textfile ...
I've experienced the same problem when exporting to excel (from an *.mdb but what the matter here ...)
The only way to get everything behind the 255 chars out to excel was to use a function using ole automation (which can become quite timeconsuming with large outputs)

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]

 

[snowmantle]

How do you get it to output a report in a tab-delimited or even comma-delimited text file??

Doing

DoCmd.OutputTo acOutputReport, "Report1", acFormatTXT, "location" & Format(Now(), "ddmmyyyy") & Format(Time(), "hhmmss") & ".txt", True

Exports all the info into a text file, which can be opened in Excel and spaced out using the text wizard, which works for me.

But i need it to be correctly formatted automatically.

Doing the above only creates a text file, i couldnt see a command to also set the format of the text file to tab delimited or comma delimited.

Any ideas?

 

[flyover789]

here is a function to output any table, query or view with any desired delimiter and some other options

Function flatfile_ADO(strinput As String, strdelim As String, bln_FieldNames As Boolean)
'Created by Martin Serra Jr. / Feb 2004
'Version for ADO: Sep 2005

Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Set cnn = Application.CodeProject.Connection

'strdelim is the variable for the tab delimiter
'bln_FieldNames is the variable for fieldnames inclusion

'variable for the fieldnumber of the recordset:
Dim i As Integer

'strinput is the Name of your table/query or SQL String
rs.Open strinput, cnn

'dir and name of your flatfile
strflat = "C:/flatfile" & Format(Now(), "yyyy_mm_dd_h_m_s") & ".txt"

'populate the recordset:
rs.MoveLast
rs.MoveFirst


Open strflat For Output As #1

'the following six lines include the fieldnames, if bln_FieldNames = -1
If bln_FieldNames = -1 Then
i = 0
Do Until i = rs.Fields.Count - 1
Print #1, rs.Fields(i).Name; strdelim;
i = i + 1
Loop
Print #1, rs.Fields(i).Name
End If


Do Until rs.EOF
i = 0
Do Until i = rs.Fields.Count - 1
'check if the field is of type Date/Time and format accordingly:
If rs.Fields(i).Type = 8 Then
Print #1, Format(rs.Fields(i).Value, "yyyy-mm-dd"); strdelim;
'check if the field is null and skip printing accordingly:
ElseIf rs.Fields(i).Value <> "" Then
Print #1, rs.Fields(i).Value; strdelim;
Else:
Print #1, strdelim;
End If
i = i + 1
Loop
'check if the field is of type Date/Time and format accordingly:
If rs.Fields(i).Type = 8 Then
Print #1, Format(rs.Fields(i).Value, "yyyy-mm-dd")
'check if the field is null and skip printing accordingly:
ElseIf rs.Fields(i).Value <> "" Then
Print #1, rs.Fields(i).Value
Else:
Print #1, ""
End If
rs.MoveNext
Loop

Close #1

rs.Close
Set rs = Nothing
cnn.Close
Set db = Nothing


End Function

maybe you can use it to output the recordsource view of your report ...

to test it type in the immediate window and hit enter

'Test Tab delimited with Fieldnames
call flatfile_ADO("yourViewName",Chr(9),True)
'Test Comma delimited without Fieldnames
call flatfile_ADO("yourViewName",",",False)

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]

 

[snowmantle]

That is great that. thanks for the info.

I have done an sql query on the view.

I have got a string that is create depending on what selections the user makes on a form, i used that as the where clause and it works fine.

I was wondering if there was a way to open Excel using Access and have it open the text file using the delimiter of "^" without the user having to figure it out for themselves.

I used ^ because there are lots of notes with commas and other symbols.