Problem with Refresh link (tdfTable.RefreshLink) in VB Script

[lgullapudi]

could you please help me...

Getting error as "RXP was unable to connect to the database" when i was trying to open the application and this exception is coming at the line tdfTable.RefreshLink.

It was working fine previously ,but i think its not working after Database upgrade from Oracle 10g to Oracle 11g.


Code :


could you please help me...

Getting error as " when i was trying to open the application and this exception is coming at the line tdfTable.RefreshLink.

It was working fine previously but i think its not working after Database upgrade from Oracle 10g to Oracle 11g.



Code :

Option Compare Database
Option Explicit
Dim mstrUserId As String
Dim mstrPassword As String
Const theConnectString = "usa1111ab2269.apps.mc.xerox.com:1551/RXP102;"


Function ValidateInput() As Boolean
Dim strConnect As String
Dim strValid As String
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

' A username must be entered
txtUsername.SetFocus
If Len(txtUsername.Text) = 0 Then
MsgBox "Username is missing.", vbExclamation, "SOLOS - D1J"
ValidateInput = False
Exit Function
End If

' A password must be entered
txtPassword.SetFocus
If IsNull(txtPassword) Then
txtPassword.Value = ""
End If
If Len(txtPassword.Text) = 0 Then
MsgBox "Password is missing.", vbExclamation, "SOLOS - D1J"
ValidateInput = False
Exit Function
End If

strConnect = "DRIVER={Microsoft ODBC for Oracle};" & _
"SERVER=" & theConnectString & _
"UID=" + "RXPLOGIN" + ";" & _
"PWD=" + "NIGOLJ1D" + ";"

Set con = New ADODB.Connection
con.ConnectionString = strConnect
con.Open

Set cmd = New ADODB.Command
cmd.ActiveConnection = con
cmd.CommandText = "RXP.RXP_LOGIN"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters("insUserId") = txtUsername.Value
cmd.Parameters("insPassword") = txtPassword.Value
cmd.Parameters("innMdbVersion") = 1

Set rs = cmd.Execute

strValid = cmd.Parameters("outsValid")

If strValid <> "Y" Then
If strValid = "I" Then
MsgBox "Username / Password is not valid", vbExclamation, "SOLOS - D1J"
ElseIf strValid = "V" Then
MsgBox "Your SOLOS D1J access database is obsolete and needs to be replaced with the latest version.", vbExclamation, "SOLOS - D1J"
Else
MsgBox "An unexpected user id validation error as occurred.", vbExclamation, "SOLOS - D1J"
End If
ValidateInput = False
Exit Function
End If

'these are used later to refresh the conneciton to the master file view
mstrUserId = cmd.Parameters("outsUserId")
mstrPassword = cmd.Parameters("outsPassword")

ValidateInput = True
End Function

Function InitializeConnection() As Boolean
Dim strConnect As String
Dim dbs As DAO.Database
Dim tdfTable As DAO.TableDef

On Error GoTo ErrorHandler

Set dbs = CurrentDb

'note that mstrUserid and mstrPassword are retrieved from a previious
'call to the D1J_LOGIN stored procedure
strConnect = "DRIVER={Microsoft ODBC for Oracle};" & _
"SERVER=" & theConnectString & _
"UID=" + mstrUserId + ";" & _
"PWD=" + mstrPassword + ";"

' Refresh Access Linked Tables
For Each tdfTable In dbs.TableDefs
' Only attempt to refresh link on tables that already
' have a connect string (linked tables only)
If Len(tdfTable.Connect) > 0 Then
' Set the tables connection string
tdfTable.Connect = strConnect

' Refresh the link to the table
[highlight #F57900]tdfTable.RefreshLink[/highlight]

End If


Next

InitializeConnection = True
Exit Function

ErrorHandler:
MsgBox "RXP was unable to connect to the database", vbExclamation, "RXP - LOGIN"
InitializeConnection = False
End Function

Private Sub cmdCancel_Click()
CurrentDb.Close
Quit
End Sub

Private Sub cmdOk_Click()
Screen.MousePointer = 11
If ValidateInput() Then
If InitializeConnection() Then
DoCmd.Close acForm, "frmLogin"
End If
End If
Screen.MousePointer = 1
End Sub


Private Sub Form_Activate()
txtUsername.SetFocus
End Sub

 

[PHV]

What are the values of Err.Number and Err.Description whent the error raises ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lgullapudi]

getting this exception "RXP was unable to connect to the database", vbExclamation, "RXP - LOGIN" at the line tdfTable.RefreshLink



For Each tdfTable In dbs.TableDefs
' Only attempt to refresh link on tables that already
' have a connect string (linked tables only)
If Len(tdfTable.Connect) > 0 Then
' Set the tables connection string
tdfTable.Connect = strConnect

' Refresh the link to the table
tdfTable.RefreshLink
End If


Next

InitializeConnection = True
Exit Function

ErrorHandler:
MsgBox "RXP was unable to connect to the database", vbExclamation, "RXP - LOGIN"
InitializeConnection = False
End Function

 

[PHV]

You didn't answer my question ...
Add Err.Number and Err.Description in your error handler message box.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Golom]

You don't really know where the error happened or what it was from that. Your MsgBox just shows the same message no matter what the actual error was and all you really know is that an error of some sort occurred. Try it with

MsgBox Err.Number & " - " & Err.Description

 

[lgullapudi]

As suggested ,i added this line "MsgBox Err.Number & " - " & Err.Description " and tried to launch the application

error is 3000-reserved error(-7778);there is no messgae for this error.

Thanks for your support

 

[Golom]

Here's a possibility involving an ODBC connection.

 

[lgullapudi]

Can you please help me ..

how to create file DSN and User DSN and what are the code changes i have to do in my code.

 

[Golom]

The link I posted pretty much explains how to set up the ODBC DSNs. I'm not an Oracle user but I have a suspicion that the change to 11g may require that you have different DLLs installed on your machine. You may want to check with your Oracle gurus to see if your local machine needs to be upgraded.