Global ADO Connection Object (Public Function) Problems and Options

[darbid]

Looking in "Microsofts Access Developers Guide to SQL Server" there is a recommendation and code for a Global ADO Connection Object.

As I do not want people to have to log in I have taken out the login stuff and so far I have the following.

Option Compare Database
Option Explicit

Public Const LUT_PROVIDER As String = "SQLOLEDB.1"
Public Const LUT_DATA_SOURCE As String = "servername"
Public Const LUT_INITIAL_CATALOG As String = "dbname"
Public Const LUT_USER_ID As String = "username"
Public Const LUT_PASSWORD As String = "PW"


Public Function OpenConnection() As Boolean

On Error GoTo HandleError
Dim boolState As Boolean

If gcnn Is Nothing Then
    Set gcnn = New ADODB.Connection
End If

If gcnn.State = adStateOpen Then
    boolState = True
Else
    gcnn.ConnectionString = "Provider=" & LUT_PROVIDER & "; Data Source=" & LUT_DATA_SOURCE & ";Initial Catalog=" & LUT_INITIAL_CATALOG & "; User ID=" & LUT_USER_ID & ";Password=" & LUT_PASSWORD
    gcnn.Open

    If gcnn.State = adStateOpen Then
        boolState = True
    End If
End If

OpenConnection = boolState

ExitHere:
    Exit Function
HandleError:
    OpenConnection = False
    Err.Raise Err.Number, Err.source, Err.Description, Err.HelpFile, Err.HelpContext
    Resume ExitHere
End Function

This is initiated at start up. Sometimes the program just stops here and does not go any further, no error message nothing it just stops. I assume that it has to do with the SQL Server settings as well which is why I have put it in this forum section.

What could this be, how can I trap this error and/or how can I fix it?

Sometimes while using this connection, if it does not exist (for whatever reason) I get a window to re-enter the SQL Server user name and password (with the password being blank) how can I capture this event and stop it from asking the user for the login information again.

With this second question I am not sure if it is as a result of this connection or as a result of the linked tables - which currently use a DSN File.

 

[Pierrick]

Looking in "Microsofts Access Developers Guide to SQL Server" there is a recommendation and code for a Global ADO Connection Object.

As I do not want people to have to log in I have taken out the login stuff and so far I have the following.

Sometimes while using this connection, if it does not exist (for whatever reason) I get a window to re-enter the SQL Server user name and password (with the password being blank) how can I capture this event and stop it from asking the user for the login information again.

With this second question I am not sure if it is as a result of this connection or as a result of the linked tables - which currently use a DSN File.

Hi darbid,

This may not be what you want to hear, but I had similar problems with my Access 2K <-> MsSQL 2k linking. I posted something, see the thread http://www.access-programmers.co.uk/forums/showthread.php?t=164978
My solution: use the Project route if you can. There doesn't seem to be any connection issue. Once you've set up your ODBC connection to the Server, you're on your way. Of course you still retain the security inherent to the SQL user you select in the connection (if you select the SQL user rather than the Windows user option that is.)
Hope that helps.

 

[LPurvis]

ADPs are a fine technology. Though the have their limitations in that they sacrifice some of what Access can offer under different circumstances (local tables can be a powerful ally in Access - giving it an advantage over other development platforms).

You'll generally have to do more coding in an ADP to acomplish things that an MDB can perform using expressions.
And FWIW ADPs don't use an ODBC connection to the server but an OLEDB connection.

ADP's are also rather version specific. Access 2000 and SQL 2000 work well together. But from then on you need a newer version of Access than the versions of SQL Server to be able to view and alter the design of objects on the server. (There are no local data objects at all).

The code above uses ADO which is establishing an OLEDB connection to the sserver - very similarly to an ADP does.
Chances are there's just some problem establishing that connection at times.
You'll need to determine when and why :-s.
It might be a timeout on the connection at busy times - or some other problem.
Are you able to try using Integrated Security instead of SQL Server verification as you're doing?
A bit more error handling might be helpful at least.

Cheers.

 

[Pierrick]

ADPs are a fine technology. Though the have their limitations in that they sacrifice some of what Access can offer under different circumstances (local tables can be a powerful ally in Access - giving it an advantage over other development platforms).

Indeed local tables can be an ally however when you link a SQL table with millions of rows (my case) Access seriously struggles. ADP does not seem to compromise Access' performance to the same degree. Plus nothing stops me from adding native Access tables if I need.

You'll generally have to do more coding in an ADP to acomplish things that an MDB can perform using expressions.

