Create Temp table from query, with memo fields?

[Larry]

I am using Access XP and trying to export queries to Excel XP with
memo fields that are getting truncated to 255. These fields have some
functions in them to get them to look right, so the only way I can
think to get them exported to Excel properly is to create a temporary
table then export that table instead.

The problem is, some of the queries do not have data longer than 255
and some do. They also do not all have the same columns. So I cannot
create a standard table and export to that table. What I was trying to
do in my code (to make it work with multiple queries) is simply write
a SQL statement like this:
"SELECT * INTO TEMP_EXPORT FROM [" & strQueryName & "]"

This exports the data, but some of the columns that need to be Memo
fields are not created "automatically" as memos. Some of the queries
bring back several hundred rows and I guess Access is looking at the
first X percent and saying, "ok, that column can be a text field", but
then it truncates data that tends to be longer later in the query.

Is there a way to force the fields to be memo fields when they are
created this way?

Does anyone have any other suggestions?

TIA,
Larry

 

[Stefan Hoffmann]

hi Larry,

I am using Access XP and trying to export queries to Excel XP with
memo fields that are getting truncated to 255.

How do you export your data? DoCmd.TransferSpreadsheet works for me.


mfG
--> stefan <--

 

[Larry]

hi Larry,


How do you export your data? DoCmd.TransferSpreadsheet works for me.

mfG
--> stefan <--

That works, unless you have memo fields that are longer than 255 and
have functions or formatting in the fields. In that case, the data is
truncated to 255. This temp table is the only way around it that I can
figure out so far.

 

[Stefan Hoffmann]

hi Larry,

That works, unless you have memo fields that are longer than 255 and
have functions or formatting in the fields. In that case, the data is
truncated to 255. This temp table is the only way around it that I can
figure out so far.

Post example data.


mfG
--> stefan <--

 

[Larry]

hi Larry,


Post example data.

mfG
--> stefan <--

Example data won't really help, it's just a bunch of text. The first X
rows of data have less than 255 characters in the field, but then many
of the following rows have well over 255 (some 2,000+). When using the
SQL statement to build the temp table, Access is make the field a TEXT
field, I need to know how to force it to be a Memo field (if it's
possible).

I'm beginning to think I'm going to have to create the table
programmatically before I load it (what a pain).

 

[Stefan Hoffmann]

hi Larry,

Example data won't really help, it's just a bunch of text. The first X
rows of data have less than 255 characters in the field, but then many
of the following rows have well over 255 (some 2,000+).

Using DoCmd.TransferSpreadsheet i can export without any problem memo
fields > 4k.

So please post your code you are using to export and some example data.
The data must not be that large, but i'd like to see what you call
"functions or formatting".


mfG
--> stefan <--

 

[Larry]

hi Larry,


Using DoCmd.TransferSpreadsheet i can export without any problem memo
fields > 4k.

So please post your code you are using to export and some example data.
The data must not be that large, but i'd like to see what you call
"functions or formatting".

mfG
--> stefan <--

The TransferSpreadsheet command looks like the following:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
mstrQueryName, Me.txtExportFile, True, ""


One of the queries has a column that is the result of a function,
which similar columns from child records. This is the FIELD definition
for that field in the query:
CombinedLog: CombineLogDesc([TicketID])

The CombineLogDesc function takes the ID of related records and pulls
them together so they appear as one large value.
Public Function CombineLogDesc(intID As Integer) As String
Dim dbs As Database
Dim rst As Recordset

Dim strCombined As String, _
strSeparator As String

strSeparator = Chr$(13) & Chr$(10)

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Desc, LogDate from tblLog
where TicketID = " & intID & _
" Order by LogID desc")
With rst
Do Until .EOF
If Not IsNull(.Fields("Desc")) Then
strCombined = strCombined & strSeparator & String(25,
"-") & .Fields("LogDate") _
& Chr$(13) & Chr$(10) & .Fields("Desc")
End If
.MoveNext
Loop
End With
If Len(strCombined) > Len(strSeparator) Then
strCombined = Mid(strCombined, Len(strSeparator) + 1)
End If
CombineLogDesc = strCombined
End Function



Example of truncated text.
============THE FOLLOWING COMES FROM THE QUERY, 438
CHARACTERS============
-------------------------10/18/2006 8:47:08 AM
""Holds"" is a method in xxxxxxx to allow documents to be imported but
held from user access. We could use this for large bulk imports where
doc control wants to validate the results before releasing the
documents to the project.
-------------------------9/14/2006 11:25:14 AM
""Holds"" are a DMS functionality that yyyyyyy is not using.
-------------------------9/6/2006 3:34:37 PM
Report to zz
==========================================================================

==THE FOLLOWING COMES FROM THE SPREADSHEET AFTER EXPORT, 255
CHARACTERS===
-------------------------10/18/2006 8:47:08 AM
""Holds"" is a method in xxxxxxxx to allow documents to be imported
but held from user access. We could use this for large bulk imports
where doc control wants to validate the results before releasing the
docum
==========================================================================

 

[Stefan Hoffmann]

hi Larry,

Larry wrote,

One of the queries has a column that is the result of a function,
which similar columns from child records. This is the FIELD definition
for that field in the query:
CombinedLog: CombineLogDesc([TicketID])

I see, you were right with using a temporary table.


mfG
--> stefan <--