Run Stored Update Query using VBA (from Word)

Hi All

Please can someone tell me how to run a stored update query in an Access database from Word/VBA?  I have got so far but I am clearly missing something.  By the way, the query works correctly if I run it manually from Access.

Thank you for your help as ever.




Public Sub UpdateDetails()
Dim dbMAIN As New ADODB.Connection
Dim qQuery As New ADODB.Command

With dbMAIN
    .ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;" & _
                        "Persist Security Info = False;" & _
                        "Data Source = "C:\Source\Numbering.mdb"
    .Open
End With

With qQuery
    .ActiveConnection = dbMAIN
    .CommandType = adCmdStoredProc
    .CommandText = "qUpdateNumber"
    .Parameters.Append .CreateParameter("NewNumber", adChar, adParamInput, Len("MyNewNumber"), "MyNewNumber")
    .Parameters.Append .CreateParameter("OldNumber", adChar, adParamInput, Len("MyOldNumber"), "MyOldNumber")
    .Parameters.Append .CreateParameter("RecordDate", adChar, adParamInput, Len("MyNewDate"), "MyNewDate")
End With

qQuery.Execute

Set qQuery = Nothing
Set dbMAIN = Nothing
End Sub

Open in new window

MonteDelMarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leigh PurvisDatabase DeveloperCommented:
Hi
What's the definition of qUpdateNumber?
Are you getting any specific error messages?
The one thing I notice right away is that it's astronomically likely that your parameters are operating upon fields of "Text" type in Access. Which is actually a Unicode VarChar.
So
    .Parameters.Append .CreateParameter("NewNumber", adChar, adParamInput, Len("MyNewNumber"), "MyNewNumber")
would be
    .Parameters.Append .CreateParameter("NewNumber", adWVarChar, adParamInput, Len("MyNewNumber"), "MyNewNumber")
and so forth...
Cheers.
0
MonteDelMarAuthor Commented:
Thank you for your response.

I have re-attached the code here to save confusion.  I had renamed some of the fields in the example I sent you as I (mistakenly) thought it would be easier.  

Do you mean the SQL for the Query?  That is

UPDATE ClientNumbers SET ClientNumbers.ArtClientNumber = [NewNumber], ClientNumbers.RenumberDate = [NewDate]
WHERE (((ClientNumbers.ArtClientNumber)=[OldNumber]));

I do not get an error message.  It appears to run but does not update the record.

I just tried changing the Type to adVarWChar but that has not made a difference.
Public Sub UpdateRecord()
Dim dbMAIN As New ADODB.Connection
Dim qQuery As New ADODB.Command

With dbMAIN
    .ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;" & _
                        "Persist Security Info = False;" & _
                        "Data Source =" & strDrive & strLocation & "Numbering.mdb"
    .Open
End With

With qQuery
    .ActiveConnection = dbMAIN
    .CommandType = adCmdStoredProc
    .CommandText = "UpdateClientDetails"
    .Parameters.Append .CreateParameter("NewNumber", adWChar, adParamInput, Len("MyNewClientNumber"), "MyNewClientNumber")
    .Parameters.Append .CreateParameter("OldNumber", adVarWChar, adParamInput, Len("BWC00002"), "BWC00002")
    .Parameters.Append .CreateParameter("RenumberDate", adVarWChar, adParamInput, Len("MyNewDate"), "MyNewDate")
End With

qQuery.Execute

Set qQuery = Nothing
Set dbMAIN = Nothing
End Sub

Open in new window

0
Leigh PurvisDatabase DeveloperCommented:
Your parameters are in the wrong order.
Unless you've explicitly gone with named parameters in your code - then you need to be consistent with the order.
With qQuery
    .ActiveConnection = dbMAIN
    .CommandType = adCmdStoredProc
    .CommandText = "UpdateClientDetails"
    .Parameters.Append .CreateParameter("NewNumber", adVarWChar, adParamInput, Len("MyNewClientNumber"), "MyNewClientNumber")
    .Parameters.Append .CreateParameter("RenumberDate", adDate, adParamInput,  8, "MyNewDate")
    .Parameters.Append .CreateParameter("OldNumber", adVarWChar, adParamInput, Len("BWC00002"), "BWC00002")
    .Execute
End With

If you wanted to be more predictable then define your parameters in the query:

PARAMETERS  [NewNumber] Text(50), [NewDate] DateTime, [OldNumber] Text;
UPDATE ClientNumbers SET ClientNumbers.ArtClientNumber = [NewNumber], ClientNumbers.RenumberDate = [NewDate]
WHERE (((ClientNumbers.ArtClientNumber)=[OldNumber]));
That way you are being very clear about the order of parameters.
The alternative is to specify named parameters in your code.

With qQuery
    .ActiveConnection = dbMAIN
    .CommandType = adCmdStoredProc
    .CommandText = "UpdateClientDetails"
    .NamedParameters = True
    .Parameters.Append .CreateParameter("NewNumber", adVarWChar, adParamInput, 50, "MyNewClientNumber")
    .Parameters.Append .CreateParameter("NewDate", adDate, adParamInput,  8, "MyNewDate")
    .Parameters.Append .CreateParameter("OldNumber", adVarWChar, adParamInput, Len("BWC00002"), "BWC00002")
    .Execute
End With


When you can create them in any order you like.
Note that you need ADO 2.6 or newer referenced to support that.
Note that you parameter named now have to match exactly. (They didn't all do so before, but that didn't matter).
Cheers.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MonteDelMarAuthor Commented:
Brilliant.  Thank you so much.
0
Leigh PurvisDatabase DeveloperCommented:
No problemo
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.