Bob
Fri Sep 16 07:28:42 CDT 2005
"Like" is only useful when you have a wildcard (either % or _) , so unless
sname contains a wildcard, you might as well use the more efficient "=".
One gotcha is that the Jet wildcards (* and ?) are used when creating and
running a query in Access. When running a query via ADO however, you must
use the ODBC wildcards (? and _) instead.
Bob Barrows
fnguy wrote:
> We've gotten further. Now I'm stuck with trying to get a like clause
> to work. If I omit the like clause it runs like a champ.
>
> strsql="update DBBackup_Query set DBBackup_Query.last_bak = #" &
> ddate & "# where (DBBackup_Query.ServerName) Like "& sname &"'"
> (I'm using the .execute method B Barrows wrote)
>
> No Error is returned but the records aren't updating....
>
>
> "Bob Barrows [MVP]" wrote:
>
>> The error means that the query parser is encountering a word in the
>> query that does not apply to any database objects, so it is treating
>> that word as a parameter for which you have not supplied a value.
>>
>> Can you run this query as-is in the Access Query Builder (after
>> substituting " for the "")?
>>
>> If it fails when run in Access, then you will probably have a more
>> helpful error message - most likely you have a field name misspelled
>>
>> If it runs in Access, then you may have a reserved keyword problem.
>>
http://www.aspfaq.com/show.asp?id=2080 No, I don't see any.
>>
>> FWIW, you should not be using a wasteful recordset to run an action
>> query. Best practice is to do as follows:
>>
>> strsql="update DBBackup_Query set last_bak " & _
>> "= #1991-01-03# where ServerName='MyServer'"
>> objAccessdbConnection.Execute strsql,,129
>>
>> Notice that I changed your date's format - always supply dates in a
>> non-ambiguous format (
http://www.aspfaq.com/show.asp?id=2040) -
>> yyyy-mm-dd will not be misinterpreted. Also note that I got rid of
>> all the field qualifiers - since there is only one table involved in
>> the sql statement, there is no need to tell the query engine which
>> table the fields belong to.
>>
>> If none of this helps, you will need to provide us with more info so
>> we can reproduce the problem - table structure as in field names and
>> datatypes.
>>
>> Bob Barrows
>> PS. I would stop using the obsolete ODBC DSN - see here:
>>
http://www.aspfaq.com/show.asp?id=2126
>>
>> fnguy wrote:
>>> Here's where I am:
>>>
>>> objAccessdbRecordset.Open "update DBBackup_Query set
>>> DBBackup_query.last_bak = #01/03/1991# where
>>> DBBackup_query.ServerName=""MyServer"";", objAccessdbConnection,
>>> adOpenStatic, adLockOptimistic, 1
>>>
>>> Getting an error still that 1 parameter is missing. Is this in the
>>> Access db help files?
>>>
>>> Thanks for the assistance.
>>>
>>>
>>> "RkTool" wrote:
>>>
>>>> Hi fnguy,
>>>>
>>>> The line: objAccessdbRecordset.Open
>>>> needs 5 arguments, so after "adLockOptimistic" you should add ",
>>>> adCmdText" or just ", 1".
>>>>
>>>> Regards,
>>>>
>>>> RkTool
>>>>
>>>> "fnguy" wrote:
>>>>
>>>>> Hello all. First, thanks for any assistance.
>>>>>
>>>>> Now to the problem, I'm trying to run an SQL update to an access
>>>>> db through vb script. Getting the following error:
>>>>>
>>>>> An error was flagged objAccessdbRecordset.Open "select * from
>>>>> DBBackup_Query [Microsoft][ODBC Microsoft Access Driver] Too few
>>>>> parameters. Expected 5.
>>>>>
>>>>>
>>>>> Here's the syntax:
>>>>>
>>>>> Set objAccessdbConnection = CreateObject("ADODB.Connection")
>>>>> Set objAccessdbRecordset = CreateObject("ADODB.Recordset")
>>>>>
>>>>> 'Hard Coded DSN of the Access DB
>>>>> objAccessdbConnection.Open "DSN=DBA_2000;"
>>>>> 'WScript.Echo objAccessdbConnection.ConnectionString
>>>>> If Err.Number <> 0 Then
>>>>> WScript.Echo "An error was flagged opening the DSN=DBA_2000;."
>>>>> WScript.Echo Err.Description
>>>>> Err.Clear
>>>>> End If
>>>>>
>>>>> 'objAccessdbRecordset.CursorLocation = adUseClient
>>>>> objAccessdbRecordset.Open "update DBBackup_Query set last_back =
>>>>> ""01/01/2005"" where servername = [FHECOMDB] And dbname = [CMDB]",
>>>>> objAccessdbConnection, adOpenStatic, adLockOptimistic
>>>>> If Err.Number <> 0 Then
>>>>> WScript.Echo "An error was flagged objAccessdbRecordset.Open
>>>>> ""select * from DBBackup_Query"
>>>>> WScript.Echo Err.Description
>>>>> Err.Clear
>>>>> End If
>>>>>
>>>>>
>>>>> wscript.echo "Thanks again!"
>>
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"