Passing Paramters to Access Queries

[lenin]

Hello,
can some tell me if it is possible to pass queries from VB ( 6 ) to MS Access ( 97 ) queries, as is the case with stored procedures for SQL Server.

I want to create a form which retrieves data ( this is done 0 and it someone changes info on the form the table in Access is updated accordingly.

Thnaks in advance.

Tony.

[Gerald]

You can pass parameters to a query stored in an Access database, but there are a couple of requirements in doing this. First the stored query must use the PARAMETERS clause as shown in the example below (a query I tested using the Northwind sample database.) This update query will add ten cents to the price of all products that match the "SupplierID" parameter.


PARAMETERS SupplierID Long;
UPDATE Products SET Products.UnitPrice = UnitPrice+0.1
WHERE Products.SupplierID=SupplierID;


The second requirement is that you must use the "MSDASQL" provider through an ODBC driver. Then you would use the query in the following manner.

Dim sConn As String
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command

sConn = "Provider=MSDASQL.1;"
sConn = sConn & "Driver={Microsoft Access Driver (*.mdb)};"
sConn = sConn & "DBQ=C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;"

cn.ConnectionString = sConn
cn.Open

cmd.ActiveConnection = cn

' The query name goes in CommandText
'
cmd.CommandText = "prIncreasePrice"
cmd.CommandType = adCmdStoredProc

' This sets the supplierID we want to update
'
cmd.Parameters("SupplierID").Value = 1
cmd.Execute , , adExecuteNoRecords


[This message has been edited by Gerald (edited 01-19-2000).]

[lenin]

Thanks very much. This looks exactly like what I require.

How can I tell if I am using "MSDASQL"?

[Clunietp]

MSDASQL is the ADO/OLEDB driver for ODBC drivers. It adds another layer to database access, so your calls go thru ADO then ODBC.

If you want to use ADO & the ODBC driver for MS Access (so you can still use a DSN) then you use this method of connecting. Are you using DAO or ADO w/o ODBC?

[lenin]

Thanks for the reply, I'm using ADo with ODBC.