I do not understand this. What do you mean more coding? It seems to take just as much VBA to work with linked tables as it does to work with ADP. Am I missing something here?

And FWIW ADPs don't use an ODBC connection to the server but an OLEDB connection.

Again please explain. Is that bad?

ADP's are also rather version specific. Access 2000 and SQL 2000 work well together. But from then on you need a newer version of Access than the versions of SQL Server to be able to view and alter the design of objects on the server. (There are no local data objects at all).

That's a very good point.

Apologies if it looks like I've hijacked the thread, but I am quite puzzled by some of your answers. I am after all a newbie at working with Access as front end to MsSQL.

 

[darbid]

Sometimes the program just stops here and does not go any further, no error message nothing it just stops. I assume that it has to do with the SQL Server settings as well which is why I have put it in this forum section.

I still do not know what was happening here. I tried different drivers ODBC and OLEDB and found that with the SQL Server 2000 that the ODBC seemed to work better. At least this problem does not exist anymore.

Sometimes while using this connection, if it does not exist (for whatever reason) I get a window to re-enter the SQL Server user name and password (with the password being blank) how can I capture this event and stop it from asking the user for the login information again.

This actually has nothing to do with the global connection above, but actually with the linked tables. I was using a DSN file and for whatever reason the password was not being saved. I moved to relinking the tables at startup with a similar connection to my connection above WITH the password saved and this solved this problem.

So I now use both linked tables and a global connections. This works great so far.

 

[LPurvis]

Mixed responses to the last two posts.

>> found that with the SQL Server 2000 that the ODBC seemed to work better

Was that using a DSN did you say? You need to use the ODBC provider if you're using a DSN. With ADO on an OLEDB provider you could look to an odc file - but it's much more normal to provide all parameters explicitly.

Of course - when you're using linked tables then you have no alternative - ODBC it is. DSN or DSN-less.

>> Indeed local tables can be an ally however when you link a SQL table with millions of rows (my case) Access seriously struggles.
Define "struggles". Understanding the behaviour behind the scenes helps explain potential behaviour you may have seen. Not everything is as it seems. However there is little arguement that, though they can be a pwerful tool, linked tables are not the most efficient way to make server data requests.

>>ADP does not seem to compromise Access' performance to the same degree.
Again - that depends. You can still make bad requests for data in an ADP that will unecessarily work the server and network.

>>Plus nothing stops me from adding native Access tables if I need.
Huh? Not to the ADP file you can't. That's what I was saying. An ADP file has *no* local data objects - because it has no local data engine. There is literally nothing running locally that can access data. Jet is not present in an ADP.

>>What do you mean more coding? It seems to take just as much VBA to work with linked tables as it does to work with ADP.
OK, a simple example for you.
Suppose you want cascading combos. Very common requirement.
In an MDB (/ACCDB) you can have a rowsource for your second combo of

SELECT ID, FieldName FROM TableName WHERE FKID = Forms!FormName!FirstCombo

(it can actually be simplified - but that's very standard).
With a rowsource like that - the second combo can be filtered by the FirstCombo selection using that expression.
Many folks moving to an ADP without realising what it means are dismayed to find that doesn't work.
Why doesn't it work? Again - there is no local data engine. No locally running expression service in that data engine. SQL Server has no clue what an Access form object is - and hence that expression means nothing to it.
So you need to recode the rowsource for the combo when the FirstCombo changes.

Me.SecondCombo.RowSource = "SELECT ID, FieldName FROM TableName WHERE FKID = " & Forms!FormName!FirstCombo

A simple example of coding what was handled previously by expressions.

>> [ODBC Vs OLEDB connection] Again please explain. Is that bad?
It's just required. The two are the providers that offer connection to the SQL database. Through linked tables you must use ODBC. In an ADP you use an OLEDB connection. It's very common to connection using an OLEDB provider in ADO. ODBC is the older of the two technologies, but they both have massively strong foot-holds now. (FWIW OLEDB can have the subtle performance advantage - but it's not necessarily detectable).


There's the Beginning SQL Server FAQ over the way here if you're interested. No great detail (that's not the intention of it) but bits about this and that.

Cheers.

 

[Pierrick]

Hi Leigh,
Thanks for your answers to my questions. I will be far more cautious with ADP from now on.
So far it has proven a huge benefit over DSN/DSN-less connections. I much prefer to work directly with MsSQL when it comes to data, and use Access for its user front-end (forms and reports.)
Old habit from VB I guess.