J
Jerry
I have several tables with memo fields that I allow the
user to key data into using both single and double
quotes. I then take the data and create a single text
file to be used in an import/export fashion using the | as
a field separator. I use the textstream object with
Writeline and Readline properties to write and read the
data. However I run into problems when I read a line that
has the memo data because the user may have inserted a
hard line return in the field, this causes the readline to
read only part of the data, thus screwing up my UPDATE
statements. I have tried using Input # but it will not
work with the quotes so I am looking for other more
effective alternatives!
Any recommendations on a better approach would be GREATLY
Appreciated!!
I have set up a parsing function to go through and pull
out the data for each field for the update as shown below
(fnParseData). The code that calls the function follows:
tmpData = tsExportData.ReadLine
aryReturnData = fnParseData(tmpData, 5)
tmpOrg = aryReturnData(0)
tmpCompany = aryReturnData(1)
tmpDescription = aryReturnData(2)
tmpDateMod = aryReturnData(3)
tmpGlossary = aryReturnData(4)
tmpType = Mid(tmpOrg, 1, 1)
'Check for end of data, if so then exit do loop
If tmpType = "@" Then
Exit Do
End If
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO [Company]
(Company,Organization, Type,Glossary,Datemod) " & _
"VALUES(""" & tmpCompany & """, " & _
"""" & tmpOrg & """, " & _
"""" & tmpTypeC & """, " & _
"'" & Replace(tmpGlossary, "'", "''") & "', '" &
tmpDateMod & "') WITH OWNERACCESS OPTION"
Function fnParseData(ByVal tmpData As Variant, arySize As
Integer) As Variant()
Dim strData As String
Dim posStart As Long
Dim posEnd As Long
Dim posLen As Long
Dim strLen As Long
Dim aryTmp() As Variant
Dim i As Integer
ReDim aryTmp(arySize)
posStart = 1
strLen = Len(tmpData)
Do While posStart < strLen
posEnd = InStr(posStart, tmpData, "|", vbTextCompare)
posLen = posEnd - posStart
aryTmp(i) = Mid(tmpData, posStart, posLen)
posStart = posStart + posLen + 1
i = i + 1
Loop
fnParseData = aryTmp
End Function
Thanks in advance for any assistance!
Jerry
user to key data into using both single and double
quotes. I then take the data and create a single text
file to be used in an import/export fashion using the | as
a field separator. I use the textstream object with
Writeline and Readline properties to write and read the
data. However I run into problems when I read a line that
has the memo data because the user may have inserted a
hard line return in the field, this causes the readline to
read only part of the data, thus screwing up my UPDATE
statements. I have tried using Input # but it will not
work with the quotes so I am looking for other more
effective alternatives!
Any recommendations on a better approach would be GREATLY
Appreciated!!
I have set up a parsing function to go through and pull
out the data for each field for the update as shown below
(fnParseData). The code that calls the function follows:
tmpData = tsExportData.ReadLine
aryReturnData = fnParseData(tmpData, 5)
tmpOrg = aryReturnData(0)
tmpCompany = aryReturnData(1)
tmpDescription = aryReturnData(2)
tmpDateMod = aryReturnData(3)
tmpGlossary = aryReturnData(4)
tmpType = Mid(tmpOrg, 1, 1)
'Check for end of data, if so then exit do loop
If tmpType = "@" Then
Exit Do
End If
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO [Company]
(Company,Organization, Type,Glossary,Datemod) " & _
"VALUES(""" & tmpCompany & """, " & _
"""" & tmpOrg & """, " & _
"""" & tmpTypeC & """, " & _
"'" & Replace(tmpGlossary, "'", "''") & "', '" &
tmpDateMod & "') WITH OWNERACCESS OPTION"
Function fnParseData(ByVal tmpData As Variant, arySize As
Integer) As Variant()
Dim strData As String
Dim posStart As Long
Dim posEnd As Long
Dim posLen As Long
Dim strLen As Long
Dim aryTmp() As Variant
Dim i As Integer
ReDim aryTmp(arySize)
posStart = 1
strLen = Len(tmpData)
Do While posStart < strLen
posEnd = InStr(posStart, tmpData, "|", vbTextCompare)
posLen = posEnd - posStart
aryTmp(i) = Mid(tmpData, posStart, posLen)
posStart = posStart + posLen + 1
i = i + 1
Loop
fnParseData = aryTmp
End Function
Thanks in advance for any assistance!
Jerry