By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,318 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

Getting Trusted Connection log on when not wanted

P: n/a
Hi and thanks in advance!
I have an Access 2000 front end and a SQL Server 2000 back end. When
the Access main form opens I run code that logs the user on to the SQL
server using the generic read only log on I have created. On some PCs
I am having a problem. The code runs and then displays a Log On box
with the Trusted Connection checked and the users NT logon displayed.
What causes this? Is there a way to correct it?

Thanks!
Kathy
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Hi Kathy,

I had this same issue a couple of weeks ago. Just running a routine to
login into the server is not enough. I'm presuming you have linked ODBC
tables? I ended up creating a procedure to basically re-link the tables, or
rather, refresh the link to the tables using a file dsn, and running that at
startup. Since the connection is cached for the session, the users won't be
prompted to logon. The file dsn was just the option I selected, but you
could actually embed the connection string in your module code and not have
a dsn file.

I would copy and paste the code, but I'm at home and my project is at work.
If this gives you a good start - great! If you need a sample to go by, let
me know and I'll paste it in the morning.

--
James Lankford
"KathyK" <ka*************@nike.com> wrote in message
news:df**************************@posting.google.c om...
Hi and thanks in advance!
I have an Access 2000 front end and a SQL Server 2000 back end. When
the Access main form opens I run code that logs the user on to the SQL
server using the generic read only log on I have created. On some PCs
I am having a problem. The code runs and then displays a Log On box
with the Trusted Connection checked and the users NT logon displayed.
What causes this? Is there a way to correct it?

Thanks!
Kathy

Nov 12 '05 #2

P: n/a
"James Lankford" <dr*********@hotmail.com> wrote in message news:<vp************@corp.supernews.com>...
James,
If it's not too much trouble I would love to see the code!
Kathy
Hi Kathy,

I had this same issue a couple of weeks ago. Just running a routine to
login into the server is not enough. I'm presuming you have linked ODBC
tables? I ended up creating a procedure to basically re-link the tables, or
rather, refresh the link to the tables using a file dsn, and running that at
startup. Since the connection is cached for the session, the users won't be
prompted to logon. The file dsn was just the option I selected, but you
could actually embed the connection string in your module code and not have
a dsn file.

I would copy and paste the code, but I'm at home and my project is at work.
If this gives you a good start - great! If you need a sample to go by, let
me know and I'll paste it in the morning.

--
James Lankford
"KathyK" <ka*************@nike.com> wrote in message
news:df**************************@posting.google.c om...
Hi and thanks in advance!
I have an Access 2000 front end and a SQL Server 2000 back end. When
the Access main form opens I run code that logs the user on to the SQL
server using the generic read only log on I have created. On some PCs
I am having a problem. The code runs and then displays a Log On box
with the Trusted Connection checked and the users NT logon displayed.
What causes this? Is there a way to correct it?

Thanks!
Kathy

Nov 12 '05 #3

P: n/a
Option Compare Database
Option Explicit

Public Function RefreshLinks() As Boolean

On Error GoTo Err_RefreshLinks

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnectString As String, strReadConnectString As String, fOdbc As
Boolean
Dim dbTargetDB As DAO.Database
Dim fs, a, retstring
Dim strFileName As String
Dim fLinkFound As Boolean

RefreshLinks = False

' -- If you want to hard code your ODBC link, then you can basically
omit all the code within
' -- Section I, and use a variable to hold your ODBC string for use in
Section II.

' -- Section I --
strFileName = Application.CurrentProject.Path & "\myDsnFileName.dsn"

Const ForReading = 1, ForWriting = 2, ForAppending = 3

Set fs = CreateObject("Scripting.FileSystemObject")

strConnectString = ""
fOdbc = False

Set a = fs.OpenTextFile(strFileName, ForReading, False)

Do While a.AtEndOfStream <> True
strReadConnectString = a.ReadLine
If Mid(strReadConnectString, 1, 1) = "[" Then
If Mid(strReadConnectString, 1, 6) = "[ODBC]" Then
fOdbc = True
Else
fOdbc = False
End If
Else
If fOdbc Then
If Len(strConnectString) > 0 Then
strConnectString = strConnectString & ";" &
strReadConnectString
Else
strConnectString = strReadConnectString
End If
End If
End If
Loop
a.Close
Set fs = Nothing

' -- End Section I

' -- Start Section II
' Loop through all tables in the database.
Set dbs = CurrentDb
fLinkFound = False
For Each tdf In dbs.TableDefs

' If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
' -- I looked for a specific name in this case because I didn't
necessary want
' -- to refresh all my linked tables, in which I had some linked
..xls and .mdb files.
' -- I was looking for a specific table name. Modify as needed.
If tdf.Name = "<somename>" Then
fLinkFound = True
tdf.Connect = "ODBC;" & strConnectString
Err = 0

On Error Resume Next
' Relink the table.
tdf.RefreshLink

RefreshLinks = (Err = 0)
End If

End If
Next

Set dbTargetDB = Nothing

If fLinkFound = False Then
' -- Do something
End If

' -- End Section II

Exit_RefreshLinks:
Exit Function

Err_RefreshLinks:

