CODEWITHSUNDEEP
vb.netms-accessado.netparameters
Mihaylov
Mihaylov

Reputation: 21

Running a query on Access from VB, passing variable or function as query parameter

I am trying to run a query an Access db from VB. The general query which I want to run is

    SELECT * FROM Patient WHERE Patient.PatientID = ?
or  SELECT * FROM Patient WHERE Patient.PatientLname = ?

I tried using an input box which captured the user input and pass that variable to the query, but that failed.

Then I read about writing a function and using that, however, I keep getting an error which says Function not defined, but when stepping through the code, it seems to work.

Here is my function:

Module Module1
    Public Function LookUpNow(ByVal userInput As String) As String
        LookUpNow = userInput
        Return userInput
        Exit Function
    End Function
End Module

also here is the query as it currently is

SELECT * FROM Patient WHERE (((Patient.PatientLName)=LookUpNow()))

I am using VisualStudio. Do I have to manually write a connection string using OleDBConnection or does VisualStudio automatically do it??

Private Sub FillBy3ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillBy3ToolStripButton.Click
    Dim Input As String
    Input = InputBox("What patientID do you want to lookup?", "PatientID entry Form", "Enter PatientID here")
    Call LookUpNow(Input)


    Try
        Me.PatientTableAdapter.FillBy3(Me.PatientRecordsDataSet.Patient)
    Catch ex As System.Exception
        System.Windows.Forms.MessageBox.Show(ex.Message)
    End Try

End Sub

I am so confused and frustrated. Thanks for your help

Upvotes: 2

Views: 3199

Answers (1)

Igor Turman
Igor Turman

Reputation: 2205

You cannot remotely (e.g. OLE DB, ODBC etc.) run Access queries that contain internally defined functions (vba). The error you got is totally legitimate.

You can use OLE DB Provider to execute the query. I'm sure you know how to do this but, just in case, here is some helpful info: http://msdn.microsoft.com/en-us/library/6d9ew87b(v=vs.90).aspx

some examples here: http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter(v=vs.90).aspx

Upvotes: 2

Related Questions