Solved

Passing parameters to SQL - MS Access

Posted on 2000-03-17
3
221 Views
Last Modified: 2010-05-18
Here is my code :

Dim cmdPubs As ADODB.Command
Set cmdPubs = New ADODB.Command
Set cmdPubs.ActiveConnection = DtEnviromentFly.FlyConnection

  With cmdPubs
        .CommandType = adCmdStoredProc
        .CommandText = "{Call SQL_Acesso (?)}"
        .Parameters(0).Value = Popup.cmbName.Text
        .Parameters(0).Direction = adParamInput
        .Execute
End With

The code is returning a invalid SQL statement errror, when it executes .Parameters(0)..........

Did I forget something ? How could I pass parameters to a SQL in MS Access ?
0
Comment
Question by:mvianna
  • 2
3 Comments
 
LVL 2

Expert Comment

by:Tchalkov
Comment Utility
Try this:
    cmdpubs.CommandText = "select * from ParamQry"
    Dim par As New ADODB.Parameter
    Set par = cmdpubs.CreateParameter("param", adInteger)
    cmdpubs.Parameters.Append par
    par.Value = Popup.cmbName.Text
       
   
   Set rs = cmdpubs.Execute
0
 
LVL 1

Accepted Solution

by:
advapp earned 50 total points
Comment Utility
There is a document in the ADO help that covers this.  There are two ways to get there:

While in VB's IDE, and in the "With" clause of your code, enter:

  .Parameter.Append

and then place your cursor over Append and press F1.  This should bring up the ADO help file on the "Append Method" page.  Click on the "example" link and you should be taken to the "Append and CreateParameter Methods Example".

If that doesn't work, open C:\<OS>\Ado210.chm and look in:

Microsoft ActiveX Data Objects (ADO)
  Microsoft ADO Programmer's Reference
    Learning ADO | ADO Code Examples
      ADO Method Examples

This "book" contains the "Append and CreateParameter Methods Example" document.

This page explains how to handle parameters and avoid this error.

Good luck!
0
 
LVL 1

Expert Comment

by:advapp
Comment Utility
Oh, I should've clarified that "<OS>" in the file path represents either "Windows" or "WinNT" depending on your system.  Plus, the path should have been:

"C:\<OS>\Help\Ado210.chm"

Sorry for the confusion.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now