Create a recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The code below I use to output a field of data as I build it into a SQL
INSERT statement. When the original table is updated (tPath Field) it is OK,
but when inserting tPath via SQL, after the string gets to 188 charactors it
exports garbage like this: 颼ᩢǡĀ退ê²å£¡é”–ϡĀ᠀怈颼ᩢǡĀ .

For i = 1 To objTree.Nodes.Count
tPath = objTree.Nodes(i).FullPath
DoCmd.SetWarnings False
DoCmd.RunSQL ("INSERT INTO OutputBOM ( Output1 ) Select tPath")
DoCmd.SetWarnings True
Next

the code loops through each node of my tree and then outputs the FullPath
(tPath) into the OutputBOM table.

I am familure with getting recordset to Excel, is there a way to get tPath
into a recordset without writing into a table? Or some other method of
passing tPath to Excel?
 
For i = 1 To objTree.Nodes.Count
tPath = objTree.Nodes(i).FullPath
DoCmd.SetWarnings False
DoCmd.RunSQL ("INSERT INTO OutputBOM ( Output1 ) Select
tPath") DoCmd.SetWarnings True
Next
Click to expand...

First of all, using DoCmd is not a safe method to run SQL commands,
particularly with safeties switched off. Use the .Execute method instead
with the dbFailOnError parameter.

Secondly, use the value of tPath rather than the variable name:

Set db = CurrentDB()

...


for i = 1 to objTree.Nodes.count
' you'll need to check for embedded quotes in the
' tPath string
db.Execute "INSERT INTO OutputBOM (Output1 ) " & _
"VALUES (""" & objTree.Nodes(i).FullPath & """);", _
dbFailOnError

Next i


Hope that helps


Tim F
 
Tim,

Thanks for the help. And thanks for the imbedded quotes warning. After
inserting the code you gave me, the program was failing at the 37th pass into
my loop. I looked at the 37th record and there it was. A quotation mark
used as a inch indicator. For the time being, I replaced them with in.

Is there another method of getting at the data that is less prone to this
problem?

aWs
 
After inserting the code you gave me, the program was failing at the
37th pass into my loop. I looked at the 37th record and there it was.
A quotation mark used as a inch indicator. For the time being, I
replaced them with in.

Is there another method of getting at the data that is less prone to
this problem?
Click to expand...

Don't you just love operating systems that default to using illegal
characters..? The One True Path is to program defensively vis:


jetSQL = "INSERT INTO OutputBOM (Output1 ) " & vbNewLine & _
"VALUES (""" & SQLString(objTree.Nodes(i).FullPath) & """);"





function SQLString(SomeText as Variant) as string
if isnull(Sometext) then
sqlstring = "Null"
else
sqlstring = """" & _
replace(SomeString, """", """""") & _
""""
end if
end function



Yes, that line does have _six_ quote marks in a row!

Hope it helps


Tim F
 
Back
Top