Application Name in connection string

[roh_8_it_3]

Hello All,

I have an adp project as a front end and sql server 2000 as the backend.I have several ASP.Net apps talking to sql server as well.

I have included the Application Name in connection string on the .Net apps and when I execute the sp_who2 stored procedure ,it correctly shows me the application name in the Program Name column.I want to do the same from the adp application so i can track the connections.

Here is what I did in the adp project.I removed the connection under the File --> connection and I am making connection like this when a user is logging as the first time -

strconn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=myuser;Pwd=mypwd;Initial Catalog=mydb;Data Source=myserver;Application Name=myapp"

If Application.CurrentProject.BaseConnectionString = "" Then
Application.CurrentProject.OpenConnection strconn
end if


This works well and opens the connection to the sql server.But when I execute the sp_who2 ,it shows the progam name as Microsoft Access 2003 and not as myapp.

Any help on this why this connection is not showing the app name that I provided in the connection string?

Thanks

 

[LPurvis]

I'd say that although you have a carefully constructed connectionstring - much as you would use from your ASP.NET code - you instruct the ADP to open a connection using that connection string but the connection the application maintains doesn't necessarily use or expose all the parameters of that string.

For example if you examine the connection string once opened - e.g.
?Application.CurrentProject.Connection.ConnectionString
Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data Source=ServerName;User ID=UserName;Initial Catalog=DBName;Data Provider=SQLOLEDB.1

?Application.CurrentProject.BaseConnectionString
PROVIDER=SQLOLEDB.1;PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=DBName;DATA SOURCE=ServerName

The ADP is maintaining it's own application connection - using the parameters provided.
You can choose to open and maintain your own connection in the application. (Which would maintain the parameters you assigned).
Perhaps create a function which returns that connection - rather than using then
CurrentProject.Connection object.
Of course - to easily bind your forms and reports - you'll need to establish your application's connection (and unfortunately you can't assign the connection - only let it open with the OpenConnection method.

A shame. But hey - not many would cite ADPs as being perfect. ;-)

Cheers.

 

[roh_8_it_3]

sorry i was working on other things and thanks for your reply.I understand it now that since all the properties are not exposed so its not taking the Application Name propertry.
I have an adp project project but i dont want the user to enter the data base password because of two reasons -1 .Its an extra step for them to access the application and 2 security,if they know the db userid/password and the name then using any sql client they can connect to the db.

For this what I have thought that I will create a access connetion like this -

If Application.CurrentProject.BaseConnectionString = "" Then
Application.CurrentProject.OpenConnection strConn
End If

strconn is my application connection string.Then i have a global function to create an use ADO connection explicitly.At present this opens multiple conenctions - access in built connection and then the ADO connection though the VBA.I am thinking to change it to use the already established connection and set it like -

set gobalvbaconnection=Application.CurrentProject.Connection

I think this help in performance of the application as now i will be opening half of the connections.Please let me know your thoughts.

One more question -if we make a connection via the Data Link then we have certain options like we can specify the timeout.How will I set a timeout in my case.I know we can set a timeout in the connection and command timeout in connection string (at least in asp.net) but then the command timeout needs to be set on every time we doing a data base transaction.I think i can do the same in the VBA code but how the timeout will be determined for the bound forms?

Thanks for your help..

 

[LPurvis]

Just generally - yes if you can share and use the application connection that all the better for efficiency. (You want as few connections to the server overall in general - let alonee from your single application ;-)

It's your choice to open and close individual connections or persist a single one.
Opinion is both generally divided and agreed in specific that it depends on the situation at hand.
The application connection is always there to be retrieved - but it's up to Access when and if it's dropped between requests. But any time you request it - it will be returned to you, even of Access has only just re-connected for that request.

As for connection properties like that Command timeout, (bear in mind that I don't use ADPs for live work), I seem to recall the "General Timeout" connection property is perhaps intended as such - however may not necessarily be effective.

 

[roh_8_it_3]

Thanks for your reply.Yes ,less open connectionns on sql server is always good for performance.As I described,in our adp clients we are opening the connection through the Data Link and through VBA as well.So I have thought to change this and the VBA will share the connection that access has already opened .One thing I have noticed ,access never drops its connection to the sql server as long as the client is open.I have checked this with the sp_who2 sp and also by running the profiler.well,I guess i have to implment a kind of session like we have in web apps.That another topic and not related to this.

On data link,we do have connection timout and general timeout.Is there is somethign that i can write in the connection string and it will have the same effect as those properties.Also would that be applicaple to all the connections - whether opened through VBA or the opened by access?

Your help is highly appreciated.

 

[LPurvis]

Hi. Yeah, you're using a single opened connection in VBA that you persist alongside the project's connection. Not ideal obviously (two connections to the same source from the single application) but you're desperate to get that parameter in for the sp_who2 SP - so there it is... ;-)

As for the Command Timeout - I'm unsure how that would be set other than through the UI for the built in connection.
For your own connection you'd set that independently and it wouldn't be limited by the application connection property.

 

[roh_8_it_3]

I think I can change the inbuilt OLE timeout like -

Application.SetOption "OLE/DDE Timeout (sec)", 1

This is working and my bound forms,queries and reports are timing out as I set the limit to one second.

Actually,this is maintenace project project,for VBA they created the connection in the Global and using it everywhere on the forms .By default they didnt use the connection or the command timeout property on any ado db transaction.
I have cheked the code ,by default the connection object has 0 as connection and 30 as command timeout.So this means the command should get timeout after 30 seconds in absence of any explicitly defined timeout but its not .I am not receiving the timeout in vba even when the queries are exceeding the 30 s limit.

Thanks all for your help.

 

[LPurvis]

Yeah that's the OLE/DDE timeout that's available in MDBs too. (As you say - definable using SetOption). I deliberately didn't mention it - in this case due to its generic nature. (i.e. I'd expect it to affect such data connections from external locations - such as Mailmerges with Word).

Does it really affect the Command Timeout in ADPs???

Or are you saying in the rest of your post that you're not actually seeing any effect based upon it?

 

[roh_8_it_3]

sorry,I didnt clearly explained it.The OLE /DD property is affecting the bound forms,queries and reports.I think the bound forms,queries and reports use the Application connection .I am refering it to the built in access connection here.So it seems like setting this value has affect on all the objects that use the application connection instead of a connection created through the VBA code.
I have tested it on one query and a bound form.when I set it to one second,the adp just showed the hour glass and then it went away,indicating that a timeout occured.When I increased it to few seconds ,adp displayed me the forms.

The second part of my post is related to the VBA connection explicity created for the db use.Now ,I am passing the currentproject.accessconnection to the ado connection object.My first thought was since I already set the OLE timeout in the application connection so the vba connection will inherit it from there.But no I was wrong,when i check the values in the code ,it shows me that connection timeout is set to 0 and command timeout to 30.
Now as I was explaining that I have a large ado transactions scattered all over the application.In these ,we havent explicitly set the connection or the command tmeout,so I think these properties should be set to the default value that is 0 and 30 for connection and command.

But no this isnt happening.I know some queries that take more than 30 seconds but the vba code is not timing out.

Thanks!