ODBC Connection in code sometimes prompts for password

We're working with Access 2003. The database is simply a reporting structure
to compile data from three other databases all on SQL (these are purchased
products that we are accessing with read-only logins), and the back-end of
this database is also on SQL. Databases A and B are on servers, database C
is on a user's PC in an off-site location. We were given user names and
passwords for each, not all the same of course, and told to code it so that
the password was pushed through in the background. I created a routine that
did this and it worked for months until about 2 weeks ago. The event fires
on opening of the main menu and again when the report request form is opened
(because of database C for times we have a bad connection to it).
About two weeks ago I was asked to change a query to add a new field, and to
do that I had to add a link to a new table in Database A. After that every
time we ran the report it asked for the password to Database A. We fixed
this by carefully running the Linked Table Manager, selecting Prompt for New
Location, and refreshing all the links to Database A.
Last week I was asked to make another change and this one affected Database
C, and I needed a new table linked into the database from C. The same issue
happened where it started prompting for the password to Database C, but then
it also asked for the password to its own back-end database which had not
changed. I managed to get it to stop prompting for the password to C, but
this time by copying the linked tables from another Access database with
those tables in it. But it still prompts for the password to its own back
end which has not changed. I can't seem to get rid of that most of the time,
no matter what I do. Occasionally it runs clean.
The only other change in the whole network is that a virus spread throughout
the network. I am sure the virus did not cause the problems however the
fixes and new security might have – however this routine sometimes works and
often fails. Why would it be intermittent?
Lastly, after running this routine we can open each table without the user
prompt for password. However when it runs behind the form for the report
request, we often get the prompt. Again, why would it be intermittent? One
user asked if it could be a timeout issue.

Here's the code. zSysDataSources is the only local table in the database,
everything else is an ODBC connection to one of the 4 SQL tables involved.
The directory test is used mainly to see if we can connect to that user's PC
that's off-site. I found that I had to actually USE the table somehow so
that's why the line i = Count(…) is there.

Thanks in advance for any suggestions.

Function Make_ODBC_Connections() As String
On Error GoTo Err_Make_ODBC_Connections

Dim rstDataSources As Recordset
Dim rs As Recordset
Dim db As DAO.Database
Dim strODBCCon As String
Dim strSQL As String
Dim strDir As String
Dim iResponse As Integer
Dim strOutput As String
Dim strFriendlyName As String
Dim strConnResult As String
Dim i As Integer

Set rstDataSources = CurrentDb.OpenRecordset("zSysDataSources",
dbOpenForwardOnly)

If rstDataSources.EOF Then
MsgBox "There are no data sources set up to connect to."
strOutput = "No Connections"
GoTo Exit_Make_ODBC_Connections
End If

strOutput = ""

With rstDataSources
Do Until .EOF
strFriendlyName = !DatabaseFriendlyName
If !UseThisDatasource Then
If !TestBeforeConnecting Then
strDir = ""
strDir = Dir(!DirectoryTest, vbDirectory)
If strDir = "" Then
iResponse = MsgBox("The connection to " &
!DatabaseFriendlyName _
& " is not available (the directory test failed). Do
you want to continue without " _
& !DatabaseFriendlyName & " data?", vbYesNo +
vbQuestion, "No connection to " & !DatabaseFriendlyName)
strConnResult = strConnResult & !DatabaseID & "=N; "
strOutput = strOutput & vbCrLf & !DatabaseFriendlyName & "
not available"
If iResponse = vbYes Then
GoTo MoveToNextRecord
Else
GoTo Exit_Make_ODBC_Connections
End If
End If
End If
strODBCCon = "ODBC" _
& ";DATABASE=" & !DatabaseName _
& ";UID=" & !LoginName _
& ";PWD=" & !PasswordText _
& ";DSN=" & !ODBCName

Set db = OpenDatabase(!ODBCName, dbDriverNoPrompt, True, strODBCCon)
i = Nz(DCount("*", !OneTableToTest, "1=0"), 0)
strConnResult = strConnResult & !DatabaseID & "=Y; "
strOutput = strOutput & vbCrLf & "Connected to " &
!DatabaseFriendlyName
End If
MoveToNextRecord:
..MoveNext
Loop
End With

Exit_Make_ODBC_Connections:
Make_ODBC_Connections = strConnResult & "*" & strOutput
rstDataSources.Close
Set rstDataSources = Nothing
Exit Function

Err_Make_ODBC_Connections:
Select Case Err.Number
Case Is = 3151 'connection failed error
strOutput = strOutput & vbCrLf & "Connection to " & strFriendlyName
& " failed"
Resume Next
Case Is = 52 'bad directory or file name
strOutput = strOutput & vbCrLf & "Directory Test to " &
rstDataSources!DirectoryTest & " failed"
Resume Next
Case Else
MsgBox "There has been an error in the Make ODBC Connections
function. For the database designer, " _
& "the error is number: " & Err.Number & ", description: " &
Err.Description, , "error"
Resume Exit_Make_ODBC_Connections
End Select
End Function

Not sure why it would work sometimes and not others.

