Struggling with A2002 and SQL Server Stored Procedures

[Kevin_S]

Hi Gang -

I have been struggling with this for a few days now and I am losing my mind so hopefully someone here will be able to give me a hand. I have two problems:

1) Is it possible to use a DSN when creating a connection string instead of listing the provider, db name, server name, password, etc...?

- I have read in a few texts (MS Access 2002 VBA by Wrox books) and other online sources that it is possible to create a connection string with ADO using a preestablished DSN instead of listing all of the connection information... Does anyone know how to do this and would it be possible to see an example?

2) Passing parameters to a stored procedure from Access 2002 has got me baffled can some explain/show example how to do this?

- I have read numerous posts on this forum/SQL Server Mag forums/ articles/white papers/etc... but for some reason I can't translate this to my db. I have created a simple stored procedure in SQL Server 2000 that does an insert on table. I want to fire this stored procedure with the On Click event of a form I have for data entry in Access 2002 and pass in a connection string 3 pieces of data (Name, Country, DOB) to the stored procedure to insert the data in the table but I cant get this to work... Any thoughts ideas?

Any/All help is appreciated.
Thanks -Kev

 

[mission2java_78]

Hi Gang -

I have been struggling with this for a few days now and I am losing my mind so hopefully someone here will be able to give me a hand. I have two problems:

1) Is it possible to use a DSN when creating a connection string instead of listing the provider, db name, server name, password, etc...?

- I have read in a few texts (MS Access 2002 VBA by Wrox books) and other online sources that it is possible to create a connection string with ADO using a preestablished DSN instead of listing all of the connection information... Does anyone know how to do this and would it be possible to see an example?

2) Passing parameters to a stored procedure from Access 2002 has got me baffled can some explain/show example how to do this?

- I have read numerous posts on this forum/SQL Server Mag forums/ articles/white papers/etc... but for some reason I can't translate this to my db. I have created a simple stored procedure in SQL Server 2000 that does an insert on table. I want to fire this stored procedure with the On Click event of a form I have for data entry in Access 2002 and pass in a connection string 3 pieces of data (Name, Country, DOB) to the stored procedure to insert the data in the table but I cant get this to work... Any thoughts ideas?

Any/All help is appreciated.
Thanks -Kev

1) you only need to specify the provider etc and the complete connection string once. Why are you specifying it more than one time? It should be in a module as a public constant. Or you can store it in a text file and read from there.

2)Yes I thought i showed you how to do this using EXEC ?

 

[mission2java_78]

I just created a form in access with a current db in sql server.

I called a sproc like so:

Call EstablishConnection  'you have to make a connection...if
'you need this function let me know

Set objCmd = New ADODB.Command
objConn.CursorLocation = adUseClient

    With objCmd
        .ActiveConnection = objConn
        .CommandText = "select_component_pricing_prices" 'name of sproc
        .CommandType = adCmdStoredProc         'its a stored procedure
        .Parameters.Append .CreateParameter("Component", adVarChar, adParamInput, 50, UCase(Me.txtComp.Text)) 'some parameters
        Set rst = .Execute
        Set rst.ActiveConnection = Nothing
        Call ReleaseConnection  'release the connection to the db
    End With
    
    If rst.BOF Then
        Call ClearFG
        'no records
    Else
        'do somthing
        End If

The Establish and ReleaseConnection functions make it easier on me to create a connection to the database every time.
Have fun!

 

[Kevin_S]

Hey Jon - Nice weather we're having for Oct today ehh?

OK - I think you and I worked on this before and I had a nice example db that was put together during this time but unfortunitly for me I got a new pc and lost a bunch of files when our model office group reimaged my machine with a copy of the old clone even when i told them not too as I have a different setup (long story)...

