Code is lnked as fixed width.
The problem appears to be the identifying the
record with the characters in question
I can append ALL the records from the
linked text list through docmd.sql,
and I can append the
subsequent records once the unique
identifying record is found, BUT
I just can't append the identifying record -
generating a runtime error '3075'
Syntax error in expression (missing operator)
in query expression '(((tmpcode.code =
'//XXXXXXX ZZZ 13DSAS,'B GATES BSP04D',XYZCLASS=T,CLASS=T))'
this is the one unique record I need to start the append process.
All subsequent records can be appended 'cause I guess
the rest of the records don't have commas and whatever.
Linked text list is a dump of all mainframe code
into one big doggy pile. Building my own
Visual Source Safe (kind of) through Access.
Programmers can select program name from combo box,
and a text file is exported of just
the code associated with that program - in exact order,
of course, otherwise the code won't compile.
here is code
dim strprogram as string 'find the name of the program
'based on combo box
dim strCode as string
strProgram = me.cmbProgram 'combo box of program names
Dim db As Database, tdf As TableDef
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT tmpcode.
Code:
_
& "FROM tmpcode WHERE (((tmpCode.code) ='" & strProgram & "'));)"
rst.findfirst 'find that program header (unique row)
Do While Not rst.EOF
strCode = rst!code 'Set strCode to contents of linked text field "CODE"
'only one field in linked text file
strSQL = "(INSERT INTO tmpExport ( & strCode & ))"
docmd.runsql (strsql) 'append into export table that line of code
rst.MoveNext 'keep going
Loop
rst.Close
BRAINSTORM:
Use a dLookup function to identify the first record,
map strCode to the dLookup, and append strCode
to export table.
Gonna try that.
[QUOTE="John Nurick"]
[QUOTE]
I believe the comma's in the field of the linked file that
are preventing that paticular record from being appended.
Is there any way to load the contents of a specific record,
even with the , and // and '
into memory such that I can copy just that record
into a temporay table?[/QUOTE]
When you link the file, the wizard gives you the opportunity to specify
the "delimiter" (the character used to separate fields) and text
qualifier (the character used to enclose the data in text fields). By
default, these are , and " respectively.
To import the whole line from the text file into a single field, set the
qualifier to {none}, and for a delimiter, specify a character that does
not appear anywhere in the file. Often the pipe character "|" is a good
one to use - but this depends entirely on the contents of your file.
To get the contents of a specific record in the text file, you have
basically two choices. One is to link the file as above and then use a
query that selects that records by its contents.
The other is to write code that reads the text file and identifies the
record in question, then appends it to your temporary table. This
usually the only sensible approach if the record is identified by its
position in the file (e.g. the first record) rather than by its
contents. Here's some air code that shows the general idea:
Dim strFileName As String
Dim strLine As String
Dim strSQL As String
Dim lngFN as Long
'Open file
strFileName = "D:\Folder\File.txt"
lngFN = FreeFile()
Open strFileName For Input As #lngFN
'Read first line and close file
Line Input #lngFN, strLine
Close #lnfFN
'If there are any ' in the line, we need to double them. Otherwise
'they will be confused with the ' ' used as quote marks in the SQL
'statement
strLine = Replace(strLine, "'", "''")
'Append to table
strSQL = "INSERT INTO NameOfTable ( NameOfField ) VALUES ('" _
& strLine & "');"
CurrentDB.Execute strSQL, dbFailOnError
[QUOTE]
That one record uniquely identifies the records to follow,
so I really need the entire contents of that record.
This can get complicated, but let's start with the simple questions.
1) What do you mean by "the insert statement doesn't execute"? I presume
that's what's actually happening is that you have a statement like
CurrentDB.Execute strSQL
which is being executed but that some or all of the records are not
being appended.
2) Is the problem in the linking or in the appending? Open the linked
table in datasheet view and inspect some of the records that you know
from the text file should contain quote marks or the other characters
you mention. Do they appear correctly in the linked table? If not, the
problem is either in the text file or in the way you linked the table.
3) Open the text file in Notepad or another text editor. As I understand
it from what you've said, you're treating each line as a single field.
Does every line begin and end with a quote (") character? (i.e. each
field is "qualified" with guote marks) This is what Access expects of a
linked text file unless you tell it otherwise.
If the lines are just plain text not "qualified" with quotes, you need
to specify this when you link the text file, either in the text import
wizard or with an import/export specification. Otherwise, quotes within
the data are likely to make the import/link routine go wrong.
On Tue, 22 Nov 2005 21:46:45 -0500, "J Shrimps, Jr."
Have a lnked text file I need to
extract data from - only one character field
in file, but some of the records have
// ' " characters, so when I try to
insert them into a temporary table with
strSQL = "Insert Into tmpExport ( Code )" _
& "Select " & StrTblName & ".[CODE] from" & StrTblName & ";"
However, when some of these funky characters are encountered
in the field CODE, then the insert statement doesn't execute.
I'm assuming Access is interpreting the // " ' characters as some kind of
delimiter.
How can I mask these characters so Access can append them
(through code) to another table?
[/QUOTE]
[/QUOTE]
[/QUOTE]
