Connection to SQL Server

[Lauren Quantrell]

I'm hoping someone can tell me if this is the right or best way to do
this: (and do I want or need the Set cmd = Nothing, cnn.close, Set cnn
= Nothing???)
I'm setting cnn using the CurrentProject.Connection I don't see it
referenced to later...
I have dozens of functions cunstructed like this - are they all bogus?

Function myFunctionName()
On Err GoTo myErr

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim myLinkID as Long
myLink ID = someUniqueID
Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "mySPName"
cmd.CommandType = adCmdStoredProc
cmd.Execute , Parameters:=Array(myLinkID),
Options:=adExecuteNoRecords

myExit:
On Error Resume Next
Set cmd = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub
myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Sub

[John Winterbottom]

"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47**************************@posting.google.c om...

I'm hoping someone can tell me if this is the right or best way to do
this: (and do I want or need the Set cmd = Nothing, cnn.close, Set cnn
= Nothing???)
I'm setting cnn using the CurrentProject.Connection I don't see it
referenced to later...
I have dozens of functions cunstructed like this - are they all bogus?

Function myFunctionName()
On Err GoTo myErr

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim myLinkID as Long
myLink ID = someUniqueID
Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "mySPName"
cmd.CommandType = adCmdStoredProc
cmd.Execute , Parameters:=Array(myLinkID),
Options:=adExecuteNoRecords

myExit:
On Error Resume Next
Set cmd = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub
myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Sub

Unless you need some specific feature of the connection object you can just
create the command using CurrentProject.Connection. I like specify
parameters explicity - I think it is slightly faster since the code does not
need to query the server to determine data types, but we do this because it
helps when debugging to see what the data types are, and whether the params
are input or output. Also, i would qualify the procedure name, (usually this
means putting "dbo." in front). So i would do soemthing like this,

Sub mySubName()
On Err GoTo myErr

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim myLinkID as Long
myLink ID = someUniqueID
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "dbo.mySPName"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("@LinkID", adInteger, adParamInput, ,
myLinkID)
cmd.Parameters.Append prm
cmd.Execute ,, adExecuteNoRecords

myExit:
Set prm = Nothing
Set cmd = Nothing
Exit Sub

myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Sub

[Lauren Quantrell]

John,
Thanks for your answer. I will change all my code.
But how do I write this for SPs with more than one param?

Set prm = cmd.CreateParameter("@LinkID", adInteger, adParamInput, , myLinkID)
cmd.Parameters.Append prm


"John Winterbottom" <as******@hotmail.com> wrote in message news:<c5*************@ID-185006.news.uni-berlin.de>...

"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47**************************@posting.google.c om...

I'm hoping someone can tell me if this is the right or best way to do
this: (and do I want or need the Set cmd = Nothing, cnn.close, Set cnn
= Nothing???)
I'm setting cnn using the CurrentProject.Connection I don't see it
referenced to later...
I have dozens of functions cunstructed like this - are they all bogus?

Function myFunctionName()
On Err GoTo myErr

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim myLinkID as Long
myLink ID = someUniqueID
Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "mySPName"
cmd.CommandType = adCmdStoredProc
cmd.Execute , Parameters:=Array(myLinkID),
Options:=adExecuteNoRecords

myExit:
On Error Resume Next
Set cmd = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub
myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Sub

Unless you need some specific feature of the connection object you can just
create the command using CurrentProject.Connection. I like specify
parameters explicity - I think it is slightly faster since the code does not
need to query the server to determine data types, but we do this because it
helps when debugging to see what the data types are, and whether the params
are input or output. Also, i would qualify the procedure name, (usually this
means putting "dbo." in front). So i would do soemthing like this,

Sub mySubName()
On Err GoTo myErr

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim myLinkID as Long
myLink ID = someUniqueID
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "dbo.mySPName"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("@LinkID", adInteger, adParamInput, ,
myLinkID)
cmd.Parameters.Append prm
cmd.Execute ,, adExecuteNoRecords

myExit:
Set prm = Nothing
Set cmd = Nothing
Exit Sub

myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Sub

[John Winterbottom]

"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47**************************@posting.google.c om...

John,
Thanks for your answer. I will change all my code.
But how do I write this for SPs with more than one param?

Set prm = cmd.CreateParameter("@LinkID", adInteger, adParamInput, , myLinkID) cmd.Parameters.Append prm

add the additional parameters to the parameters collection in the same way:

Set prm = cmd.CreateParameter("@param1", adInteger, adParamInput, ,
param1Value)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("@param2", adVarchar, adParamInput, 50,
param2Value)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("@param3", adDouble, adParamInput, ,
param3Value)
cmd.Parameters.Append prm

