Access 2007 & Vbs Output Restrictions

[Lost In Programming]

I am having trouble with an output in Access. I am using vba to create an
output string in a txt file. In access 2003 I am limited to a string length
of 255 characters. Does Access 2007 allow a longer string length? How can I
create an output record length of 411 characters in either Access 2003 or
2007?

 

[Clifford Bass]

Hi,

I know of no reason you cannot do that in ANY version of Access.
Perhaps you could post the relevant code?

Clifford Bass

 

[Lost In Programming]

Hope you can help me Clifford,

Here is my code:

strOutput = "03, " & Format(strDate, "mm/dd/yy") & "," & AdjStrLen("KF", 26)
& ", " & strEmpNum & ", " & strBN & "," & AdjStrLen(CStr(strID), 16) & ", " &
strAmt
AddToStringTable (strOutput)

.MoveNext

Currently on this source, I am limiting the output to a small size, however,
if I try and add more fields, access limits me to 255 characters in each
output line. If I put more than 255 characters worth, Access halts the code
with an error message. I am trying to create an output file that can be
uploaded by one of our clients. There will be about 20 fields that may or
may not be populated, but must fall in a specific start and stop location on
the txt file.

Any help would be appreciated.

 

[Clifford Bass]

Hi,

What is this AddToStringTable function/subroutine?

Clifford Bass

 

[Lost In Programming]

Here you go. I'm betting this is very confusing. This databse was written
by a friend of mine who is MIA currently. I am sure there is a better way to
do this and I am really open to re-writing the code. I basically have a
table (tbldonations) that has about 30 fields in it for each record. Some
fields may have data, some are blank. I need to create an output file (.txt)
of all records entered on a certain date. I know I can pull the records with
a query for the date range I need. My problem is creating the .txt file and
making sure that the information for each record falls in specific positions
which go out as far as 597 positions.

For instance, one of my fields is [TitleCode]. On the output file, the 2
position title code must be in spaces 27 and 28, counting from the left to
the right. Next would be [FirstName] which must be in positions 29 through
44, left justified. After that would be [LastName] which must start in
position 45 and go through 60 left justified. This goes on and on until all
fields are accounted for through postion 597.

Where could I find code to do this? Or what can I try?

This is what you were asking about:

Public Function AddToStringTable(strInput As String) As Boolean
On Error GoTo Error_Catcher

Dim dbATS As Database
Dim rsTable As Recordset

Set dbATS = CurrentDb
Set rsTable = dbATS.OpenRecordset("Output Table - OT")

With rsTable
.AddNew
![Output].Value = strInput
.Update
.Close
End With
dbATS.Close

Error_Catcher_Exit:
Exit Function
Error_Catcher:
Resume Error_Catcher_Exit
End Function

 

[Clifford Bass]

Hi,

Ah, as I suspected; the problem is related to the size of a column in a
table. The quickest fix would be to change the "Output" column in "Output
Table - OT" from a text field to a memo field.

Unless you are massaging the data that you are exporting in some
complicated way, you most likely could eliminate that code entirely and just
use Access's export-as-fixed-width-text feature in conjuction with the query
or table that is the original source of the data. This is available in all
of the versions of Access that I have ever used. You will need to create an
export specification so that you can repeat the process without having to
define the output field sizes every time. If you give a try and run into
questions, post back.

Clifford Bass

 

[Lost In Programming]

Cool, on changing the column from a text to a memo field. How do I do that
in vb code? The table does not actually exist in the database. It is
created each time the output code is executed and then vanishes as soon as
the file is saved as a txt file.

As, for your second suggestion, where do I find out how to create export
specifications? This sounds like a good method. I would like to make this
as simple as possible, becasue my training in code writing is all completely
self taught by trial and error.

You are being very helpful thank you!

Hi,

Ah, as I suspected; the problem is related to the size of a column in a
table. The quickest fix would be to change the "Output" column in "Output
Table - OT" from a text field to a memo field.

