ODBC error invalid connection string attribute

[TommieB]

Hi, need a little help.

I have an application that was written for Access 2000 and has tables linked to a SQL server 2000 DB. It uses both DSN's and DSN-less connections with no ill effects until recently.

I have installed the application on a server and provided all the users with icons on their desktops to run it. Each machine has the Access 2000 runtime installed, MDAC 2.7, and a system DSN. There are 8 machines and the app runs and connects on 6 of these with no problems. On the other 2, I am receiving intermittent errors (invalid connection string attribute)on the application start which would be the form load event of the first form. This section does make calls to some DSN-less connections to verify that the DSN is valid and to relink the tables if not. But these have always worked before and are still working on most machines.

If I close the application, reboot the machines and reopen the app, it opens and runs fine.

The only difference that I can find with these machines is that they are running Office XP and that for some strange reason, when I create the DSN, the same server name is listed twice in the drop down list. The network people keep telling me that there is no problem, but unless the problem is with the application being Access 2000 and the users running Office XP, I just don't see how it can be the connections. They have always worked before.

Does anyone have any ideas on what I might look for? I have spent the better part of the day looking all over the internet for information and just have not found anything that would apply to my situation.

The code for the connections is:

Form_load calls RelinkTheFiles Which in turns calls the next three subs in the order they are here.

Sub RunRelinkBeforeUpdate()
Dim CurDB As Database, tdfLinked As TableDef
Dim TBDef As TableDef, InstallPath As String
Dim DBPath As String
Dim strDSN As String

Dim varDbName As Variant
Dim strDbName As String
Dim varDbConnect As Variant
Dim varDbSplit As Variant

Set CurDB = CurrentDb
varDbName = Split(CurDB.Name, "\&quot
strDbName = varDbName(UBound(varDbName))

DBPath = SafePath & strDbName

For Each TBDef In CurDB.TableDefs
If (TBDef.Attributes And dbAttachedODBC) Then
Set tdfLinked = CurDB.TableDefs(TBDef.Name)
strDSN = "ODBC;DSN=" & gstrDSNName & ";"
strDSN = strDSN & "UID=" & gstrDSNUserID & ";"
strDSN = strDSN & "pwd=" & gstrDSNPassword & ";"
strDSN = strDSN & "DATABASE=" & gstrDSNDatabase & ";"
strDSN = strDSN & "SERVICE=sql server"

tdfLinked.Connect = strDSN

tdfLinked.RefreshLink
End If
Next TBDef

End Sub

Sub UpdateDataSourceTable()
On Error GoTo subError
Dim strDocName As String
DoCmd.DeleteObject acTable, "tblODBCDataSources"
strDocName = "QryTables"
DoCmd.OpenQuery strDocName, acViewNormal, acEdit
Exit Sub
subError:
Resume Next

End Sub

Sub RelinkTablesAfterUpdate()

Call CreateODBCLinkedTables
End Sub

Function CreateODBCLinkedTables() As Boolean
On Error GoTo CreateODBCLinkedTables_Err
Dim strTblName As String, strConn As String
Dim db As DAO.Database, rs As DAO.Recordset, tbl As DAO.TableDef
Dim strDSN As String
Dim intCount As Integer
' ---------------------------------------------
' Register ODBC database(s).
' ---------------------------------------------
strDSN = gstrDSNName
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * From tblODBCDataSources Order By DSN&quot
With rs
While Not .EOF
If strDSN <> CStr(rs(&quot;DSN&quot) Then
DBEngine.RegisterDatabase rs(&quot;DSN&quot, _
&quot;SQL Server&quot;, _
True, _
&quot;Description=VSS - &quot; & rs(&quot;DataBase&quot & _
Chr(13) & &quot;Server=&quot; & rs(&quot;Server&quot & _
Chr(13) & &quot;Database=&quot; & rs(&quot;DataBase&quot
End If
strDSN = rs(&quot;DSN&quot
' ---------------------------------------------
' Link table.
' ---------------------------------------------
strTblName = rs(&quot;LocalTableName&quot
strConn = &quot;ODBC;&quot;
strConn = strConn & &quot;DSN=&quot; & rs(&quot;DSN&quot & &quot;;&quot;
strConn = strConn & &quot;APP=Microsoft Access;&quot;
strConn = strConn & &quot;DATABASE=&quot; & rs(&quot;DataBase&quot & &quot;;&quot;
strConn = strConn & &quot;UID=&quot; & rs(&quot;UID&quot & &quot;;&quot;
strConn = strConn & &quot;PWD=&quot; & rs(&quot;PWD&quot & &quot;;&quot;
strConn = strConn & &quot;TABLE=&quot; & rs(&quot;ODBCTableName&quot
If (DoesTblExist(strTblName) = False) Then
Set tbl = db.CreateTableDef(strTblName, _
dbAttachSavePWD, rs(&quot;ODBCTableName&quot, _
strConn)
db.TableDefs.Append tbl
Else
Set tbl = db.TableDefs(strTblName)
tbl.Connect = strConn
tbl.RefreshLink
End If

rs.MoveNext
Wend
End With

CreateODBCLinkedTables = True
rs.Close
Set rs = Nothing
'MsgBox &quot;Refreshed ODBC Data Sources&quot;, vbInformation
CreateODBCLinkedTables_End:
Exit Function
CreateODBCLinkedTables_Err:
MsgBox Err.Description, vbCritical, &quot;MyApp&quot;
Resume CreateODBCLinkedTables_End
End Function