Vba rs.execute (Parameter Query)

Question

Please, could you tell me how is the sentence to execute a SQL Query using vba? (With parameters) I'm trying to do this :

First prepare a sub to call the function Arir_Recordset:

Public Sub Retriev_rs()

Dim Query As String
Query = "SELECT * FROM  '" & Table & "'  WHERE DNI = '" & DNI & "'  "
Dim rs2 As ADODB.Recordset
Set rs2 = New ADODB.Recordset


Open_Connection---- Works well!

Set rs2 = Abrir_Recordset(Query, "PRODUCTOS_CLIENTESBCO", "00000098687")


End Sub

The Function:

Public Function Abrir_Recordset(Query As String, Table As String, DNI As String) As ADODB.Recordset

Dim comm As ADODB.Command

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic

Set rs = conn.Execute(QUERY)

Set Abrir_Recordset = rs


End Function

Issue:

It's not recognising the Query. It says :

Incorrect sintax near "

It's reading:

 - "SELECT * FROM  ''  WHERE DNI = ''  "

Of course... it isn't write well the Query. Could you help me ??

Many thanks!!!

You need to either assign or pass in as arguments the Table and DNI variables. — Parfait
Thanks! It´s working now : : Query = "Select * from " & Tabla & " where DNI = '" & DNI & "'" — Marcos

Marcos Marcos · Accepted Answer · 2016-09-29T13:53:15

Its Done... passing parameters to this Function works well :

Public Function Abrir_Recordset(Tabla As String, DNI As String) As ADODB.Recordset

Dim comm As ADODB.Command

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic

Query = "Select * from  " & Tabla & "  where DNI = '" & DNI & "'"

Abrir_Conexion


Set rs = conn.Execute(Query)

Set Abrir_Recordset = rs


End Function

Vba rs.execute (Parameter Query)

1 Answers