Test Connection to SQL Server

[Ivan Grozney]

I have a A2K7 FE and SQL 05 BE. There is fairly tight security on the SQL
Server for this database. I use Doug Steele's DSNless connection but need to
test if the user is authorized to use/access to the database.

So if User BOB has access, he gets in
If User Gus doesn't, it times out after a while and gives some
error.

I would like Gus to get a quicker response and trap the error and have it
tell him to contact our Risk Management group for access. I don't know where
to start on this.

tia

Vanya

 

[Albert D. Kallal]

You can trap a logon error with sql server using DAO and you can avoid
having that odbc message come up.

How this trick is done is outlined here:

ACC2000: How to Trap ODBC Logon Error Messages
http://support.microsoft.com/kb/210319

I use the following code based on the above KB article:

Function TestLogin(strCon As String) As Boolean

On Error GoTo TestError

Dim dbs          As DAO.Database
Dim qdf          As DAO.QueryDef

Set dbs = CurrentDb()
Set qdf = dbs.CreateQueryDef("")

qdf.Connect = strCon
qdf.ReturnsRecords = False

'Any SQL statement will work below.
qdf.SQL = "SELECT * FROM calls"
qdf.Execute

TestLogin = True

Exit Function

TestError:
TestLogin = False
Exit Function

End Function


To call the above, I would go:


strCon = "ODBC;DRIVER={sql server};" & _
"SERVER=" & ServerName & ";" & _
"DATABASE=" & DatabaseName & ";" & _
"UID=" & UserID & ";" & _
"PWD=" & USERpw & ";" & _
"APP=Microsoft Office 2003;" & _
"WSID=" & WSID

If TestLogin(strCon) = True Then
' logon is ok!

So, the above allows you to "test" your logon. I assume you also have some
built in re-link code that allows you to re-link your tables. You can find
that code here:

The above is nice since it avoids both the long time out and also the system
generated message.

 

[John Spencer]

Albert,

Do you know of a way to modify the code when you are using a DSN-less
connection? The poster specifically said he was using Doug Steele's DSNless
connection method.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Ivan Grozney]

Gents,

Thank you for the prompt response.

In Doug's code it says to use it as long as the copyright is not
changed. So I think I am free to add this snippet to the DSNless code. I
will be giving it a try today or tonight.

 

[Albert D. Kallal]

Albert,

Do you know of a way to modify the code when you are using a DSN-less
connection? The poster specifically said he was using Doug Steele's
DSNless connection method.

The example I gave actually works well even with a DSN less. In fact I only
use DSN less connections with that code snip.

Note in code:

Set dbs = CurrentDb()
Set qdf = dbs.CreateQueryDef("")

qdf.Connect = strCon <--- connection string here is DSN less
qdf.ReturnsRecords = False

In my example you can see that I set the connection setup "strCon" is thus
DSN less

It works VERY well, and it far better then the ODBC timeout msg.

My code does assume you have someplace the connection string setup with
logon info. However, you can/could pull that out of any existing linked
table with ease.

 

[Ivan Grozney]

Albert,

Thanks for all your insight and help.

However, I guess I had too many helpings of Ding Dong flakes this
morning. I cannot get it to work for me.

I have a splash screen with a time so that I can check the SQL
Database for their access level. So that if they are a general user the can
do X and super user Y and if a member of the risk folks they get Z. So I was
trying to put it in there to test before I do all the other stuff but it just
hangs up.

In your first post that you "assume you also have some built in re-link
code... You can find that code here: "

I don't have any re-link code and I didn't see it in your response,
maybe that is the issue.

Any further guidance would be much appreciated.

Vanya

 

[Albert D. Kallal]

Albert,


In your first post that you "assume you also have some built in re-link
code... You can find that code here: "

The re-link code is not really relevant to this issue. I was however
referring to Doug's DSN less re-link code here:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

(so, I did accident leave above link it out...sorry)

I don't have any re-link code and I didn't see it in your response,
maybe that is the issue.

No, the re-link code should not make any difference here.

'Any SQL statement will work below.
qdf.SQL = "SELECT * FROM calls"
qdf.Execute

While I state above that any sql statement should work...you do have to
choose a LEGAL and existing table in sql server. So, perhaps your table is
dbo.Calls for example. I used table Calls, but you have to choose something
that the user/person has access to.

Any further guidance would be much appreciated.

Check you string/setup of:

strCon = "ODBC;DRIVER={sql server};" & _
"SERVER=" & ServerName & ";" & _
"DATABASE=" & DatabaseName & ";" & _
"UID=" & UserID & ";" & _
"PWD=" & USERpw & ";" & _
"APP=Microsoft Office 2003;" & _
"WSID=" & WSID

Perhaps put a debug.print strCon after the above statement. Then create a
pass-though query, and in the connection setting paste in the strCon results
and see if the pass-though query works.

In other words, create the pass-though query. I would also suggest you in
the debug-window go:

debug.print currentdb.TableDefs("name of some linked table").Connect

Take a look at the connection string, and note any differences between it
and what the strCon produces. Perhaps the string needs some tweaking due to
you using the sql server native driver for example.

 

[Ivan Grozney]

Albert,

Thank you. I will check it all again and post back.

Vanya