T
Trillium97
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
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