CODEWITHSUNDEEP
excelvbams-accessms-access-2010excel-2010
roemba
roemba

Reputation: 11

vba access query with parameters throws "invalid procedure" on execute()

I'm building a small Excel 2010 program for my users to quickly generate a timetable from an Access 2010 database. As I have not done many projects like this before I try to adhere to good coding conventions and want to use a stored procedure to get all the information I need from the database.

The code I use (only the relevant parts) is as follows:

Dim courses As New ADODB.Recordset

Set conn = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & fname
conn.Open (strConnection)

Set getCoursescmd = New ADODB.Command
With getCoursescmd
    Set .ActiveConnection = conn
    .CommandText = "getCoursesByYearAndPeriod"
    .CommandType = adCmdStoredProc
    .NamedParameters = True
    .Parameters.Append .CreateParameter("@Weekday", adInteger)
    .Parameters.Append .CreateParameter("@CategoryID", adInteger)
    .Parameters.Append .CreateParameter("@Period", adInteger)
    .Parameters.Append .CreateParameter("@Year", adInteger)
    .Parameters("@Weekday") = WeekDay
    .Parameters("@CategoryID") = CategoryIDs.Fields(0).Value
    .Parameters("@Period") = Blok
    .Parameters("@Year") = Year
    .Prepared = True
End With


Set courses = getCoursescmd.Execute() 'This is where it fails

The SQL query runs perfectly fine in Access 2010 and is as follows (ignore the Dutch variable names):

SELECT Cursussen.*, Weekday(Cursussen.StartDatum,2) AS Dag
FROM Cursussen
WHERE Weekday(Cursussen.StartDatum,2)=[@WeekDay] And Cursussen.Categorie=[@CategoryID] And Cursussen.Blok=[@Period] And Cursussen.Jaar=[@Year]
ORDER BY Cursussen.Naam, Cursussen.Niveau;

I have run multiple other stored queries to the same database which get or update data, even some with parameters. I just cannot seem to figure out why it fails on .Execute(). It throws a run-time error -2147467259 (80004005) Invalid Operation which gives me no clue as to what it is I'm doing wrong here.

Any help would be much appreciated.

Upvotes: 1

Views: 329

Answers (1)

dbmitch
dbmitch

Reputation: 5386

Like @Rory said, you either need to provide the variables inline 

.Parameters.Append .CreateParameter("@Weekday", adInteger, adParamInput, <yourintVar>)

or add them afterwards with 

Dim prmWeekday As ADODB.Parameter
SET prmWeekday = .Parameters.Append .CreateParameter("@Weekday", adInteger)
prmWeekday.Value = <yourIntVar>

Upvotes: 2

Related Questions