MS Access SQL Like

  • Thread starter Thread starter Bob Weiner
  • Start date Start date
B

Bob Weiner

I have an MS Access database which I am using to update some groups in an
Active Directory.

I discovered through the debugging process, that if you connect to a stored
query containing a "Like" in the Where clause, that ADO.Net won't retrieve
any records even though running the same query inside access works fine.

Now that I know this fact I can work around it but I am curious if there are
any other "gotchas" that I need to be concerned with. I can't find any good
references that talk specifically about the peculiarities of ADO.Net and MS
Access. Any pointers?

thanks,
bob
 
Perhaps the usual difference regarding the wildcard character (* or %).

Also the JOIN syntax were a bit weird (Access 2003 allows now to "enable"
the use of the "correct" JOIN syntax).

Others may exists but I guess that they are likely each the top 1 trap /
annoyance...
 
The main ones with Access that you'll find probably find is paramaeter
sequencing and with reserved words. If you use a Reserved word
http://www.knowdotnet.com/articles/reservedwords.html for a column or table
name, it will probably blow up in .NET but will run fine within Access.
With parameters, if you name them, it doesn't actually accept the name so it
still behaves in the order which they appear. The order of their appearance
in the query must match the exact order that they are added. These two
issues in particular come up fairly frequently. As far as LIKE goes, you're
right, there are only work arounds (here's one for Sql Server in case you're
interested http://support.microsoft.com/default.aspx?scid=kb;en-us;555167)
b/c the escaping [which is very beneficial and one of the main reasons to
use parameterized queries instead of regular dynamic sql] treats the comma
separated values as one value which effectively defeats the whole purpose.
It's actually worse b/c chances are nothing will match.

Anyway, I can't think of anything else off of the top of my head but
hopefully this helps.

Bill
 
W.G. Ryan,

I think you are confusing LIKE with IN in your remarks below.

The biggest problem with LIKE and Access is needing to use the * wildcard
character within Access and the % wildcard character within ADO.Net.

Kerry Moorman
 
Thanks! I didn't even think about that.

bob


Patrice said:
Perhaps the usual difference regarding the wildcard character (* or %).

Also the JOIN syntax were a bit weird (Access 2003 allows now to "enable"
the use of the "correct" JOIN syntax).

Others may exists but I guess that they are likely each the top 1 trap /
annoyance...
Click to expand...
 
Thanks for the additional info!

I was struggling with parameters while trying to update a table in Access.
I didn't work it out but will probably be back on that today. Now that I'm
aware of this issue, I may have better luck.

bob


W.G. Ryan MVP said:
The main ones with Access that you'll find probably find is paramaeter
sequencing and with reserved words. If you use a Reserved word
http://www.knowdotnet.com/articles/reservedwords.html for a column or
table name, it will probably blow up in .NET but will run fine within
Access. With parameters, if you name them, it doesn't actually accept the
name so it still behaves in the order which they appear. The order of
their appearance in the query must match the exact order that they are
added. These two issues in particular come up fairly frequently. As far
as LIKE goes, you're right, there are only work arounds (here's one for
Sql Server in case you're interested
http://support.microsoft.com/default.aspx?scid=kb;en-us;555167) b/c the
escaping [which is very beneficial and one of the main reasons to use
parameterized queries instead of regular dynamic sql] treats the comma
separated values as one value which effectively defeats the whole purpose.
It's actually worse b/c chances are nothing will match.

Anyway, I can't think of anything else off of the top of my head but
hopefully this helps.

Bill
Bob Weiner said:
I have an MS Access database which I am using to update some groups in an
Active Directory.

I discovered through the debugging process, that if you connect to a
stored query containing a "Like" in the Where clause, that ADO.Net won't
retrieve any records even though running the same query inside access
works fine.

Now that I know this fact I can work around it but I am curious if there
are any other "gotchas" that I need to be concerned with. I can't find
any good references that talk specifically about the peculiarities of
ADO.Net and MS Access. Any pointers?

thanks,
bob
Click to expand...
Click to expand...
 
YOu're right - my mind was elsewhere - thanks ;-)
Kerry Moorman said:
W.G. Ryan,

I think you are confusing LIKE with IN in your remarks below.

The biggest problem with LIKE and Access is needing to use the * wildcard
character within Access and the % wildcard character within ADO.Net.

Kerry Moorman


W.G. Ryan MVP said:
As far as LIKE goes, you're
right, there are only work arounds (here's one for Sql Server in case
you're
interested
http://support.microsoft.com/default.aspx?scid=kb;en-us;555167)
b/c the escaping [which is very beneficial and one of the main reasons to
use parameterized queries instead of regular dynamic sql] treats the
comma
separated values as one value which effectively defeats the whole
purpose.
It's actually worse b/c chances are nothing will match.
Click to expand...
Click to expand...
 
Back
Top