Oracle and Visual Basic

Recently we have been trying to build some user interfaces in Visual Basic 4.0 that would connect to our oracle 7 server (RS 6000 running AIX) using ODBC. We have the ODBC software configured correctly now and can manipulate the Oracle data using Microsoft Access. I have written code in Visual Basic to connect to an Access database file, but for some reason can't get the connection to the Oracle database to work correctly. I currently have an Access database file with a connection made to the Oracle database in it. If I try to perform a standard select statement in VB on a table that was created with Access it works correctly. If I perform the same select statement on one of the Oracle linked tables, I get the following error:

Run time error '3061'

Too few parameters. Expected 1

This error seems like nonsense to me. Does anyone have a suggestion. This looks like a VB or Access rather than an Oracle problem, but I thought that someone else dealing with Oracle might have tried this before.

Here is a shortened version of the Visual Basic code I am using:

Private Sub Command3_Click()
Dim Db As Database
Dim MySQL As String

Connect$ = "C:\Pharmacy.mdb"
Set Db = OpenDatabase(Connect$)

MySQL = "SELECT PAT_ID, GEN_NAME, PROV_NO, PRESCRIBER_NO, NDC_CODE, F_DATE_SERVICE FROM RXHX WHERE [GEN_NAME] = " & Chr$(39) & List2.List(0) & Chr$(39)

For I% = 1 To (List2.ListCount - 1)

      MySQL = MySQL & " OR [GEN_NAME] = " & Chr$(39) & List2.List(I%) & Chr$(39)
Next I%

MySQL = MySQL & " ORDER BY [PAT_ID],[F_DATE_SERVICE]"

Set MyQuery = Db.OpenRecordset(MySQL, dbOpenSnapshot)

{Code omitted for sake of bandwidth}

Thanks.

Travis Nelson
Idaho State University
Center for Decision Support Received on Tue Nov 14 1995 - 00:00:00 CET