Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...
MS Access Forum / Forms Programming / May 2007

Tip: Looking for answers? Try searching our database.

DoCmd.TransferText & variables?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
LF - 15 May 2007 00:25 GMT
Why if the below line of code works with hard-coded parameter values, does it
not work with variables?

These hard-coded parameter work...
    DoCmd.TransferText acImportDelim, RFPSpecs, "copy_tblRFP", _
       "\\az25fil14\PDC Processes\RFP.CSV", True

These variable parameters do not work...
   Dim strSpec as String, strTmpTbl as String, strFilePath as String
   Dim blnRow1FldNames as Boolean
   strSpec = "RFPSpecs"
   strTmpTbl = "copy_tblRFP"
   strFilePath = "\\az25fil14\PDC Processes\RFP.CSV"
   blnRow1FldNames = true
   DoCmd.TransferText acImportDelim, strSpec, strTmpTbl, _
       strFilePath, blnRow1FldNames
Signature

LF

Reply to this Message
Ken Snell (MVP) - 15 May 2007 02:52 GMT
What does "not work" mean in this context? Do you get an error message? do
you get the wrong data imported? do the data import to the wrong table? do
the data import incorrectly (not conforming to the import specification's
parameters)?

Signature

       Ken Snell
<MS ACCESS MVP>

> Why if the below line of code works with hard-coded parameter values, does
> it
[quoted text clipped - 13 lines]
>    DoCmd.TransferText acImportDelim, strSpec, strTmpTbl, _
>        strFilePath, blnRow1FldNames
Reply to this Message
LF - 15 May 2007 17:14 GMT
Sorry for the lack of detail, I should have known better. I've narrowed my
problem down to a single variable, the import specification parameter. It
works as the actual import spec name; but when the name is passed as a
variable I get key violation errors.

Private Sub Test_Click()
   Dim strSpec As String, strTmpTbl As String, strFilePath As String,
   Dim blnRow1FldNames As Boolean
       strSpec = "RFPSpecs"
       strTmpTbl = "copy_tblRFP"
       strFilePath = "\\az25fil14\PDC Processes\RFP.CSV"
       blnRow1FldNames = True

'This works...
       DoCmd.TransferText acImportDelim, RFPSpecs, strTmpTbl, strFilePath, _
           blnRow1FldNames
'This produces an error stating that all records are lost due to key
violations...
       DoCmd.TransferText acImportDelim, strSpec, strTmpTbl, strFilePath, _
           blnRow1FldNames
End Sub

Signature

LF

> What does "not work" mean in this context? Do you get an error message? do
> you get the wrong data imported? do the data import to the wrong table? do
> the data import incorrectly (not conforming to the import specification's
> parameters)?
>         Ken Snell
> <MS ACCESS MVP>
Reply to this Message
LF - 15 May 2007 17:16 GMT
Sorry for the lack of detail, I should have known better. I've narrowed my
problem down to a single variable, the import specification parameter. It
works as the actual import spec name; but when the name is passed as a
variable I get key violation errors.

Private Sub Test_Click()
   Dim strSpec As String, strTmpTbl As String, strFilePath As String,
   Dim blnRow1FldNames As Boolean
       strSpec = "RFPSpecs"
       strTmpTbl = "copy_tblRFP"
       strFilePath = "\\az25fil14\PDC Processes\RFP.CSV"
       blnRow1FldNames = True

'This works...
       DoCmd.TransferText acImportDelim, RFPSpecs, strTmpTbl, strFilePath, _
           blnRow1FldNames
'This produces an error stating that all records are lost due to key
violations...
       DoCmd.TransferText acImportDelim, strSpec, strTmpTbl, strFilePath, _
           blnRow1FldNames
End Sub

Signature

LF

> What does "not work" mean in this context? Do you get an error message? do
> you get the wrong data imported? do the data import to the wrong table? do
> the data import incorrectly (not conforming to the import specification's
> parameters)?
>         Ken Snell
> <MS ACCESS MVP>
Reply to this Message
Ken Snell (MVP) - 16 May 2007 00:09 GMT
Did the copy_tblRFP table already have data in it when you tried to run the
code? The error message that you're getting suggests to me that the table
already contains records that have primary key field values that match the
text file's records' primary key values.

Believe me, there should be no difference in operational results if you
manually import or do the import from code -- assuming that both operations
are starting with exactly the same table data (preferably, an empty table).
Signature


       Ken Snell
<MS ACCESS MVP>

> Sorry for the lack of detail, I should have known better. I've narrowed my
> problem down to a single variable, the import specification parameter. It
[quoted text clipped - 28 lines]
>>         Ken Snell
>> <MS ACCESS MVP>
Reply to this Message
LF - 16 May 2007 01:52 GMT
Yes, the table is empty upon testing the code.
Signature

LF

> Did the copy_tblRFP table already have data in it when you tried to run the
> code? The error message that you're getting suggests to me that the table
[quoted text clipped - 36 lines]
> >>         Ken Snell
> >> <MS ACCESS MVP>
Reply to this Message
Ken Snell (MVP) - 16 May 2007 03:50 GMT
I'm amazed that this method works:

       DoCmd.TransferText acImportDelim, RFPSpecs, strTmpTbl, strFilePath,
_
           blnRow1FldNames