I've seen similar behavior when a newly-linked table does NOT get the
checkbox (remember my password) checked during linking.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Trillium97" wrote in message
...
We're working with Access 2003. The database is simply a reporting
structure
to compile data from three other databases all on SQL (these are purchased
products that we are accessing with read-only logins), and the back-end of
this database is also on SQL. Databases A and B are on servers, database
C
is on a user's PC in an off-site location. We were given user names and
passwords for each, not all the same of course, and told to code it so
that
the password was pushed through in the background. I created a routine
that
did this and it worked for months until about 2 weeks ago. The event
fires
on opening of the main menu and again when the report request form is
opened
(because of database C for times we have a bad connection to it).
About two weeks ago I was asked to change a query to add a new field, and
to
do that I had to add a link to a new table in Database A. After that
every
time we ran the report it asked for the password to Database A. We fixed
this by carefully running the Linked Table Manager, selecting Prompt for
New
Location, and refreshing all the links to Database A.
Last week I was asked to make another change and this one affected
Database
C, and I needed a new table linked into the database from C. The same
issue
happened where it started prompting for the password to Database C, but
then
it also asked for the password to its own back-end database which had not
changed. I managed to get it to stop prompting for the password to C, but
this time by copying the linked tables from another Access database with
those tables in it. But it still prompts for the password to its own
back
end which has not changed. I can't seem to get rid of that most of the
time,
no matter what I do. Occasionally it runs clean.
The only other change in the whole network is that a virus spread
throughout
the network. I am sure the virus did not cause the problems however the
fixes and new security might have - however this routine sometimes works
and
often fails. Why would it be intermittent?
Lastly, after running this routine we can open each table without the user
prompt for password. However when it runs behind the form for the report
request, we often get the prompt. Again, why would it be intermittent?
One
user asked if it could be a timeout issue.

Here's the code. zSysDataSources is the only local table in the database,
everything else is an ODBC connection to one of the 4 SQL tables involved.
The directory test is used mainly to see if we can connect to that user's
PC
that's off-site. I found that I had to actually USE the table somehow so
that's why the line i = Count(.) is there.

Thanks in advance for any suggestions.

Function Make_ODBC_Connections() As String
On Error GoTo Err_Make_ODBC_Connections

Dim rstDataSources As Recordset
Dim rs As Recordset
Dim db As DAO.Database
Dim strODBCCon As String
Dim strSQL As String
Dim strDir As String
Dim iResponse As Integer
Dim strOutput As String
Dim strFriendlyName As String
Dim strConnResult As String
Dim i As Integer

Set rstDataSources = CurrentDb.OpenRecordset("zSysDataSources",
dbOpenForwardOnly)

If rstDataSources.EOF Then
MsgBox "There are no data sources set up to connect to."
strOutput = "No Connections"
GoTo Exit_Make_ODBC_Connections
End If

strOutput = ""

With rstDataSources
Do Until .EOF
strFriendlyName = !DatabaseFriendlyName
If !UseThisDatasource Then
If !TestBeforeConnecting Then
strDir = ""
strDir = Dir(!DirectoryTest, vbDirectory)
If strDir = "" Then
iResponse = MsgBox("The connection to " &
!DatabaseFriendlyName _
& " is not available (the directory test failed). Do
you want to continue without " _
& !DatabaseFriendlyName & " data?", vbYesNo +
vbQuestion, "No connection to " & !DatabaseFriendlyName)
strConnResult = strConnResult & !DatabaseID & "=N; "
strOutput = strOutput & vbCrLf & !DatabaseFriendlyName & "
not available"
If iResponse = vbYes Then
GoTo MoveToNextRecord
Else
GoTo Exit_Make_ODBC_Connections
End If
End If
End If
strODBCCon = "ODBC" _
& ";DATABASE=" & !DatabaseName _
& ";UID=" & !LoginName _
& ";PWD=" & !PasswordText _
& ";DSN=" & !ODBCName

Set db = OpenDatabase(!ODBCName, dbDriverNoPrompt, True,
strODBCCon)
i = Nz(DCount("*", !OneTableToTest, "1=0"), 0)
strConnResult = strConnResult & !DatabaseID & "=Y; "
strOutput = strOutput & vbCrLf & "Connected to " &
!DatabaseFriendlyName
End If
MoveToNextRecord:
.MoveNext
Loop
End With

Exit_Make_ODBC_Connections:
Make_ODBC_Connections = strConnResult & "*" & strOutput
rstDataSources.Close
Set rstDataSources = Nothing
Exit Function

Err_Make_ODBC_Connections:
Select Case Err.Number
Case Is = 3151 'connection failed error
strOutput = strOutput & vbCrLf & "Connection to " & strFriendlyName
& " failed"
Resume Next
Case Is = 52 'bad directory or file name
strOutput = strOutput & vbCrLf & "Directory Test to " &
rstDataSources!DirectoryTest & " failed"
Resume Next
Case Else
MsgBox "There has been an error in the Make ODBC Connections
function. For the database designer, " _
& "the error is number: " & Err.Number & ", description: " &
Err.Description, , "error"
Resume Exit_Make_ODBC_Connections
End Select
End Function