I was trying to use the DSN instead of passing the connection info in the string because the DSN connects to the server using NT Authentication (which I want) but every example connection string I have ever seen the username and password are imbedded in the string (which I don't want) because I use the users role one the server as the way i restrict access to objects and if I imbed the dbo user name and password am I not going around the user's role? (I'm a little confused here on some of this still but working my way through it)

In regards to the Exec function I did look into this as you suggested but the documentation I found referred to Access Project Files and I am trying to stay away from adp files cuz my DAO kung-fu is stronger then my ADO kung-fu and I'm holding out before converting !!!

If your offering the code for establishing and disconnecting the connection I'm all ears!! (Like I said above I had this a while ago thanks to your help but lost it )

So... using the code example you posted (and I appreciate the example) I pass the parameter to the stored procedure here:

.Parameters.Append .CreateParameter("Component", adVarChar, adParamInput, 50, UCase(Me.txtComp.Text)) 'some parameters

and this reads as
- the parameter's name is "Component"
- type = adVarChar
- 'its an input parameter
- length=50
- 'Upper case
- value=me.txtComp

right?!?!?!?!?!

 

[mission2java_78]

Yes just type the parameters there..you will get a list of options while you type it out for the type (Integer, Character, etc) im not sure what the equivalents are in access because i've been doing work in SQL Server more oftenly.

However, like I said I copied and pasted that right into an access form and looped through a recordset. So yes that is how to call a sproc in access..maybe we should store this thread somewhere I've seen others ask the same question.

here's code to help you connect:

Option Explicit
Public objConn As ADODB.Connection
Public Const dbConnectionString As String = "Provider=SQLOLEDB;Data Source=YOURSQLSERVERHERE;" & _
                                            "Database=YOURDBHERE;UID=CREATEASQLSERVERUSERSTOREHERE;PWD=URUSERPWDHERE"


Public Function EstablishConnection()
On Error GoTo Err_Handler

Set objConn = New ADODB.Connection
objConn.ConnectionString = dbConnectionString
objConn.Open

Done:
Exit Function

Err_Handler:
MsgBox Err.Description, vbCritical, "Error #: " & Err.Number
Resume Done

End Function
Public Function ReleaseConnection()
On Error GoTo Err_Handler

objConn.Close
Set objConn = Nothing

Done:
Exit Function

Err_Handler:
MsgBox Err.Description, vbCritical, "Error #: " & Err.Number
Resume Done

End Function

For every call to EstablishConnection you ahve to ensure there is a call to ReleaseConnection to clean up memory.

As for your dsn...dont do this...just create a single SQL Server login (DO NOT USE THE SA login and default password here)...create one single user for this database as DBO.Owner this user will be used with the connection string provided in the code.

 

[Kevin_S]

As for your dsn...dont do this...just create a single SQL Server login (DO NOT USE THE SA login and default password here)...create one single user for this database as DBO.Owner this user will be used with the connection string provided in the code.

But if I establish the connection without the DSN then my user's will not be registered with the SQL Server so their roles and subsequent permissions will be ignored right? If this is the case then this method is a "No Go" for my situation as I base ALOT of the code in my FE off of their SQL Server Role which I obtain in the front end through pass-through queries.... ugghhh....

You also said to create one user but the only user ID I have access to is mine (granted to me by the Dept. DBA) so I would have to imbed MY SQL Server Login in the string which would result in the users getting my permission to create tables, delete objects, etc... on the server which would be a bad thing...

Is this the case then or can I create the connection string with the imbedded password/username and then still restrict the users rights with the established roles/permissions?

thx Jon,
Kev

 

[mission2java_78]

Kev,

Maybe we need to meet at starbucks...you local?

=)

Why didn't you keep security within the table layer and not at the SQL Server layer? You've made your job a bit more difficult. Meaning...when I create an application I have ONE user ID created through SQL Server which grants all permissions. My application backend stores the applications security for each end user..then I can write my own functions to see who has access to what. Are you saying each user is entered as a user in your users list of SQL ServeR (boy that made sense...) what happens when a new user needs to be added? Do you go into your users list in SQL Server and add another user??? That would seem to be a lot of work...your users should be in a table. There should only be one user in your list of SQL Server users. If this is not the case you can still use the hard coded name to *connect* to the database...and use your NT security to access the objects. The name provided in a string is just to make a connection to the database, however this may bring about problems with ownership of objects...Ill have to research what you've done.

 

[mission2java_78]

Kevin,

Have you tried using a trusted connection string?

For a Trusted Connection

oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"Integrated Security=SSPI"

 

[Kevin_S]

Maybe we need to meet at starbucks...you local?

If your in the Lansing area then your on and I'm buyin'

OK Here is the way Security and users are set up:

I use Enterprise Manager to create all the necessary roles in SQL Server and use these roles to grant permissions (SELECT, INSERT, UPDATE, DELETE, EXECUTE) to objects in SQL Server. Then I create NT groups without any permissions and add these groups to their corresponding roles in the SQL Server environment. Then I add individual user accounts to the NT groups. So the heirarchy looks like this:

- SQL Roles
-- NT Groups
--- Individual User IDs

This way I can have our IT tech manage adding/removing users to the groups and all I have to manage is adding the groups to the appropriate server role. THis way the user is not bothered with passwords and such as it is coved with the DSN and NT Authentication. I use the role on the server side to grant permission and I use the role (which I capture with a pass-though query) in the FE to hide/show objects and other fun stuff...

If this is not the case you can still use the hard coded name to *connect* to the database...and use your NT security to access the objects. The name provided in a string is just to make a connection to the database,

So - by this your saying that the connection string won't override permissions, just creates the connection? (I wonder if I can still use NT Authentication for permissions though..?)

Background: I am trying to do this to (1) speed up process by doing them on the server side and (2) eventually eliminate the granting permission on tables/views and just grant execute permissions on Sprocs instead...

Kev

 

[Kevin_S]

Haven't tried trusted connections but I'm going to right now!

I'll work on a few of these and get back to you when I find out some of the results...

Thanks for the continued Help Jon!
Kev

 

[mission2java_78]

Yikes lansing..im in Auburn Hills...

I know exactly what you're doing...do me a favor...see if the trusted connection works out.

Otherwise I'll figure out something else...the hardcoded uid and pwd...may / may not work...i DONT think it will work because it refers to the single user in your database in SQL Server, in which case all permissions are granted. You have it setup for each different user, that is why I think the trusted connection should work.

Let me know if it does...

if it doesnt well get it...we've yet to miss any of this in the past.

 

[Kevin_S]

Yikes lansing..im in Auburn Hills...

The 'Hills ehh... to rich for a simple civil servant like myself

I'll post back shortly when I have a chance to try out the trusted connection...

won't be here too long though as I'm gonna be in your neighborhood this evening since I'm going to the Palace to see the Pistons Exhibition Opener against LeBron James and the Cleveland Cavs tonight...

As Always... Thanks,
Kev

 

[mission2java_78]

Cool,

Lebron...$$$$ talk about bill gates in basketball.

Pistons need John Barry Back!!! And doug collins

O well...we better win this year!

 

[mission2java_78]

Kevin,

I just got reminded and it hit me...
you build the connection string as the user logs in.

strUserName= InputBox("Username?")
strPass = Inputbox("Password")

then you set the connection string with userID= strUserName
and password with strPass

Jon

 

[Kevin_S]

First off the important stuff:

Pistons need John Barry Back!!!

Don't I know this!!! We trade Jon Barry "Mr Instant Energy and 3 point threat" for whom...? Bobby Sura?!?!? give me a break

Other stuff:

Good Idea on building the connection string on the fly but this wont work either cuz the user never physically enters their ID & password - this is all done through the DSN with NT Authentication. I have my splash (by way of pass-through query) check and see if the user is valid - if they are they are granted access to the db... if the user isn't valid entry is not allow and the db shuts down...

I'm still working on the trusted connection string as this seems so far to be working... I'll follow up when I know more or if you think of anything else let me know...

Thanks,
Kev

 

[mission2java_78]

First off the important stuff:

Don't I know this!!! We trade Jon Barry "Mr Instant Energy and 3 point threat" for whom...? Bobby Sura?!?!? give me a break

Other stuff:

Good Idea on building the connection string on the fly but this wont work either cuz the user never physically enters their ID & password - this is all done through the DSN with NT Authentication. I have my splash (by way of pass-through query) check and see if the user is valid - if they are they are granted access to the db... if the user isn't valid entry is not allow and the db shuts down...

I'm still working on the trusted connection string as this seems so far to be working... I'll follow up when I know more or if you think of anything else let me know...

Thanks,
Kev

Yes...
Here is what you do...you snatch the user name from the OS this is the same as the NT login name. The password is left blank..so you DONT enter it ...

So what you do is..in the splash screen or wherever you make the connection you assign the name of the user by snatching the nt login and for pwd just put nothing pwd=""

Jon

 

[Kevin_S]

Hey Jon-

Ok. Here is the code I have so far:

Private Sub Command4_Click()
Call EstablishConnection

Set objCmd = New ADODB.Command
objConn.CursorLocation = adUseClient

With objCmd
.ActiveConnection = objConn
.CommandText = "sproc_TEST" 'name of sproc
.CommandType = adCmdStoredProc 'its a stored procedure
.Parameters.Append .CreateParameter("@SubmissionID", adnVarChar, adParamInput, 8, Me.txts) 'some parameters
.Parameters.Append .CreateParameter("@Project_Name", adnVarChar, adParamInput, 50, Me.txtp) 'some parameters
Set rst = .Execute
Set rst.ActiveConnection = Nothing
End With
Call ReleaseConnection 'release the connection to the db
End Sub

It is called from a button as I am trying to run the sproc when the user clicks the button following the inert of data into the form. I am receiving the follwoing error:

Error 3708
Parameter object is improperly defined. Inconsisten or incomplete information was provided

and it highlights the first .Parameter.Append line. the only thing I see different from this code to the example you provided is that my fields in SQL Server 2000 are of the nVarChar data type whereas yours were VarChar... this can't be it is it?

Thanks,
Kev

 

[mission2java_78]

Kevin what the heck is adnVarChar?

is it supposed to be adVarChar?

Jon

 

[Kevin_S]

Nahh... Actually I was just takin' shots in the dark and I was doing searches and saw alot of examples of parameter input where they had "adTinyInt" or "adByte" or "adVarChar" as the input datatype so i figured I'd try "adnvarchar"... I forgot to change it back but I get the exact same error message and the same line is highlighted. When I cursor over the line at I hesitate over the nVarChar it pops up this:

nVarChar=empty

This is really getting frustrating....

Thanks Again Jon...
Kev

 

[mission2java_78]

Nahh... Actually I was just takin' shots in the dark and I was doing searches and saw alot of examples of parameter input where they had "adTinyInt" or "adByte" or "adVarChar" as the input datatype so i figured I'd try "adnvarchar"... I forgot to change it back but I get the exact same error message and the same line is highlighted. When I cursor over the line at I hesitate over the nVarChar it pops up this:


This is really getting frustrating....

Thanks Again Jon...
Kev

There is no nvarchar...
post your exact code.

Jon