Supply parameter with Command object

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Supply parameter with Command object

Post by agibsonsw »

Hello. (Access 2003)
I've created a parameter query with the parameter [Which surname].
I'm trying to Execute a Command (ADO) that supplies this parameter:
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandType = "qrySurnames"
.Parameters.Refresh
.Parameters("[Which surname]") = strName
set rs = cmd.Execute
, but the recordset returns no records.
Is it possible for the Command object to supply an Access query parameter? Thanks, Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78678
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Supply parameter with Command object

Post by HansV »

The name of the query should be the CommandText property of the Command object, and the CommandType property should be adCmdStoredProc.

And you have to create a Parameter object:

Code: Select all

Dim prm As ADODB.Parameter
With cmd
  .ActiveConnection = CurrentProject.Connection
  .CommandText = "qrySurnames"
  .CommandType = adCmdStoreProc
  Set prm = .CreateParameter("Which Surname", adVarChar, 20, strName)
  .Parameters.Append prm
  Set rs = .Execute
End With
Best wishes,
Hans

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Supply parameter with Command object

Post by agibsonsw »

My mistake in copying - I had .CommandType = adCmdTable and .CommandText="qrySurnames".
I'll try this, thanks, Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Supply parameter with Command object

Post by agibsonsw »

HansV wrote:The name of the query should be the CommandText property of the Command object, and the CommandType property should be adCmdStoredProc.

And you have to create a Parameter object:

Code: Select all

Dim prm As ADODB.Parameter
With cmd
  .ActiveConnection = CurrentProject.Connection
  .CommandText = "qrySurnames"
  .CommandType = adCmdStoreProc
  Set prm = .CreateParameter("Which Surname", adVarChar, 20, strName)
  .Parameters.Append prm
  Set rs = .Execute
End With
Hi, still no joy. Is there a comma missing in the CreateParameter call? Will the number 20 affect the outcome? Ta Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78678
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Supply parameter with Command object

Post by HansV »

20 is the maximum length of the parameter; you can increase it if necessary.
Does it help if you change the name of the parameter from "Which Surname" to "[Which Surname]" ?
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78678
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Supply parameter with Command object

Post by HansV »

Oh, and my bad - ActiveConnection is an object, so you should change

.ActiveConnection = CurrentProject.Connection

to

Set .ActiveConnection = CurrentProject.Connection
Best wishes,
Hans

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Supply parameter with Command object

Post by agibsonsw »

Hi.
I'd still prefer to get my original version to work if possible. I've used Set .ActiveConnection but I'm not sure that this is required.
Debugging I can check the parameter count (1) and Parameter(0).Name and Parameter(0).Value are correct.
Even though the RecordCount is -1, I can see that rs!FirstName and rs!Surname do pick up the correct first row.
I think I'm quite close?! (Sorry to be persistent..) Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78678
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Supply parameter with Command object

Post by HansV »

RecordCount is not dependable until you've moved to the last record.
Best wishes,
Hans

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Supply parameter with Command object

Post by agibsonsw »

I can .MoveNext to discover the records but not .MoveLast - 'Rowset does not support fetching backwards'?
I also notice that rs.CacheSize is 1. Any further thoughts? Ta, Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78678
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Supply parameter with Command object

Post by HansV »

What do you want to accomplish? Do you want to do something with the records, or do you only want to know the number of records?
Best wishes,
Hans

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Supply parameter with Command object

Post by agibsonsw »

For the moment I just want to know the number of records.
Some research is indicating that the default cursor type for a recordset is Forward-Only. I tried:
rs.CursorType = adOpenStatic, then
Set rs = cmd.Execute
but perhaps setting rs = cmd.Execute 're-sets' the cursor type? Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78678
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Supply parameter with Command object

Post by HansV »

If you want to know the number of records, you can use DCount, or open a recordset on a totals query that counts the records. That's much easier and also much more efficient than opening a recordset with all the records and trying to find out how many records it contains.
Best wishes,
Hans

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Supply parameter with Command object

Post by agibsonsw »

I'm studying the Command object and specifying parameters.
I blame Access 2007 VBA Programmer's Reference by Wrox. Their code example(s) are wrong I think. They use 'If rs.RecordCount < 1' but don't actually then display the
RecordCount. I think their procedures are wrong.
Thanks for the assistance again, Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78678
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Supply parameter with Command object

Post by HansV »

I generally find DAO a lot easier to work with than ADODB. Since Access 2003 it is the default data object model in Access again...
Best wishes,
Hans

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Supply parameter with Command object

Post by agibsonsw »

I'm studying both DAO and ADO.
I found advice that it is preferable to use DAO within the Access/ Jet database and ADO when connecting to external data sources.
I'm very much finding this the case, although the book I have constantly uses the current project's connection in the ADO examples - I don't think this
is helping my studies. Ta, Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78678
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Supply parameter with Command object

Post by HansV »

ADO is indeed more flexible with other data sources such as Excel and text files, and it has capabilities that are lacking in DAO.
But DAO is both easier to use and more powerful with Jet databases (Access), so within Access I use DAO exclusively.
Best wishes,
Hans