PC Review


Reply
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average.

Test Connection to SQL Server

 
 
Ivan Grozney
Guest
Posts: n/a
 
      4th Sep 2009
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

 
Reply With Quote
 
 
 
 
Albert D. Kallal
Guest
Posts: n/a
 
      4th Sep 2009

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:

[code]

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.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)


 
Reply With Quote
 
 
 
 
John Spencer
Guest
Posts: n/a
 
      4th Sep 2009
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

Albert D. Kallal wrote:
> 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

 
Reply With Quote
 
Ivan Grozney
Guest
Posts: n/a
 
      4th Sep 2009
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.

"John Spencer" wrote:

> 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
>
> Albert D. Kallal wrote:
> > 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

>

 
Reply With Quote
 
Albert D. Kallal
Guest
Posts: n/a
 
      4th Sep 2009
"John Spencer" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)


 
Reply With Quote
 
Ivan Grozney
Guest
Posts: n/a
 
      4th Sep 2009
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" wrote:

> "John Spencer" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> (E-Mail Removed)
>
>
>

 
Reply With Quote
 
Albert D. Kallal
Guest
Posts: n/a
 
      5th Sep 2009
"Ivan Grozney" <stormovick (ditch) @hotmail.com> wrote in message
news:(E-Mail Removed)...
> 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.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)


 
Reply With Quote
 
Ivan Grozney
Guest
Posts: n/a
 
      7th Sep 2009
Albert,

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

Vanya

"Albert D. Kallal" wrote:

> "Ivan Grozney" <stormovick (ditch) @hotmail.com> wrote in message
> news:(E-Mail Removed)...
> > 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.
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> (E-Mail Removed)
>
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
An error has occurred while establishing a connection to the server.When connecting to SQL Server 2005, this failure may be caused by the factthat under the default settings SQL Server does not allow remote connections. mina Microsoft VB .NET 0 8th Oct 2008 07:12 AM
An error has occurred while establishing a connection to the server.When connecting to SQL Server 2005, this failure may be caused by the factthat under the default settings SQL Server does not allow remote connections. mina Microsoft Dot NET 0 8th Oct 2008 07:11 AM
test test test test jadfl Microsoft Dot NET Framework 1 6th Jul 2005 06:31 AM
Test Test Test!!!! Zero Windows XP General 2 11th Jul 2004 10:47 AM
Getting TEST!TEST!TEST message.... Narrgirl Windows XP Internet Explorer 1 21st Apr 2004 03:26 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:45 PM.