Bruce said:
In my code below I am trying to import filenames from a directory to the
Field ProductID in table tbl_Spec.
1) The returned value of msgbox myFile is the element I want to append.
However it is not recognised in my SQL string. Access thinks its a parameter.
What have I got wrong?
2) Am I approaching the problem from the right angle. Can anyone suggest
a better approach than the way I am tackling this.
Bruce.
Sub myDir()
Dim myFile As String
myFile = Dir("D:\Spec\Spec2\*.*")
Do While Len(myFile) > 0
MsgBox myFile
'Build the String
myString = "INSERT INTO tbl_Spec ( ProductID ) SELECT myFile;"
'Append record
DoCmd.RunSQL myString
'Itterate to next file...
myFile = Dir()
Loop
End Sub
Bruce,
'Build the String
myString = "INSERT INTO tbl_Spec ( ProductID ) SELECT myFile;"
In the following, you are assembling a string that is, letter for
letter:
"INSERT INTO tbl_Spec ( ProductID ) SELECT myFile;"
"myFile", in this case, is just *letters* in a string.
What you need to do is "concatenate" the string with the variable.
'Build the String 1
myString = "INSERT INTO tbl_Spec ( ProductID ) SELECT " &
myFile & ";"
Note that the space after the select is important. It's the
difference between:
The "&", or ampersand, is an operator that sticks two strings
together, or a string and a string variable (or variable that can be
interpreted as a string).
1) Without space: "INSERT INTO tbl_Spec ( ProductID )
SELECTyourfilename.txt;"
and
2) With space: "INSERT INTO tbl_Spec ( ProductID ) SELECT
yourfile.txt;"
Now, we need to move on to SQL syntax, as the above will not work.
Because you are adding a "value", it's ok to use the VALUES clause of
the INSERT statement.
Note the attention to the quote marks, there are extras, and they are
needed.
'Build the String 2
myString = "INSERT INTO tbl_Spec ( ProductID ) " _
myString = myString & "VALUES(" & """" & myFile & """" & ");"
The string fed to JET needs to like like:
INSERT INTO tbl_Spec ( ProductID ) VALUES ("yourfilename.text");
However . . .
myString = "INSERT INTO tbl_Spec ( ProductID ) " _
myString = myString & "VALUES(" & myFile & ");"
.. . . only produces . . .
INSERT INTO tbl_Spec ( ProductID ) VALUES (yourfilename.text);
.. . . note the absence of the "" marks.
When VBA is interpreting its own code, it pairs up "" marks and thinks
everything between each matched pair is a string. If there is a lone
" somewhere, VBA can become confused in odd ways, including just
mis-assembling the string and not bothering to tell you.
In order to "put a quote mark into a string", you have to "escape it".
Here's an example of what happens when you just type in Quote marks
around a standard English sentence.
strQuoteTest = "John asked Mary, "Why did you do that?""
Produces an error.
VBA thinks that "John asked Mary, " is one string, that __Why did you
do that?__ is gibberish, and that "" is a zero-length string.
VBA gets by this by letting you show which quote marks are to *become*
part of the string, and which aren't. It's called "escaping" the
quote mark. This is done by adding a quote mark right before the
quote mark to be kept in the actual string.
Now, the same example, with the quote marks "escaped".
strQuoteTest = "John asked Mary, ""Why did you do that?"""
produces: John asked Mary, "Why did you do that?"
Which is what we really wanted for this example.
Now, and here's the tough part, when assembling a string, the string
variable is "outside" the string, and so to get actual double-quote
marks to *appear* around it when the string is assembled, you have to
do what I did above (shown here again).
'Build the String 2
myString = "INSERT INTO tbl_Spec ( ProductID ) " _
myString = myString & "VALUES(" & """" & myFile & """" & ");"
Sincerely,
Chris O.