You're passing the import specification as a variable named RFPSpecs, which
obviously must contain some string value (or more likely, because you don't
Dim it and you don't assign it a value, it contains a Null value, which the
TransferText method then should ignore and it then will use a default import
description -- namely, comma-delimited with " character as text qualifier).
You're not passing a string value of "RFPSpecs".

Try your code this way and see if it works as well:

       DoCmd.TransferText acImportDelim, , strTmpTbl, strFilePath, _
           blnRow1FldNames

If the above works the same way your "this works" method works, then my
guess is probably accurate. That would mean that the "RFPSpecs" import
specification is not correctly designed to match the data in your text file
and/or to match the structure of your "copy_tblRFP" table. Take a good look
at it and verify that the spec is what you think it should be.
Signature


       Ken Snell
<MS ACCESS MVP>

> Yes, the table is empty upon testing the code.
>
[quoted text clipped - 49 lines]
>> >>         Ken Snell
>> >> <MS ACCESS MVP>
Reply to this Message
LF - 16 May 2007 20:10 GMT
I am declaring and assigned values to the variables as shown earlier in this
thread as the "Test_Click" subprocedure. I only included the single problem
line of code though in posts after that. My [RFPSpecs] import spec does also
match the configuration of my [copy_tblRFP] import table and it works as
expected when the actual import spec name is used as the import spec
parameter.

I found that at least part of my problem is explained in the MS Knowledge
Base artilcle 88415 "You may receive an error message when you try to insert
a new record in a table that contains an Autonumber field in Access 2007 or
Access 2003". As the article suggests, I compacted my database and reset the
autonumber seed of my import table. Doing so rid my key violation error but
now results in the error message below in all three cases (using RFPSpecs, or
strSpec, or nothing as the import spec parameter.

   Runtime-time error '31519'. You can not import this file.

I appreciate your continued help.
Signature

LF

> I'm amazed that this method works:
>         DoCmd.TransferText acImportDelim, RFPSpecs, strTmpTbl, strFilePath, _
[quoted text clipped - 48 lines]
> >> >>         Ken Snell
> >> >> <MS ACCESS MVP>
Reply to this Message
Ken Snell (MVP) - 16 May 2007 20:59 GMT
Is the autonumber field the first field in the table? If yes, move it to the
last field in the table. I assume that there is no corresponding field in
the text file for the autonumber field, and that the table has one field
more (the autonumber field) than the text file; if yes, then the import
won't try to write data into that field but will let the table insert the
sequential number into the field as each record is imported.

If this doesn't fix the problem, then I request that you post the table's
structure, a few sample rows of data from the text file, and the details of
the import specification. That will help us to see where a discrepancy might
sit.

I assure you that an import specification argument will not depend upon
whether you use an actual string text or you use a variable that contains
that string text.
Signature


       Ken Snell
<MS ACCESS MVP>

>I am declaring and assigned values to the variables as shown earlier in
>this
[quoted text clipped - 84 lines]
>> >> >>         Ken Snell
>> >> >> <MS ACCESS MVP>
Reply to this Message
LF - 17 May 2007 02:05 GMT
See responses below preceeded by ... I apologize for the length; I tried to
be concise as possable.
Signature

LF

> Is the autonumber field the first field in the table?
...Yes

> If yes, move it to the last field in the table.
...I tried this, and got the same "You can't import this file" error.

>I assume that there is no corresponding field in the text file for the autonumber
...Correct

> and that the table has one field more (the autonumber field) than the text file;
... The table has 8 fields (5 for which MySysIMEXColumns.SkipColumn = true)
... The text file has 4 fields (for which MySysIMEXColumns.SkipColumn = false)

> if yes, then the import  won't try to write data into that field but will let the table
> insert the sequential number into the field as each record is imported.
... I got this result, with the autonumber field as the first or last table
field,
... only if I remove the other 4 SkipColumn = True fields from the import
spec.

> If this doesn't fix the problem,
...Yes & No; the fields in my imported text files will not always be
consistant;
...which is why I set the spec to include all 12 fields thinking that each
...could be toggled on/off thru the SkipColumn field. I now realize that  
...that the SkipColumn applies to the text file fields not import spec fields.

> then I request that you post the table's structure,
... | RFPid: AutoNumber | RFPdoc: Text | RFPnum: Text | RFPsort: Text
... | RFPsec: Text | RFPpara: Memo | Annotation: Memo | Response: Memo
... | Compliance: Text | Risk: Text | UpdateBy: Text | UpdateWhen: Date/Time

>  a few sample rows of data from the text file,
... "RFPdoc", "RFPnum", "RFPsec", "RFPpara"
... "SOW", "1.1.2", "Introduction", "The solution shall provide an improved
widget."
... "SOW", "1.1.2", "Introduction", "The soultion shall reduce cycle time."
... "SOW", "1.3", "System Architecture", " The current system should be
replaced."
... "L", "2.4", "Proposal Submital", "Submitted documents shall include..."
... "L", "2.5", "Submital Formats", "The page margins should not exceed 1
inch."

> and the details of  the import specification.
... MSysIMEXSpecs:
... | DateDelim=/ | DateFourDigitYear=-1 | DateLeadingZeros=-1
... | DateOrder=2 | DecimalPoint=. | FieldSepartor=, | FileType=437
... | SpecID=34 | SpecName=RFPSpecs | SpecType=1 |StartRow=1
... | TextDelim=: |TimeDelim=:

... MSysIMEXColumns:
... Attributes|DataType|FieldName|IndexType|SkipColumn|SpecID|Start|Width|
... 0            |4            |RFPid       |1             |-1              
|34      |1     |11
... 0            |10          |RFPdoc     |0             |0                
|34      |12   |10
... 0            |10          |RFPnum    |1             |0                
|34      |22   |25
... 0            |10          |RFPsort     |0             |-1              
|34      |26    |3
... 0            |10          |RFPsec      |0             |0              
|34      |28    |255
... 0            |10          |RFPpara    |0             |0              
|34      |278  |32000
... 0            |10          |Annotation |0             |-1              
|34      |391  |32000
... 0            |10          |Response  |0              |-1              
|34      |504  |32000
... 0            |10          |Comply      |0             |-1              
|34      |617  |10
... 0            |10          |Risk           |0             |-1            
|34      |627  |5
... 0            |10          |UpdateBy    |0             |-1              
|34      |702  |30
... 0            |8            |UpdateWhen|0             |-1              
|34      |732 |19

> That will help us to see where discrepancy might sit.
... I have an editable parent\child subform based on the above two tables.
... The child form also has an [MSysIMEXColumns].[Import] checkbox field.
... The parent form also has an Import button that is intended to feed user-
... selected values to the DoCmd.TransferText variable parameters.

> I assure you that an import specification argument will not depend upon
> whether you use an actual string text or you use a variable that contains
> that string text.
... Agreed, there was nothing wrong with my line of code that produced the
... error. I was mislead in thinking so because the hard-coded spec parameter
... oddly worked prior to compacting and resetting the autonumber seed.
... I'm thinking this theory will still work with the addition of
programatically building
... each import spec on-the-fly; as opposed to using default specs based on
the
... entire import table fields? or is there an easier way to go about it?
Reply to this Message
Ken Snell (MVP) - 17 May 2007 04:28 GMT
An interesting challenge -- the ability to import various text files, but
where each text file may have different fields (out of the total 12
possible). While one might eventually find a way to make something work
using the TransferText option, I would be inclined to forgo that approach
and instead use programming to open the file, identify the fields that are
in the file by reading in the first line from the text file, open a
recordset to your table, and then read each line of data from the text file
and parse it to variables that represent the different fields and then add a
new record to the recordset by writing the variables' values to the
recordset's appropriate fields.

Is this of interest? If yes, I can guide you through how to set up such a
process.
Signature


       Ken Snell
<MS ACCESS MVP>

> See responses below preceeded by ... I apologize for the length; I tried
> to
[quoted text clipped - 107 lines]
> the
> ... entire import table fields? or is there an easier way to go about it?
Reply to this Message
LF - 17 May 2007 19:14 GMT
Yes, please; I welcome and appreciate your help. I am on the hook to have
this functionality completed by the 22nd (next Tuesday).

The ability to import various text files where each may have different
fields is only a third of the whole story. The import file could either be a
MS-Word table or MS-Excel spreadsheet relating to any one of six different
MS-Access 2003 tables. The user also needs to preview the imported table and
run additional code automation (that I previously ran manually) to populate
several missing mandated field values required for other database forms.

The code behind my Import button currently feeds the user-selected form
values to an import function. The import function below currently interprets
and converts a user-selected .doc or .xls file to CSV syntax, and copies the
applicable table structure to a temporary table. The function is then suppose
to import the converted text file into the tempoary table (which is where I'm
stuck). Then open a secondary form where the user can preview the temp table
to fix any import errors and initiate code that applies missing mandated
field values before importing the temp table records into the final table.

Is my form design based on the MSysIMEXSpecs\Columns sound?
Is my import function theory sound?
If so, and if you can help me get passed the text file import problem, I
think I can finish the rest on my own. Thank you for your patience.

Function fncImport(strSpec As String, blnRow1FldNames As Boolean) As Boolean
Dim strFilePath As String, strFileExt As String
Dim strImport As String, strTmpTbl As String, strTbl As String
Dim strMsg As String, intMsg As Integer, strSQL As String

'On Error GoTo Err_Handler

' File>Open User dialogbox
   strFilePath = GetOpenFile("Please select an INPUT file...", 5)

'Trap and handle valid/invalid filetype instances
   strFileExt = Right(strFilePath, 4)
'Convert Word.doc\Excel.xls to CSV, verify CSV.txt, or return invalid
filetypes
   If strFileExt = ".doc" Or strFileExt = ".xls" Then
       strImport = fncCSV(strFilePath, strFileExt)
       If Len(strImport) = 0 Then GoTo Exit_Handler
       strFilePath = Mid(strFilePath, 1, Len(strFilePath) - Len(strImport))
       strFilePath = """" & strFilePath & strImport & """"
   ElseIf strFileExt = ".txt" Or strFileExt = ".csv" Then
       strMsg = "Do not proceed unless this text file conforms to comma
seperated_
                     values where..."
       strMsg = strMsg & vbCrLf & "* field columns are seperated by commas"
       strMsg = strMsg & vbCrLf & "* record rows are seperated by paragraph_
                                                  returns"
       strMsg = strMsg & vbCrLf & "* textual datatype cells are surrounded
by_
                                                 quotes"
       strMsg = strMsg & vbCrLf & "* numeral datatypes are not surrounded by_
                                                quotes"
       strMsg = strMsg & vbCrLf & vbCrLf & "NOTE: "
       strMsg = strMsg & "A single MS-Word.doc table or MS-Excell.xls_
                                    spreadsheet is also importable."
       intMsg = MsgBox(strMsg, vbOKCancel, "CSV Syntax Verification")
       If intMsg = 0 Then GoTo Exit_Handler
   Else
       strMsg = "This file is not an importable filetype."
       strMsg = strMsg & vbCrLf & vbCrLf & "Valid filetype inputs include:"
       strMsg = strMsg & "* MS-Word.doc"
       strMsg = strMsg & vbCrLf & "* MS-Excel.xls"
       strMsg = strMsg & vbCrLf & "* plain text.txt"
       MsgBox strMsg, vbOKOnly, "Invalid FileType"
       GoTo Exit_Handler
   End If
'Copy temp table structure
   strTbl = "tbl" & UCase(Mid(strSpec, 1, InStr(1, strSpec, "Specs") - 1))
   strTmpTbl = "copy_" & strTbl
   strSource = CurrentDb.TableDefs(strTbl).Connect
   strSource = Mid(strSource, InStr(1, strSource, "=") + 1)
   DoCmd.TransferDatabase acImport, "Microsoft Access", strSource, acTable,_
                                         strTbl, strTmpTbl, True
'____Import into temp table
   'DoCmd.TransferText acImportDelim, strSpec, strTmpTbl, strFilePath,_
                                  blnRow1FldNames
   '> instead open the file, identify the fields in the file by reading in
the first line,
   '> open a recordset to your table, and read each line of data from the
text file,
   '> parse variables that represent the different fields
   '> add a record to the recordset by writing variable values to
appropriate fields.

'____Preview: fill-in missing mandated temp table values and handle import
errors
   DoCmd.OpenTable strTmpTbl
   DoCmd.OpenForm fdlgSchema
   DoCmd.Close acTable, strTmpTbl, acSaveYes
'Append\delete temp table
   strSQL = "INSERT INTO " & strTbl & " SELECT " & strTmpTbl & ".* FROM " &_
                 strTmpTbl & ";"
   DoCmd.RunSQL strSQL
   DoCmd.SetWarnings False
       DoCmd.DeleteObject acTable, strTmpTbl
   DoCmd.SetWarnings True
'Return import status
   fncImport = True

Exit_Handler:
   Exit Function

Err_Handler:
   Msg = "Error #: " & Format$(Err.Number) & vbCrLf & Err.Description
   MsgBox Msg, vbOKOnly, "Import Error..."
   fncImport = False
   Resume Exit_Handler
End Function

--
LF
Reply to this Message
Ken Snell (MVP) - 18 May 2007 04:59 GMT
I won't have a chance tonite to put some code together for you -- haven't
written code for this type of setup before, so it'll take me a bit of time
to draft some code steps. Probably will be sometime this weekend before I
can do this, and that may be too late for your current timing.

But here's the concept that I have in mind:

1) Open the text file using the Open statement in VBA:
           Open "Path\Folder\FileName.txt" For Input As #1

2) Read the first line of data in the textfile as a long string into a
string variable, then use Split function to parse the field names into a
variant variable (which will make it an array).

