Access masking character changed from * to %

[Tranman]

Hi All,
Sometime since last week, the masking character in Access SQL on my computer (Access 2003/WinXP SP2) changed from the old standard of asterisk (*) to percent sign (%), like it is in Oracle and SQL.

Here's a snippet which DOES NOT WORK:

Private Sub del2005()
Dim strSQL As String
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
  strSQL = "Delete from " & tdf.Name & " where accident_key like '2005[red]*[/red]'"
  CurrentProject.Connection.Execute strSQL
Next
Set tdf = Nothing
End Sub

This code WORKS JUST LIKE IT SHOULD:

Private Sub del2005()
Dim strSQL As String
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
  strSQL = "Delete from " & tdf.Name & " where accident_key like '2005[red]%[/red]'"
  CurrentProject.Connection.Execute strSQL
Next
Set tdf = Nothing
End Sub

Does anyone have any insight about why this happened?

In a way, it would be good to change because I could use the same syntax in all of my databases, but in a way, this is horrible because it is going to break dozens of my Access databases.

Anyone who knows anything about what happened, please write.

Tranman


"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[Golom]

Check under "Options / Tables/Queries". There is a check box for "SQL Server Compatible Syntax". If it is checked then Access will use "%" rather than "*" as the wildcard character (among other things.)

 

[Tranman]

Golom,
Thanks. It's not checked. Any other ideas?
Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[PHV]

You haven't change your mdb into adp ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Tranman]

PHV,
Nope, it is, and always has been a mdb.
Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[Tranman]

BTW, it is not "just" this database, and it is not just update queries. The following code does not work, either(returns an empty recordset), even though James Taylor is in there:

Private Sub Getrows()
Dim rs As New ADODB.Recordset
Dim ary
rs.Open "Select * from cust where L_NAME like 'Tay[red]*[/red]'", CurrentProject.Connection, adOpenStatic, adLockReadOnly
ary = rs.Getrows()
rs.Close
Set rs = Nothing
End Sub


"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[PHV]

Dim rs As New ADODB.Recordset
ADO NEVER admits * as wildchar

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Tranman]

Yeah, you're right about that...still have a problem with the original delete query though.
Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[PHV]

CurrentProject.Connection.Execute
This is ADO ...
The DAO way:
CurrentDB.Execute

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886