Unless you are massaging the data that you are exporting in some
complicated way, you most likely could eliminate that code entirely and just
use Access's export-as-fixed-width-text feature in conjuction with the query
or table that is the original source of the data. This is available in all
of the versions of Access that I have ever used. You will need to create an
export specification so that you can repeat the process without having to
define the output field sizes every time. If you give a try and run into
questions, post back.

Clifford Bass

Here you go. I'm betting this is very confusing. This databse was written
by a friend of mine who is MIA currently. I am sure there is a better way to
do this and I am really open to re-writing the code. I basically have a
table (tbldonations) that has about 30 fields in it for each record. Some
fields may have data, some are blank. I need to create an output file (.txt)
of all records entered on a certain date. I know I can pull the records with
a query for the date range I need. My problem is creating the .txt file and
making sure that the information for each record falls in specific positions
which go out as far as 597 positions.

For instance, one of my fields is [TitleCode]. On the output file, the 2
position title code must be in spaces 27 and 28, counting from the left to
the right. Next would be [FirstName] which must be in positions 29 through
44, left justified. After that would be [LastName] which must start in
position 45 and go through 60 left justified. This goes on and on until all
fields are accounted for through postion 597.

Where could I find code to do this? Or what can I try?

This is what you were asking about:

Public Function AddToStringTable(strInput As String) As Boolean
On Error GoTo Error_Catcher

Dim dbATS As Database
Dim rsTable As Recordset

Set dbATS = CurrentDb
Set rsTable = dbATS.OpenRecordset("Output Table - OT")

With rsTable
.AddNew
![Output].Value = strInput
.Update
.Close
End With
dbATS.Close

Error_Catcher_Exit:
Exit Function
Error_Catcher:
Resume Error_Catcher_Exit
End Function

 

[Clifford Bass]

Hi,

You are welcome.

In your code you should find where it creates the table, probably using
the CreateTableDef method. Then there will be something like this:

..Fields.Append .CreateField("Output", dbText)

Change the dbText to dbMemo.

To create an export specification, you simply do an actual export. In
Access 2003: File, Export, set type to Text Files and give it a name. Export
button. Choose Fixed Width then next button. You can adjust the column
sizes by dragging the vertical lines. Click the Advanced button. You can
tweak the settings here. Click on Save As button. Give it a name then OK
button twice. Cancel if you do not want to do the actual export. The next
time you export you can click on the Advanced button right away when you get
to the Export Text Wizard. Then click Specs button and choose your saved
specification. Open button, OK button, then Finish button.

To do the export from code you would do something like this:

DoCmd.TransferText acExportFixed, "MyExportSpecificationName", _
"qryToExport", "C:\Temp\Output.txt", False

Note that I have encountered some issues with how it formats dates.
When that happens, I use Format(MyDateField, "mm/dd/yyyy") in the query
instead of just the field itself.

Hope that helps,

Clifford Bass

 

[Lost In Programming]

Thank you very much. I will try this tonight.

Hi,

You are welcome.

In your code you should find where it creates the table, probably using
the CreateTableDef method. Then there will be something like this:

.Fields.Append .CreateField("Output", dbText)

Change the dbText to dbMemo.

To create an export specification, you simply do an actual export. In
Access 2003: File, Export, set type to Text Files and give it a name. Export
button. Choose Fixed Width then next button. You can adjust the column
sizes by dragging the vertical lines. Click the Advanced button. You can
tweak the settings here. Click on Save As button. Give it a name then OK
button twice. Cancel if you do not want to do the actual export. The next
time you export you can click on the Advanced button right away when you get
to the Export Text Wizard. Then click Specs button and choose your saved
specification. Open button, OK button, then Finish button.

To do the export from code you would do something like this:

DoCmd.TransferText acExportFixed, "MyExportSpecificationName", _
"qryToExport", "C:\Temp\Output.txt", False

Note that I have encountered some issues with how it formats dates.
When that happens, I use Format(MyDateField, "mm/dd/yyyy") in the query
instead of just the field itself.

Hope that helps,

Clifford Bass

 

[Clifford Bass]

You are welcome. And good luck!

Clifford Bass