3) Open a recordset to the table that is to get the data.

4) Begin a loop for handling the text file's data -- needs to end when EOF
has been reached for text file.

5) Read a line of data from the textfile into a single string variable, then
use Split function to parse the values into a variant variable (another
array).

7) Start to add new record to recordset.

8) Begin a loop through the array from LBound to UBound values of the first
variant variable (see 2)).

9) Use value from first array variable as name of recordset field to get the
value, and use value from second array variable as value for that recordset
field:
           For lngFields = LBound(varArray1) To UBound(varArray2)
               rst.Fields(varArray1(lngFields)).Value =
varArray2(lngFields)
           Next lngFields

10) End of loop begun in 8).

11) Update the recordset to finish adding the new record.

12) End of loop begun in 4).

13) Close recordset variable and close text file.

Perhaps this will help you get a headstart on me.
Signature


       Ken Snell
<MS ACCESS MVP>

> Yes, please; I welcome and appreciate your help. I am on the hook to have
> this functionality completed by the 22nd (next Tuesday).
[quoted text clipped - 126 lines]
> --
> LF
Reply to this Message
LF - 19 May 2007 07:56 GMT
Well, I'm not familiar with reading "Text files" or "Arrarys" but below is my
stab at coding your suggested concept. I place ??? in three instances where I
was unsure of how to go about assigning the variable value.

