How to pass the text of variables to Access Query

[paulfla]

Hi everyone,

I really appreciate all of you experts that take the time out to help us VB challenged people.

From VB6, I am able to execute a query in my MS Access database. This query has parameters that I pass. It runs the query but the values are the actual variable names "var1" and "var2" in the access table.

What syntax do I need around the parameters to pass the values of the field. line --> DB.Execute "exec mytableupdateq val1 , val2"

Thanks in advance for your time.
Paul

Here is my code:
========
Private Sub cmd_runqry_Click()

Dim val1, val2, ret
val1 = Textname.Text
val2 = inv_date.Text

Dim DB
Set DB = CreateObject("ADODB.Connection")
DB.Mode = adModeReadWrite
DB.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\SPE_V1_COPY.mdb")
DB.Execute "exec mytableupdateq val1 , val2"
DB.Close
MsgBox ("The update completed successfully")


End Sub
==================

 

[koala15]

Here is an example from MS:

Sub ADOExecuteParamQuery2()

   Dim cnn As New ADODB.Connection
   Dim cmd As New ADODB.Command
   Dim rst As New ADODB.Recordset
   Dim fld As ADODB.Field

   ' Open the connection
   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=.\NorthWind.mdb;"

   ' Create the command
   Set cmd.ActiveConnection = cnn
   cmd.CommandText = "[Sales by Year]"

   ' Execute the Command, passing in the
   ' values for the parameters
   Set rst = cmd.Execute(, Array(#8/1/1997#, #8/31/1997#), _
      adCmdStoredProc)

   ' Display the records in the
   ' debug window
   Do Until rst.EOF
      For Each fld In rst.Fields
         Debug.Print fld.Value & ";";
      Next
      Debug.Print
      rst.MoveNext
   Loop

   ' Close the recordset
   rst.Close

End Sub

"Life is full of learning, and then there is wisdom"

 

[paulfla]

Is there a way to use my current code and just modifying the syntax for the parameters?

TIA,
Paul

 

[koala15]

The alterations you would have to make to your code (I think) would be quite minor.

Also, the MS example code results in faster execution as it uses "early binding" (ie the compiler does the work), rather than your code (specifically Set DB = CreateObject("ADODB.Connection")) which results in "late binding" (ie the application at run time does the work).

Does this make sense?

Please ask more questions, if you need clarification

Cheers .... Greg

"Life is full of learning, and then there is wisdom"

 

[paulfla]

Greg:

Thanks for the reply.

I did figure out the soulution ot that one line. Here it is:

DB.Execute "exec mytableupdateq " & val1 & ", '" & val2 & "'"

In the example above val2 is passing a date to a date field.

Thank you all for reviewing. Now if I can convert the above code to the code that you supplied Greg.

Paul

 

[koala15]

Paul, If you get stuck ... ask.

"Life is full of learning, and then there is wisdom"