# causes syntax error

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

Guest

Hi again.

Problem:
I've exported a bunch of huge tables from an Oracle db that I'm restricted
to using a particular interface for. This front end exports null values as a
string: #EMPTY
I have to export the tables as txt files because many of them run 300k to
over 500k records. Then I import them into Access 2K making all the fields
text data type (any other data type causes import errors).
Now I've got a boatload of tables with boatloads of fields containing
"#EMPTY".

This code produces a syntax error:
'Fields are numbered from 0 so we start at 1 to avoid the ID field
For i = 1 To rs.Fields.Count - 1
sField = rs.Fields(i).Name
sSql = "UPDATE " & strTable & " SET [" & strTable & "]. _
[" & sField & "] = Null "
sSql = sSql & "WHERE ((([" & strTable & "]. _
[" & sField & "])='#EMPTY'));"
db.Execute sSql, dbFailOnError
Next i

Question:
I *know* it's that damn hash mark! I need to do this programatically
because it will be automated once I work out the kinks. How do I get around
the hash mark?

Thanks,
RD
 
HA! Nevermind.

I got around it this way:
sStr = """" & Chr(35) & "EMPTY" & """"
For i = 1 To rs.Fields.Count - 1
sField = rs.Fields(i).Name
sSql = "UPDATE " & strTable & " SET [" & strTable & "].[" & sField & "]
= Null "
sSql = sSql & "WHERE ((([" & strTable & "].[" & sField & "])=" & sStr &
"));"
db.Execute sSql, dbFailOnError
Next i

What a pain in the ...
 
1. What is the SPECIFIC error message that you're getting?
2. Anytime I'm building an action query for using in code, I ALWAYS use
the query builder whenever possible to ensure the syntax is correct and
to tweak the query. Then its just a matter of copying the SQL statement
into the procedure.

David H
 
Thanks for the response, David, but I fixed this one myself. I have a new issue
I'll post under a new header.

Regards,
RD


1. What is the SPECIFIC error message that you're getting?
2. Anytime I'm building an action query for using in code, I ALWAYS use
the query builder whenever possible to ensure the syntax is correct and
to tweak the query. Then its just a matter of copying the SQL statement
into the procedure.

David H
Hi again.

Problem:
I've exported a bunch of huge tables from an Oracle db that I'm restricted
to using a particular interface for. This front end exports null values as a
string: #EMPTY
I have to export the tables as txt files because many of them run 300k to
over 500k records. Then I import them into Access 2K making all the fields
text data type (any other data type causes import errors).
Now I've got a boatload of tables with boatloads of fields containing
"#EMPTY".

This code produces a syntax error:
'Fields are numbered from 0 so we start at 1 to avoid the ID field
For i = 1 To rs.Fields.Count - 1
sField = rs.Fields(i).Name
sSql = "UPDATE " & strTable & " SET [" & strTable & "]. _
[" & sField & "] = Null "
sSql = sSql & "WHERE ((([" & strTable & "]. _
[" & sField & "])='#EMPTY'));"
db.Execute sSql, dbFailOnError
Next i

Question:
I *know* it's that damn hash mark! I need to do this programatically
because it will be automated once I work out the kinks. How do I get around
the hash mark?

Thanks,
RD
Click to expand...
Click to expand...
 
Back
Top