> > Is my form design based on the MSysIMEXSpecs\Columns sound?
> > Is my import function theory sound?

Sub btnTest_Click (strFilePath as String, strTbl as String)
   Dim strColumns as String, strRow as String
   Dim varColumn as Variant, varRow as Variant
   Dim lngDelimCount as Long, i as Integer
   Dim db as DAO.database, rs as DAO.recordset, strSQL as String

'__1) Open the text file.
   Open strTbl For Input As #1
'__2) Get column names from the first text file paragraph.
   strColumns = ???
'__3) Parse column names into an array of field names.
   lngDelimCount = ???
   varColumn = Split (strColumns, 1, lngDelimCount, 1)
'__4) Open a recordset to the table that will receive the data.
   Set db = CurrentDb
   strSQL = "SELECT * FROM [" & srtTbl & "];"
   Set rs = db.OpenRecordset (strSQL, dbOpenDynaset)
'__5) Read each text file paragraph until the end of file is reached.
   Do While Not rs.EOF
       strRow = ???
'__6)  Parse rows into an array of field values.
       varRow = Split (strRow, 1, lngDelimCount, 1)
'__7) Add a new record to the recordset.
       rs.Edit
       rs.AddNew
'__8) Write row field values to the recordset.
       For lngDelimCount = LBound(varColumn) To UBound(varRow)
           rs.Fields(varColumn(lngDelimCount)).Value = varRow(lngDelimCount)
       Next lngDelimCount
'__9) End the second loop.
       Next
'__10) Update the recordset.
       rs.Update
'__11) End the first loop
   Loop
'__12) Close the recordset.
   rs.Close
   rs = Nothing
   db.Close
   Set db - Nothing
'__13) Close the text file.
   Close strFilePath For Input
End Sub
Reply to this Message
Ken Snell (MVP) - 19 May 2007 20:53 GMT
You got pretty close. Here is my modification to your code; I've not tested
it, so there may be a bug in it You'll need to change the line that sets the
value of the strTbl variable. Let me know of your results.

--
Sub btnTest_Click (strFilePath as String, strTbl as String)
   Dim strColumns as String, strRow as String
   Dim varColumn as Variant, varRow as Variant
   Dim lngDelimCount as Long, i as Integer, intF as Integer
   Dim db as DAO.database, rs as DAO.recordset, strSQL as String

   Const strDelim As String = ","

   strTbl = "PathToFileToImport\FileName.txt"
   intF = FreeFile()

'__1) Open the text file.
   Open strTbl For Input As #intF
'__2) Get column names from the first text file paragraph.
   Line Input #intF, strColumns
'__3) Parse column names into an array of field names.
   varColumn = Split (strColumns, strDelim)
'__4) Open a recordset to the table that will receive the data.
   Set db = CurrentDb
   strSQL = "SELECT * FROM [" & srtTbl & "];"
   Set rs = db.OpenRecordset (strSQL, dbOpenDynaset, dbAppendOnly)
'__5) Read each text file paragraph until the end of file is reached.
   Do While EOF(intF) = False
       Line Input #intF, strRow
'__6)  Parse rows into an array of field values.
       varRow = Split (strRow, strDelim)
'__7) Add a new record to the recordset.
       rs.AddNew
'__8) Write row field values to the recordset.
           For lngDelimCount = LBound(varColumn) To UBound(varColumn)
               rs.Fields(varColumn(lngDelimCount)).Value =
varRow(lngDelimCount)
           Next lngDelimCount
'__9) End the second loop.
'__10) Update the recordset.
       rs.Update
'__11) End the first loop
   Loop
'__12) Close the recordset.
   rs.Close
   Set rs = Nothing
   db.Close
   Set db = Nothing
'__13) Close the text file.
   Close #intF
End Sub

Signature

       Ken Snell
<MS ACCESS MVP>

> Well, I'm not familiar with reading "Text files" or "Arrarys" but below is
> my
[quoted text clipped - 49 lines]
>    Close strFilePath For Input
> End Sub
Reply to this Message
Ken Snell (MVP) - 19 May 2007 21:06 GMT
Sorry -- I overlooked that you're bringing the values of strFilePath and
strTbl to the sub. Here is corrected code:

Sub btnTest_Click (strFilePath as String, strTbl as String)
   Dim strColumns as String, strRow as String
   Dim varColumn as Variant, varRow as Variant
   Dim lngDelimCount as Long, i as Integer, intF as Integer
   Dim db as DAO.database, rs as DAO.recordset, strSQL as String

   Const strDelim As String = ","

   intF = FreeFile()

'__1) Open the text file.
   Open strFilePath For Input As #intF
'__2) Get column names from the first text file paragraph.
   Line Input #intF, strColumns
'__3) Parse column names into an array of field names.
   varColumn = Split (strColumns, strDelim)
'__4) Open a recordset to the table that will receive the data.
   Set db = CurrentDb
   strSQL = "SELECT * FROM [" & srtTbl & "];"
   Set rs = db.OpenRecordset (strSQL, dbOpenDynaset, dbAppendOnly)
'__5) Read each text file paragraph until the end of file is reached.
   Do While EOF(intF) = False
       Line Input #intF, strRow
