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.Connectio n
Dim cmd As ADODB.Command
Dim myLinkID as Long
myLink ID = someUniqueID
Set cnn = CurrentProject. Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConne ction = CurrentProject. Connection
cmd.CommandText = "mySPName"
cmd.CommandType = adCmdStoredProc
cmd.Execute , Parameters:=Arr ay(myLinkID),
Options:=adExec uteNoRecords

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]

Re: Connection to SQL Server

"Lauren Quantrell" <laurenquantrel l@hotmail.com> wrote in message
news:47e5bd72.0 404090919.13316 107@posting.goo gle.com...[color=blue]
> 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.Connectio n
> Dim cmd As ADODB.Command
> Dim myLinkID as Long
> myLink ID = someUniqueID
> Set cnn = CurrentProject. Connection
> Set cmd = New ADODB.Command
> Set cmd.ActiveConne ction = CurrentProject. Connection
> cmd.CommandText = "mySPName"
> cmd.CommandType = adCmdStoredProc
> cmd.Execute , Parameters:=Arr ay(myLinkID),
> Options:=adExec uteNoRecords
>
> 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[/color]


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.ActiveConne ction = CurrentProject. Connection
cmd.CommandText = "dbo.mySPNa me"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParam eter("@LinkID", adInteger, adParamInput, ,
myLinkID)
cmd.Parameters. Append prm
cmd.Execute ,, adExecuteNoReco rds

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

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

[Lauren Quantrell]

Re: Connection to SQL Server

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.CreateParam eter("@LinkID", adInteger, adParamInput, , myLinkID)
cmd.Parameters. Append prm




"John Winterbottom" <assaynet@hotma il.com> wrote in message news:<c56o8q$2o b542$1@ID-185006.news.uni-berlin.de>...[color=blue]
> "Lauren Quantrell" <laurenquantrel l@hotmail.com> wrote in message
> news:47e5bd72.0 404090919.13316 107@posting.goo gle.com...[color=green]
> > 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.Connectio n
> > Dim cmd As ADODB.Command
> > Dim myLinkID as Long
> > myLink ID = someUniqueID
> > Set cnn = CurrentProject. Connection
> > Set cmd = New ADODB.Command
> > Set cmd.ActiveConne ction = CurrentProject. Connection
> > cmd.CommandText = "mySPName"
> > cmd.CommandType = adCmdStoredProc
> > cmd.Execute , Parameters:=Arr ay(myLinkID),
> > Options:=adExec uteNoRecords
> >
> > 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[/color]
>
>
> 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.ActiveConne ction = CurrentProject. Connection
> cmd.CommandText = "dbo.mySPNa me"
> cmd.CommandType = adCmdStoredProc
> Set prm = cmd.CreateParam eter("@LinkID", adInteger, adParamInput, ,
> myLinkID)
> cmd.Parameters. Append prm
> cmd.Execute ,, adExecuteNoReco rds
>
> myExit:
> Set prm = Nothing
> Set cmd = Nothing
> Exit Sub
>
> myErr:
> MsgBox Err.Number & " " & Err.Description
> Resume myExit
> End Sub[/color]

[John Winterbottom]

Re: Connection to SQL Server

"Lauren Quantrell" <laurenquantrel l@hotmail.com> wrote in message
news:47e5bd72.0 404091813.67257 300@posting.goo gle.com...[color=blue]
> 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.CreateParam eter("@LinkID", adInteger, adParamInput, ,[/color]
myLinkID)[color=blue]
> cmd.Parameters. Append prm
>
>[/color]


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

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

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

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

[Lauren Quantrell]

Re: Connection to SQL Server

That was a silly question I asked.

answer:
Set prm = cmd.CreateParam eter("@LinkID", adInteger, adParamInput, , myLinkID)
cmd.Parameters. Append prm
Set prm = cmd.CreateParam eter("@LinkFiel d", adInteger, adParamInput, , myLinkField)
cmd.Parameters. Append prm
etc...

lq