MsgBox ("<Some message>")

Resume Exit_RefreshLinks

End Function

Hope this helps,
--
James Lankford
"KathyK" <ka*************@nike.com> wrote in message
news:df**************************@posting.google.c om...
"James Lankford" <dr*********@hotmail.com> wrote in message news:<vp************@corp.supernews.com>... James,
If it's not too much trouble I would love to see the code!
Kathy
Hi Kathy,

I had this same issue a couple of weeks ago. Just running a routine to login into the server is not enough. I'm presuming you have linked ODBC
tables? I ended up creating a procedure to basically re-link the tables, or rather, refresh the link to the tables using a file dsn, and running that at startup. Since the connection is cached for the session, the users won't be prompted to logon. The file dsn was just the option I selected, but you
could actually embed the connection string in your module code and not have a dsn file.

I would copy and paste the code, but I'm at home and my project is at work. If this gives you a good start - great! If you need a sample to go by, let me know and I'll paste it in the morning.

--
James Lankford
"KathyK" <ka*************@nike.com> wrote in message
news:df**************************@posting.google.c om...
Hi and thanks in advance!
I have an Access 2000 front end and a SQL Server 2000 back end. When
the Access main form opens I run code that logs the user on to the SQL
server using the generic read only log on I have created. On some PCs
I am having a problem. The code runs and then displays a Log On box
with the Trusted Connection checked and the users NT logon displayed.
What causes this? Is there a way to correct it?

Thanks!
Kathy

Nov 12 '05 #4

P: n/a
You don't have to relink all the tables. Just one is sufficient

James Lankford wrote:

Hi Kathy,

I had this same issue a couple of weeks ago. Just running a routine to
login into the server is not enough. I'm presuming you have linked ODBC
tables? I ended up creating a procedure to basically re-link the tables, or
rather, refresh the link to the tables using a file dsn, and running that at
startup. Since the connection is cached for the session, the users won't be
prompted to logon. The file dsn was just the option I selected, but you
could actually embed the connection string in your module code and not have
a dsn file.

I would copy and paste the code, but I'm at home and my project is at work.
If this gives you a good start - great! If you need a sample to go by, let
me know and I'll paste it in the morning.

--
James Lankford

"KathyK" <ka*************@nike.com> wrote in message
news:df**************************@posting.google.c om...
Hi and thanks in advance!
I have an Access 2000 front end and a SQL Server 2000 back end. When
the Access main form opens I run code that logs the user on to the SQL
server using the generic read only log on I have created. On some PCs
I am having a problem. The code runs and then displays a Log On box
with the Trusted Connection checked and the users NT logon displayed.
What causes this? Is there a way to correct it?

Thanks!
Kathy

Nov 12 '05 #5

P: n/a
Yes, good point - thanks.

--
James Lankford
"Norman Scheinin" <no***************@boeing.com> wrote in message
news:3F***************@boeing.com...
You don't have to relink all the tables. Just one is sufficient

James Lankford wrote:

Hi Kathy,

I had this same issue a couple of weeks ago. Just running a routine to login into the server is not enough. I'm presuming you have linked ODBC
tables? I ended up creating a procedure to basically re-link the tables, or rather, refresh the link to the tables using a file dsn, and running that at startup. Since the connection is cached for the session, the users won't be prompted to logon. The file dsn was just the option I selected, but you
could actually embed the connection string in your module code and not have a dsn file.

I would copy and paste the code, but I'm at home and my project is at work. If this gives you a good start - great! If you need a sample to go by, let me know and I'll paste it in the morning.

--
James Lankford

"KathyK" <ka*************@nike.com> wrote in message
news:df**************************@posting.google.c om...
Hi and thanks in advance!
I have an Access 2000 front end and a SQL Server 2000 back end. When
the Access main form opens I run code that logs the user on to the SQL
server using the generic read only log on I have created. On some PCs
I am having a problem. The code runs and then displays a Log On box
with the Trusted Connection checked and the users NT logon displayed.
What causes this? Is there a way to correct it?

Thanks!
Kathy

Nov 12 '05 #6

P: n/a

I found a fix! I deleted the links to the tables and when I linked them
again I checked the "Save Password" checkbox. Then voila! No more error.
I have never done this before and never had a problem until now.
Interestingly, the problem did not occur on every pc trying to open the
database. So I'm wondering if the real cause is some difference in the
settings on the PC's.

Thanks again for your great advice and code.
Kathy
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #7

P: n/a
Did you create a System or a User DSN?

You know, I don't know if I've ever noticed the Save Password checkbox
before! It's pretty discreet down there in the corner by itself. Thanks,
I'll have to remember that myself.

--
James Lankford
"Kathy Kashmaier" <ka*************@nike.com> wrote in message
news:3f*********************@news.frii.net...

I found a fix! I deleted the links to the tables and when I linked them
again I checked the "Save Password" checkbox. Then voila! No more error.
I have never done this before and never had a problem until now.
Interestingly, the problem did not occur on every pc trying to open the
database. So I'm wondering if the real cause is some difference in the
settings on the PC's.

Thanks again for your great advice and code.
Kathy
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.