'__6)  Parse rows into an array of field values.
       varRow = Split (strRow, strDelim)
'__7) Add a new record to the recordset.
       rs.AddNew
'__8) Write row field values to the recordset.
           For lngDelimCount = LBound(varColumn) To UBound(varColumn)
               rs.Fields(varColumn(lngDelimCount)).Value =
varRow(lngDelimCount)
           Next lngDelimCount
'__9) End the second loop.
'__10) Update the recordset.
       rs.Update
'__11) End the first loop
   Loop
'__12) Close the recordset.
   rs.Close
   Set rs = Nothing
   db.Close
   Set db = Nothing
'__13) Close the text file.
   Close #intF
End Sub

Signature

       Ken Snell
<MS ACCESS MVP>

> You got pretty close. Here is my modification to your code; I've not
> tested it, so there may be a bug in it You'll need to change the line that
> sets the value of the strTbl variable. Let me know of your results.

< snipped >

>> Well, I'm not familiar with reading "Text files" or "Arrarys" but below
>> is my
[quoted text clipped - 49 lines]
>>    Close strFilePath For Input
>> End Sub
Reply to this Message
LF - 21 May 2007 18:12 GMT
Almost there, although I can see in the VB Locals pane that it returns the
expected varColumn, varRow, & lngDelimCount variables. it errors "Item not
found in this collection" at the rs.Fields Value property in step 8.

One of the fields datatype is Memo; does that matter?

Signature

LF

> Sorry -- I overlooked that you're bringing the values of strFilePath and
> strTbl to the sub. Here is corrected code:
[quoted text clipped - 104 lines]
> >>    Close strFilePath For Input
> >> End Sub
Reply to this Message
Ken Snell (MVP) - 21 May 2007 18:32 GMT
When you get the error, hold cursor over the varColumn variable and note
what the value of that variable is. Is that field in the table that's the
basis of the recordset?

Signature

       Ken Snell
<MS ACCESS MVP>

> Almost there, although I can see in the VB Locals pane that it returns the
> expected varColumn, varRow, & lngDelimCount variables. it errors "Item not
[quoted text clipped - 50 lines]
>>     Close #intF
>> End Sub
Reply to this Message
LF - 21 May 2007 19:25 GMT
Yes the hover value is in the recordset table, but I noticed that the hover
value is surrounded by double quotes> do those quotes need to be stripped out
first?

I tried two seperate text files to rule out that the content was the source
of the problem. I also tried converting the variant column to a string and
got the same error in both instances.
   strCol = varColumn(lngDelimCount))
   rs.Fields(strCol).Value = varRow(lngDelimCount)

Signature

LF

> When you get the error, hold cursor over the varColumn variable and note
> what the value of that variable is. Is that field in the table that's the
[quoted text clipped - 54 lines]
> >>     Close #intF
> >> End Sub
Reply to this Message
Ken Snell (MVP) - 21 May 2007 20:06 GMT
If the hover value is this:
   "string"

then it's correct.

If it's this:
   ""string""

then the value in the variable needs to have the leading and trailing "
characters stripped out. This can be done this way, assuming that there
would not be a field name beginning with or ending with " character:

           For lngDelimCount = LBound(varColumn) To UBound(varColumn)
               If Left(varColumn(lngDelimCount), 1) = Chr(34) And _
                   Right(varColumn(lngDelimCount), 1) = Chr(34) Then _
                   varColumn(lngDelimCount) = Mid(varColumn(lngDelimCount),
_
                   2, Len(varColumn(lngDelimCount)) - 2)
               rs.Fields(varColumn(lngDelimCount)).Value =
varRow(lngDelimCount)
           Next lngDelimCount

Signature

       Ken Snell
<MS ACCESS MVP>

> Yes the hover value is in the recordset table, but I noticed that the
> hover
[quoted text clipped - 70 lines]
>> >>     Close #intF
>> >> End Sub
Reply to this Message
LF - 21 May 2007 20:57 GMT
OK, that rids the "item not found in this collection" error.
And the code will now import records; but it still errors
when it hits memo data in the text file.
   Run-Time Error 3163: The field is too small to accept
   the amount of data you attempted to add; try inserting
   or pasting less data.
I assume that will require an additional if statement using the Append Chunk
function?
Signature

LF

> If the hover value is this:
>     "string"
[quoted text clipped - 92 lines]
> >> >>     Close #intF
> >> >> End Sub
Reply to this Message
LF - 21 May 2007 21:03 GMT
Forgot to add this to my previous reply. It appears to grab all of the memo
content, but it tries to insert it into the preceeding field which is not a
memo datatype; it also does not strip the quotes from the value.
Signature

LF

Reply to this Message
Ken Snell (MVP) - 21 May 2007 22:40 GMT
Memo fields should not be a problem for this method/code. And, if the memo
field is in the appropriate location in the record (i.e., corresponding to
the field name in first row of text file that is the memo type), it
shouldn't be trying to put it into the previous field in the recordset.

I assume that your previously posted table and data info are still valid
here:

Table:
... | RFPid: AutoNumber | RFPdoc: Text | RFPnum: Text | RFPsort: Text
... | RFPsec: Text | RFPpara: Memo | Annotation: Memo | Response: Memo
... | Compliance: Text | Risk: Text | UpdateBy: Text | UpdateWhen: Date/Time

 a few sample rows of data from the text file,
... "RFPdoc", "RFPnum", "RFPsec", "RFPpara"
... "SOW", "1.1.2", "Introduction", "The solution shall provide an improved
widget."
... "SOW", "1.1.2", "Introduction", "The soultion shall reduce cycle time."
... "SOW", "1.3", "System Architecture", " The current system should be
replaced."
... "L", "2.4", "Proposal Submital", "Submitted documents shall include..."
... "L", "2.5", "Submital Formats", "The page margins should not exceed 1
inch."

By chance, do the data in the memo fields or other fields contain commas
(your field delimiter)? If yes, then the code is truncating the data because
of the Split function. So we need to "protect" the commas until after the
Split function, then "unprotect" them afterwards. The code to do this is a
bit tricky; it would be easier if you could use a different field delimiter
(such as | character). Is this possible?

Try this code - I've also made changes in the " character stripping to
remove the " characters from varRow values too -- I've not added the
"protection / unprotection" of the comma delimiter at this point, awaiting
your reply about whether you can use a different delimiter -- if not, post
back and I'll put together some code for you to do this (would do sometime
this evening):