laurenquantrell @hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72. 0404091813.6725 7300@posting.go ogle.com>...[color=blue]
> 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.CreateParam eter("@LinkID", adInteger, adParamInput, , myLinkID)
> cmd.Parameters. Append prm
>
>
>
>
> "John Winterbottom" <assaynet@hotma il.com> wrote in message news:<c56o8q$2o b542$1@ID-185006.news.uni-berlin.de>...[color=green]
> > "Lauren Quantrell" <laurenquantrel l@hotmail.com> wrote in message
> > news:47e5bd72.0 404090919.13316 107@posting.goo gle.com...[color=darkred]
> > > 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.Connectio n
> > > Dim cmd As ADODB.Command
> > > Dim myLinkID as Long
> > > myLink ID = someUniqueID
> > > Set cnn = CurrentProject. Connection
> > > Set cmd = New ADODB.Command
> > > Set cmd.ActiveConne ction = CurrentProject. Connection
> > > cmd.CommandText = "mySPName"
> > > cmd.CommandType = adCmdStoredProc
> > > cmd.Execute , Parameters:=Arr ay(myLinkID),
> > > Options:=adExec uteNoRecords
> > >
> > > 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[/color]
> >
> >
> > 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.ActiveConne ction = CurrentProject. Connection
> > cmd.CommandText = "dbo.mySPNa me"
> > cmd.CommandType = adCmdStoredProc
> > Set prm = cmd.CreateParam eter("@LinkID", adInteger, adParamInput, ,
> > myLinkID)
> > cmd.Parameters. Append prm
> > cmd.Execute ,, adExecuteNoReco rds
> >
> > myExit:
> > Set prm = Nothing
> > Set cmd = Nothing
> > Exit Sub
> >
> > myErr:
> > MsgBox Err.Number & " " & Err.Description
> > Resume myExit
> > End Sub[/color][/color]

[Lauren Quantrell]

Re: Connection to SQL Server

That was a silly question I asked.

answer:
Set prm = cmd.CreateParam eter("@LinkID", adInteger, adParamInput, , myLinkID)
cmd.Parameters. Append prm
Set prm = cmd.CreateParam eter("@LinkFiel d", adInteger, adParamInput, , myLinkField)
cmd.Parameters. Append prm
etc...

lq

laurenquantrell @hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72. 0404091813.6725 7300@posting.go ogle.com>...[color=blue]
> 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.CreateParam eter("@LinkID", adInteger, adParamInput, , myLinkID)
> cmd.Parameters. Append prm
>
>
>
>
> "John Winterbottom" <assaynet@hotma il.com> wrote in message news:<c56o8q$2o b542$1@ID-185006.news.uni-berlin.de>...[color=green]
> > "Lauren Quantrell" <laurenquantrel l@hotmail.com> wrote in message
> > news:47e5bd72.0 404090919.13316 107@posting.goo gle.com...[color=darkred]
> > > 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.Connectio n
> > > Dim cmd As ADODB.Command
> > > Dim myLinkID as Long
> > > myLink ID = someUniqueID
> > > Set cnn = CurrentProject. Connection
> > > Set cmd = New ADODB.Command
> > > Set cmd.ActiveConne ction = CurrentProject. Connection
> > > cmd.CommandText = "mySPName"
> > > cmd.CommandType = adCmdStoredProc
> > > cmd.Execute , Parameters:=Arr ay(myLinkID),
> > > Options:=adExec uteNoRecords
> > >
> > > 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[/color]
> >
> >
> > 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.ActiveConne ction = CurrentProject. Connection
> > cmd.CommandText = "dbo.mySPNa me"
> > cmd.CommandType = adCmdStoredProc
> > Set prm = cmd.CreateParam eter("@LinkID", adInteger, adParamInput, ,
> > myLinkID)
> > cmd.Parameters. Append prm
> > cmd.Execute ,, adExecuteNoReco rds
> >
> > myExit:
> > Set prm = Nothing
> > Set cmd = Nothing
> > Exit Sub
> >
> > myErr:
> > MsgBox Err.Number & " " & Err.Description
> > Resume myExit
> > End Sub[/color][/color]