# causes syntax error

[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

 

[Guest]

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 ...

 

[David C. Holley]

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

 

[RD]

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