Sub btnTest_Click (strFilePath as String, strTbl as String)
   Dim strColumns as String, strRow as String
   Dim varColumn as Variant, varRow as Variant
   Dim lngDelimCount as Long, i as Integer, intF as Integer
   Dim db as DAO.database, rs as DAO.recordset, strSQL as String

   Const strDelim As String = ","

   intF = FreeFile()

'__1) Open the text file.
   Open strFilePath For Input As #intF
'__2) Get column names from the first text file paragraph.
   Line Input #intF, strColumns
'__3) Parse column names into an array of field names.
   varColumn = Split (strColumns, strDelim)
   For lngDelimCount = LBound(varColumn) To UBound(varColumn)
       If Left(Trim(varColumn(lngDelimCount)), 1) = Chr(34) And _
           Right(Trim(varColumn(lngDelimCount)), 1) = Chr(34) Then _
           varColumn(lngDelimCount) = Mid(Trim(varColumn(lngDelimCount)), _
           2, Len(Trim(varColumn(lngDelimCount))) - 2)
       Next lngDelimCount
'__4) Open a recordset to the table that will receive the data.
   Set db = CurrentDb
   strSQL = "SELECT * FROM [" & srtTbl & "];"
   Set rs = db.OpenRecordset (strSQL, dbOpenDynaset, dbAppendOnly)
'__5) Read each text file paragraph until the end of file is reached.
   Do While EOF(intF) = False
       Line Input #intF, strRow
'__6)  Parse rows into an array of field values.
       varRow = Split (strRow, strDelim)
'__7) Add a new record to the recordset.
       rs.AddNew
'__8) Write row field values to the recordset.
           For lngDelimCount = LBound(varColumn) To UBound(varColumn)
               If Left(Trim(varRow(lngDelimCount)), 1) = Chr(34) And _
                   Right(Trim(varRow(lngDelimCount)), 1) = Chr(34) Then _
                   varRow(lngDelimCount) = Mid(Trim(varRow(lngDelimCount)),
_
                   2, Len(Trim(varRow(lngDelimCount))) - 2)
               rs.Fields(varColumn(lngDelimCount)).Value =
varRow(lngDelimCount)
           Next lngDelimCount
'__9) End the second loop.
'__10) Update the recordset.
       rs.Update
'__11) End the first loop
   Loop
'__12) Close the recordset.
   rs.Close
   Set rs = Nothing
   db.Close
   Set db = Nothing
'__13) Close the text file.
   Close #intF
End Sub

Signature

       Ken Snell
<MS ACCESS MVP>

> Forgot to add this to my previous reply. It appears to grab all of the
> memo
> content, but it tries to insert it into the preceeding field which is not
> a
> memo datatype; it also does not strip the quotes from the value.
Reply to this Message
LF - 22 May 2007 01:31 GMT
Yes, the memo field is in the appropriate location in the record.

Yes, the previously posted table and data info are still valid.

Yes, the data in the memo fields or other fields contain commas;
as well as | charachters. I went with CSV syntax due to the
originating source file either being MS-Word or MS-Excell.
Also because my original DoCmd.TransferText used import specs
that support CSV delimits.

Can the delimitor constant accomodate a multi-character delimitor
such as ", " (i.e. quote, comma, space quote)?

Your latest code modification removes all of the surrounding quotes
except for the beginning of the the last field value.

I appreciate your patience with the back and forth regarding this issue.

--
LF

