Difficulty Connecting ODBC Drivers

In the Access application that I have developed for a user, the application is supposed to establish the ODBC connection to an external database when the application is initiated. On my PC, the application works. On an older PC, the application works. However, on the User's PC and on the new one that we are building for her the linking to the external tables does not work. I have displays for the tables that she is to link to as the program is doing the linking. Sometimes, on the PC for her use, the first table will not link and the rest will. Sometimes, none of the tables will link. Sometimes, the first half will link and the second half won't. I am stymied. Below is the logic that I use to make the connections at the beginning of the process. Any suggestions?


Dim strTable As String

On Error GoTo Assign_Error

'MsgBox ("Click on OK and Please be Patient as the program links to PeopleSoft tables.")

' NOTE : Do not unlink the tables as you will have to manually relink the tables

strTable = "ps_comp_ratecd_tbl"
Call unlink_table(strTable)
Call link_table(strTable)
strTable = "ps_current_job"
Call unlink_table(strTable)
Call link_table(strTable)

strTable = "ps_names"
Call unlink_table(strTable)
Call link_table(strTable)
strTable = "ps_tl_compleav_tbl"
Call unlink_table(strTable)
Call link_table(strTable)

strTable = "ps_tl_empl_data"
Call unlink_table(strTable)
Call link_table(strTable)
strTable = "ps_tl_trc_tbl"
Call unlink_table(strTable)
Call link_table(strTable)

strTable = "ps_tl_tskprf_detl"
Call unlink_table(strTable)
Call link_table(strTable)

Assign_Exit:
Exit Function

Assign_Error:
MsgBox Error$
MsgBox Err
Resume Assign_Exit

End Function
Function link_table(strTable As String)
Dim dbs As Database
Dim tdfLinked As TableDef
Dim strDatasource As String
Dim strDatabase As String


On Error GoTo Link_Error

DoCmd.OpenForm "Message"
[Forms]![Message].SetFocus
[Forms]![Message]![Message] = "Linking Table " & strTable
[Forms]![Message].Repaint

strDatabase = "HR8PROD"
strDatasource = "PS_Payroll"
strTable = UCase(strTable)

Set dbs = CurrentDb
Set tdfLinked = dbs.CreateTableDef(strTable)

' tdfLinked.Attributes = dbAttachSavePWD
' Check to see if table already exists and if it does then do nothing
' For Each tdfLinked In dbs.TableDefs
' If tdfLinked. = strTable Then
tdfLinked.Connect = "ODBC;UID=sysadm;PWD=sysadm;DSN=" & strDatasource & ";"
tdfLinked.SourceTableName = strTable
tdfLinked.Attributes = dbAttachSavePWD

dbs.TableDefs.Append tdfLinked
DoCmd.Close acForm, "Message"
' Exit Function
' End If
' Next
' [Forms]![Message].SetFocus
' [Forms]![Message]![Message] = strTable & " Not Found for Linking"
' [Forms]![Message].Repaint

' DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=PS_Payroll;UID=sysadm;PWD=sysadm;DATABASE =HR8PROD", acTable, "SYSADM." & UCase(strTable), strTable

Link_Exit:
Exit Function

Link_Error:
' if the error is simply that the item to delete isn't actually
' there to delete, we want to skip it.
If Err = 3265 Then
Resume Next
End If
' otherwise we want to show what the error is, and then exit.
MsgBox Error$
Resume Link_Exit

End Function

Function unlink_table(strTable As String)
Dim dbs As Database

On Error GoTo Unlink_table_Error

Set dbs = CurrentDb
dbs.TableDefs.Delete strTable

Unlink_table_Exit:
Exit Function

Unlink_table_Error:
' if the error is simply that the item to delete isn't actually
' there to delete, we want to skip it.
If Err = 3265 Then
Resume Next
End If
' otherwise we want to show what the error is, and then exit.
MsgBox Error$
Resume Unlink_table_Exit

End Function