[Lauren Quantrell]

That was a silly question I asked.

answer:
Set prm = cmd.CreateParameter("@LinkID", adInteger, adParamInput, , myLinkID)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@LinkField", adInteger, adParamInput, , myLinkField)
cmd.Parameters.Append prm
etc...

lq

la*************@hotmail.com (Lauren Quantrell) wrote in message news:<47**************************@posting.google. com>...

John,
Thanks for your answer. I will change all my code.
But how do I write this for SPs with more than one param?

Set prm = cmd.CreateParameter("@LinkID", adInteger, adParamInput, , myLinkID)
cmd.Parameters.Append prm


"John Winterbottom" <as******@hotmail.com> wrote in message news:<c5*************@ID-185006.news.uni-berlin.de>...

"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47**************************@posting.google.c om...

I'm hoping someone can tell me if this is the right or best way to do
this: (and do I want or need the Set cmd = Nothing, cnn.close, Set cnn
= Nothing???)
I'm setting cnn using the CurrentProject.Connection I don't see it
referenced to later...
I have dozens of functions cunstructed like this - are they all bogus?

Function myFunctionName()
On Err GoTo myErr

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim myLinkID as Long
myLink ID = someUniqueID
Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "mySPName"
cmd.CommandType = adCmdStoredProc
cmd.Execute , Parameters:=Array(myLinkID),
Options:=adExecuteNoRecords

myExit:
On Error Resume Next
Set cmd = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub
myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Sub

Unless you need some specific feature of the connection object you can just
create the command using CurrentProject.Connection. I like specify
parameters explicity - I think it is slightly faster since the code does not
need to query the server to determine data types, but we do this because it
helps when debugging to see what the data types are, and whether the params
are input or output. Also, i would qualify the procedure name, (usually this
means putting "dbo." in front). So i would do soemthing like this,

Sub mySubName()
On Err GoTo myErr

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim myLinkID as Long
myLink ID = someUniqueID
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "dbo.mySPName"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("@LinkID", adInteger, adParamInput, ,
myLinkID)
cmd.Parameters.Append prm
cmd.Execute ,, adExecuteNoRecords

myExit:
Set prm = Nothing
Set cmd = Nothing
Exit Sub

myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Sub

[Lauren Quantrell]

That was a silly question I asked.

answer:
Set prm = cmd.CreateParameter("@LinkID", adInteger, adParamInput, , myLinkID)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@LinkField", adInteger, adParamInput, , myLinkField)
cmd.Parameters.Append prm
etc...

lq

la*************@hotmail.com (Lauren Quantrell) wrote in message news:<47**************************@posting.google. com>...

John,
Thanks for your answer. I will change all my code.
But how do I write this for SPs with more than one param?

Set prm = cmd.CreateParameter("@LinkID", adInteger, adParamInput, , myLinkID)
cmd.Parameters.Append prm


"John Winterbottom" <as******@hotmail.com> wrote in message news:<c5*************@ID-185006.news.uni-berlin.de>...

"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47**************************@posting.google.c om...

I'm hoping someone can tell me if this is the right or best way to do
this: (and do I want or need the Set cmd = Nothing, cnn.close, Set cnn
= Nothing???)
I'm setting cnn using the CurrentProject.Connection I don't see it
referenced to later...
I have dozens of functions cunstructed like this - are they all bogus?

Function myFunctionName()
On Err GoTo myErr

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim myLinkID as Long
myLink ID = someUniqueID
Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "mySPName"
cmd.CommandType = adCmdStoredProc
cmd.Execute , Parameters:=Array(myLinkID),
Options:=adExecuteNoRecords

myExit:
On Error Resume Next
Set cmd = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub
myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Sub

Unless you need some specific feature of the connection object you can just
create the command using CurrentProject.Connection. I like specify
parameters explicity - I think it is slightly faster since the code does not
need to query the server to determine data types, but we do this because it
helps when debugging to see what the data types are, and whether the params
are input or output. Also, i would qualify the procedure name, (usually this
means putting "dbo." in front). So i would do soemthing like this,

Sub mySubName()
On Err GoTo myErr

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim myLinkID as Long
myLink ID = someUniqueID
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "dbo.mySPName"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("@LinkID", adInteger, adParamInput, ,
myLinkID)
cmd.Parameters.Append prm
cmd.Execute ,, adExecuteNoRecords

myExit:
Set prm = Nothing
Set cmd = Nothing
Exit Sub

myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Sub