ODBC--CALL FAILED 1

[SteveGSI]

I have just had to move our Pervasive SQL database over to a new server due to hardware failure. But Now any Access files that are linked to it say "ODBC--CALL FAILED" how do i get round this?

 

[Blorf]

If the server name or file paths have changed, you will have to delete the ODBC link, and recreate them.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[SteveGSI]

I had re-configured (not deleted) the ODBC links but no joy, I take it this wasn't good enough

 

[Blorf]

I would delete them, or at least one to see if redoing the connection makes it happy. If not, sad now.

Chaz

Ascii dumb question, get a dumb Ansi

 

[SteveGSI]

sad now?

 

[Blorf]

Sad because could not fix the problem.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[SteveGSI]

Okay...

I have deleted the ODBC DSN's and also uninstalled / re-installed the Pervasive Client but still no joy.

 

[Tranman]

Steve,
Is it safe to assume you went into Tools|Database Utilities|Linked Table Manager, and refreshed the links???

Tranman

 

[SteveGSI]

No i didn't (i'm not that good with acces) but i'll check it out

 

[ByteMyzer]

I've dealt with the problem before, and here's the solution:

First, go to the ODBC Data Source Administrator. For each DSN which uses the Pervasive Engine, select Configure, and change the Server field to the name of the new Server.

Once each of the DSN's has been changed, open up the database with the ODBC linked tables. Create a new module and paste the following code after the Option Explicit statement:

''' Begin Code '''
Public Function odbcFix(serverOld As String, serverNew As String)

Dim tbDefs As TableDefs, tbCtr As Integer

Set tbDefs = CurrentDb.TableDefs

For tbCtr = 0 To tbDefs.Count - 1
If tbDefs(tbCtr).Attributes = dbAttachedODBC Then
tbDefs(tbCtr).Connect = Replace(tbDefs(tbCtr).Connect, serverOld, serverNew)
tbDefs(tbCtr).RefreshLink
End If
Next tbCtr

Set tbDefs = Nothing

End Function

Public Function Replace(sIn As String, sFind As String, _
sReplace As String, Optional nStart As Long = 1, _
Optional nCount As Long = -1, Optional bCompare As _
Variant = vbBinaryCompare) As String

Dim nC As Long, nPos As Integer, sOut As String
sOut = sIn
nPos = InStr(nStart, sOut, sFind, bCompare)
If nPos = 0 Then GoTo EndFn:
Do
nC = nC + 1
sOut = Left(sOut, nPos - 1) & sReplace & _
Mid(sOut, nPos + Len(sFind))
If nCount <> -1 And nC >= nCount Then Exit Do
nPos = InStr(nStart, sOut, sFind, bCompare)
Loop While nPos > 0
EndFn:
Replace = sOut
End Function
''' End Code '''


Save the module as 'odbcFix Module", go to the Debug window, type:
Call odbcFix("OldServerName","NewServerName")

and press Enter. See if this fixes your problem.