> Sub btnTest_Click (strFilePath as String, strTbl as String)
>     Dim strColumns as String, strRow as String
[quoted text clipped - 56 lines]
> > memo content, but it tries to insert it into the preceeding field which
> > is not a memo datatype; it also does not strip the quotes from the value.
Reply to this Message
Ken Snell (MVP) - 22 May 2007 04:22 GMT
No, the Split function can accommodate only a single-character delimiter, so
", " won't work. The reason the " character remains at beginning of the memo
field is because it's being truncated and the string doesn't end with a "
character (the code looks for preceding and trailing " characters).

Let me have a bit of time here and see if I can write code to protect /
unprotect the comma character embedded within the strings.
Signature


       Ken Snell
<MS ACCESS MVP>

> Yes, the memo field is in the appropriate location in the record.
>
[quoted text clipped - 81 lines]
>> > is not a memo datatype; it also does not strip the quotes from the
>> > value.
Reply to this Message
Ken Snell (MVP) - 22 May 2007 04:54 GMT
OK - I've written a function (DelimProtect) that will protect or unprotect
an embedded delimiter within a text string. To protect an embedded
delimiter, it converts the specified delimiter character to ASCII character
7 (highly unlikely that any text string from your sources is going to
contain this character); to unprotect, it converts any ASCII character 7
back to the delimiter character. The function is at the end of the following
code; put it in the same module where your original subroutine is located.

Sub btnTest_Click (strFilePath as String, strTbl as String)
   Dim strColumns as String, strRow as String
   Dim varColumn as Variant, varRow as Variant
   Dim lngDelimCount as Long, i as Integer, intF as Integer
   Dim db as DAO.database, rs as DAO.recordset, strSQL as String

   Const strDelim As String = ","

   intF = FreeFile()

'__1) Open the text file.
   Open strFilePath For Input As #intF
'__2) Get column names from the first text file paragraph.
   Line Input #intF, strColumns
'_2a) Protect embedded delimiter characters
   strColumns = DelimProtect(strColumns, strDelim, True)
'__3) Parse column names into an array of field names.
   varColumn = Split (strColumns, strDelim)
'_3a) Unprotect embedded delimiter characters
   strColumns = DelimProtect(strColumns, strDelim, False)
   For lngDelimCount = LBound(varColumn) To UBound(varColumn)
'_3b) Unprotect embedded delimiter characters
       varColumn(lngDelimCount) = DelimProtect(varColumn(lngDelimCount),
strDelim, False)
       If Left(Trim(varColumn(lngDelimCount)), 1) = Chr(34) And _
           Right(Trim(varColumn(lngDelimCount)), 1) = Chr(34) Then _
           varColumn(lngDelimCount) = Mid(Trim(varColumn(lngDelimCount)), _
           2, Len(Trim(varColumn(lngDelimCount))) - 2)
       Next lngDelimCount
'__4) Open a recordset to the table that will receive the data.
   Set db = CurrentDb
   strSQL = "SELECT * FROM [" & strTbl & "];"
   Set rs = db.OpenRecordset (strSQL, dbOpenDynaset, dbAppendOnly)
'__5) Read each text file paragraph until the end of file is reached.
   Do While EOF(intF) = False
       Line Input #intF, strRow
'_5a) Protect embedded delimiter characters
       strRow = DelimProtect(strRow, strDelim, True)
'__6)  Parse rows into an array of field values.
       varRow = Split (strRow, strDelim)
'__7) Add a new record to the recordset.
       rs.AddNew
'__8) Write row field values to the recordset.
           For lngDelimCount = LBound(varColumn) To UBound(varColumn)
'_8a) Unprotect embedded delimiter characters
               varRow(lngDelimCount) = DelimProtect(varRow(lngDelimCount),
strDelim, False)
               If Left(Trim(varRow(lngDelimCount)), 1) = Chr(34) And _
                   Right(Trim(varRow(lngDelimCount)), 1) = Chr(34) Then _
                   varRow(lngDelimCount) = Mid(Trim(varRow(lngDelimCount)),
_
                   2, Len(Trim(varRow(lngDelimCount))) - 2)
               rs.Fields(varColumn(lngDelimCount)).Value =
varRow(lngDelimCount)
           Next lngDelimCount
'__9) End the second loop.
'__10) Update the recordset.
       rs.Update
'__11) End the first loop
   Loop
'__12) Close the recordset.
   rs.Close
   Set rs = Nothing
   db.Close
   Set db = Nothing
'__13) Close the text file.
   Close #intF
End Sub

Public Function DelimProtect(strString As Variant, strDelim As String,
blnProtect As Boolean) As Variant

Dim blnInString As Boolean
Dim lngLen As Long, lngLoop As Long
Dim strProtect As String, strChar As String
Dim strNewString As String

strProtect = Chr(7)
lngLen = Len(strString)
strNewString = ""

If blnProtect = True Then
   lngLoop = 1
   Do While lngLoop <= lngLen
       strChar = Mid(strString, lngLoop, 1)
       If strChar = Chr(34) Then
           If blnInString = True Then
               If Mid(strString, lngLoop + 1, 1) = Chr(34) Then
                   strNewString = strNewString & strChar
                   lngLoop = lngLoop + 1
                   strChar = Mid(strString, lngLoop, 1)
               ElseIf Mid(strString, lngLoop + 1, 1) = "," Then
                   blnInString = False
               End If
           Else
               blnInString = True
           End If
       ElseIf strChar = strDelim Then
           If blnInString = True Then strChar = strProtect
       End If
       strNewString = strNewString & strChar
       lngLoop = lngLoop + 1
   Loop
Else
   For lngLoop = 1 To lngLen
       strChar = Mid(strString, lngLoop, 1)
       If strChar = strProtect Then strChar = strDelim
       strNewString = strNewString & strChar
   Next lngLoop
End If

DelimProtect = strNewString

Exit Function
End Function

Signature

       Ken Snell
<MS ACCESS MVP>

> No, the Split function can accommodate only a single-character delimiter,
> so ", " won't work. The reason the " character remains at beginning of the
[quoted text clipped - 89 lines]
>>> > is not a memo datatype; it also does not strip the quotes from the
>>> > value.
Reply to this Message
Ken Snell (MVP) - 22 May 2007 05:14 GMT
Rats -- typo in the code -- here is corrected copy:

Sub btnTest_Click (strFilePath as String, strTbl as String)
   Dim strColumns as String, strRow as String
   Dim varColumn as Variant, varRow as Variant
   Dim lngDelimCount as Long, i as Integer, intF as Integer
   Dim db as DAO.database, rs as DAO.recordset, strSQL as String

   Const strDelim As String = ","

   intF = FreeFile()

'__1) Open the text file.
   Open strFilePath For Input As #intF
'__2) Get column names from the first text file paragraph.
   Line Input #intF, strColumns
'_2a) Protect embedded delimiter characters
   strColumns = DelimProtect(strColumns, strDelim, True)
'__3) Parse column names into an array of field names.
   varColumn = Split (strColumns, strDelim)
   For lngDelimCount = LBound(varColumn) To UBound(varColumn)
'_3a) Unprotect embedded delimiter characters
       varColumn(lngDelimCount) = DelimProtect(varColumn(lngDelimCount),
strDelim, False)
       If Left(Trim(varColumn(lngDelimCount)), 1) = Chr(34) And _
           Right(Trim(varColumn(lngDelimCount)), 1) = Chr(34) Then _
           varColumn(lngDelimCount) = Mid(Trim(varColumn(lngDelimCount)), _
           2, Len(Trim(varColumn(lngDelimCount))) - 2)
       Next lngDelimCount
'__4) Open a recordset to the table that will receive the data.
   Set db = CurrentDb
   strSQL = "SELECT * FROM [" & strTbl & "];"
   Set rs = db.OpenRecordset (strSQL, dbOpenDynaset, dbAppendOnly)
'__5) Read each text file paragraph until the end of file is reached.
   Do While EOF(intF) = False
       Line Input #intF, strRow
'_5a) Protect embedded delimiter characters
       strRow = DelimProtect(strRow, strDelim, True)
'__6)  Parse rows into an array of field values.
       varRow = Split (strRow, strDelim)
'__7) Add a new record to the recordset.
       rs.AddNew
'__8) Write row field values to the recordset.
           For lngDelimCount = LBound(varColumn) To UBound(varColumn)
'_8a) Unprotect embedded delimiter characters
               varRow(lngDelimCount) = DelimProtect(varRow(lngDelimCount),
strDelim, False)
               If Left(Trim(varRow(lngDelimCount)), 1) = Chr(34) And _
                   Right(Trim(varRow(lngDelimCount)), 1) = Chr(34) Then _
                   varRow(lngDelimCount) = Mid(Trim(varRow(lngDelimCount)),
_
                   2, Len(Trim(varRow(lngDelimCount))) - 2)
               rs.Fields(varColumn(lngDelimCount)).Value =
varRow(lngDelimCount)
           Next lngDelimCount
'__9) End the second loop.
'__10) Update the recordset.
       rs.Update
'__11) End the first loop
   Loop
'__12) Close the recordset.
   rs.Close
   Set rs = Nothing
   db.Close
   Set db = Nothing
'__13) Close the text file.
   Close #intF
End Sub

Public Function DelimProtect(strString As Variant, strDelim As String,
blnProtect As Boolean) As Variant

Dim blnInString As Boolean
Dim lngLen As Long, lngLoop As Long
Dim strProtect As String, strChar As String
Dim strNewString As String

strProtect = Chr(7)
lngLen = Len(strString)
strNewString = ""

If blnProtect = True Then
   lngLoop = 1
   Do While lngLoop <= lngLen
       strChar = Mid(strString, lngLoop, 1)
       If strChar = Chr(34) Then
           If blnInString = True Then
               If Mid(strString, lngLoop + 1, 1) = Chr(34) Then
                   strNewString = strNewString & strChar
                   lngLoop = lngLoop + 1
                   strChar = Mid(strString, lngLoop, 1)
               ElseIf Mid(strString, lngLoop + 1, 1) = "," Then
                   blnInString = False
               End If
           Else
               blnInString = True
           End If
       ElseIf strChar = strDelim Then
           If blnInString = True Then strChar = strProtect
       End If
       strNewString = strNewString & strChar
       lngLoop = lngLoop + 1
   Loop
Else
   For lngLoop = 1 To lngLen
       strChar = Mid(strString, lngLoop, 1)
       If strChar = strProtect Then strChar = strDelim
       strNewString = strNewString & strChar
   Next lngLoop
End If

DelimProtect = strNewString

Exit Function
End Function

Signature

       Ken Snell

"Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
news:...

> OK - I've written a function (DelimProtect) that will protect or unprotect
> an embedded delimiter within a text string. To protect an embedded
[quoted text clipped - 100 lines]
>>>> > is not a memo datatype; it also does not strip the quotes from the
>>>> > value.
Reply to this Message
Ken Snell (MVP) - 22 May 2007 05:06 GMT
Rats -- typo in the code -- here is corrected copy:

Sub btnTest_Click (strFilePath as String, strTbl as String)
   Dim strColumns as String, strRow as String
   Dim varColumn as Variant, varRow as Variant
   Dim lngDelimCount as Long, i as Integer, intF as Integer
   Dim db as DAO.database, rs as DAO.recordset, strSQL as String

   Const strDelim As String = ","

   intF = FreeFile()

'__1) Open the text file.
   Open strFilePath For Input As #intF
'__2) Get column names from the first text file paragraph.
   Line Input #intF, strColumns
'_2a) Protect embedded delimiter characters
   strColumns = DelimProtect(strColumns, strDelim, True)
'__3) Parse column names into an array of field names.
   varColumn = Split (strColumns, strDelim)
   For lngDelimCount = LBound(varColumn) To UBound(varColumn)
'_3a) Unprotect embedded delimiter characters
       varColumn(lngDelimCount) = DelimProtect(varColumn(lngDelimCount),
strDelim, False)
       If Left(Trim(varColumn(lngDelimCount)), 1) = Chr(34) And _
           Right(Trim(varColumn(lngDelimCount)), 1) = Chr(34) Then _
           varColumn(lngDelimCount) = Mid(Trim(varColumn(lngDelimCount)), _
           2, Len(Trim(varColumn(lngDelimCount))) - 2)
       Next lngDelimCount
'__4) Open a recordset to the table that will receive the data.
   Set db = CurrentDb
   strSQL = "SELECT * FROM [" & strTbl & "];"
   Set rs = db.OpenRecordset (strSQL, dbOpenDynaset, dbAppendOnly)
'__5) Read each text file paragraph until the end of file is reached.
   Do While EOF(intF) = False
       Line Input #intF, strRow
'_5a) Protect embedded delimiter characters
       strRow = DelimProtect(strRow, strDelim, True)
'__6)  Parse rows into an array of field values.
       varRow = Split (strRow, strDelim)
'__7) Add a new record to the recordset.
       rs.AddNew
'__8) Write row field values to the recordset.
           For lngDelimCount = LBound(varColumn) To UBound(varColumn)
'_8a) Unprotect embedded delimiter characters
               varRow(lngDelimCount) = DelimProtect(varRow(lngDelimCount),
strDelim, False)
               If Left(Trim(varRow(lngDelimCount)), 1) = Chr(34) And _
                   Right(Trim(varRow(lngDelimCount)), 1) = Chr(34) Then _
                   varRow(lngDelimCount) = Mid(Trim(varRow(lngDelimCount)),
_
                   2, Len(Trim(varRow(lngDelimCount))) - 2)
               rs.Fields(varColumn(lngDelimCount)).Value =
varRow(lngDelimCount)
           Next lngDelimCount
'__9) End the second loop.
'__10) Update the recordset.
       rs.Update
'__11) End the first loop
   Loop
'__12) Close the recordset.
   rs.Close
   Set rs = Nothing
   db.Close
   Set db = Nothing
'__13) Close the text file.
   Close #intF
End Sub

Public Function DelimProtect(strString As Variant, strDelim As String,
blnProtect As Boolean) As Variant

Dim blnInString As Boolean
Dim lngLen As Long, lngLoop As Long
Dim strProtect As String, strChar As String
Dim strNewString As String

strProtect = Chr(7)
lngLen = Len(strString)
strNewString = ""

If blnProtect = True Then
   lngLoop = 1
   Do While lngLoop <= lngLen
       strChar = Mid(strString, lngLoop, 1)
       If strChar = Chr(34) Then
           If blnInString = True Then
               If Mid(strString, lngLoop + 1, 1) = Chr(34) Then
                   strNewString = strNewString & strChar
                   lngLoop = lngLoop + 1
                   strChar = Mid(strString, lngLoop, 1)
               ElseIf Mid(strString, lngLoop + 1, 1) = "," Then
                   blnInString = False
               End If
           Else
               blnInString = True
           End If
       ElseIf strChar = strDelim Then
           If blnInString = True Then strChar = strProtect
       End If
       strNewString = strNewString & strChar
       lngLoop = lngLoop + 1
   Loop
Else
   For lngLoop = 1 To lngLen
       strChar = Mid(strString, lngLoop, 1)
       If strChar = strProtect Then strChar = strDelim
       strNewString = strNewString & strChar
   Next lngLoop
End If

DelimProtect = strNewString

Exit Function
End Function

Signature

       Ken Snell

> OK - I've written a function (DelimProtect) that will protect or unprotect
> an embedded delimiter within a text string. To protect an embedded
[quoted text clipped - 100 lines]
>>>> > is not a memo datatype; it also does not strip the quotes from the
>>>> > value.
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.