asked on

MS Access no TNSNAMES DSNLess change linked table connections to Oracle

I need to change the linked table connections on 30+ MS Access databases. The tables are all connected to an Oracle server that is being replaced with a new one. My problem is that the company is not updating anyone's TNSNAMES.ora file. Everyone currently has an old TNSNAMES.ora file from 2002 and everyone has different versions of Oracle Client on their PC's. So DO NOT suggest that I append information to the TNSNAMES.ora file as that is not an option. I ran across this snippet of code (which I modified) from:

http://www.codeproject.com/vbscript/connection_string.asp?df=100&forumid=230908&exp=0&select=1986329

Dim strCon
strCon = "Driver={Microsoft ODBC for Oracle};" & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=mysrv.usa.domain.com)(PORT=7001))" & _
"(CONNECT_DATA=(SID=mysrv)));uid=read;pwd=read;"

Dim oCon As ADODB.Connection
Set oCon = CreateObject("ADODB.Connection")
Dim oRs As ADODB.Recordset
Set oRs = CreateObject("ADODB.Recordset")
oCon.Open strCon
Set oRs = oCon.Execute("SELECT KUNNR FROM DAT.KNA1")
oRs.MoveFirst
Debug.Print oRs(0).Value
oCon.Close
Set oRs = Nothing
Set oCon = Nothing

This works fine for gettng data but when I try to refresh my links using the same connection string I get an error. Here is my code:

Public Function RefreshTableLinks()

Dim wsp As DAO.Workspace
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim strConnect As String

For Each tbl In CurrentDb.TableDefs
If InStr(1, tbl.Connect, "odr.world") > 0 Then
Debug.Print tbl.Name
Debug.Print "Before " & tbl.Connect

strConnect = "ODBC;Driver={Microsoft ODBC for Oracle};" & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=mysrv.usa.domain.com)(PORT=7001))" & _
"(CONNECT_DATA=(SID=mysrv)));uid=read;pwd=read;"

tbl.Connect = strConnect

Debug.Print "After " & tbl.Connect
tbl.RefreshLink
End If
Next

End Function

The code runs fine but when I try to open the linked table I get this error:
Reserved error (-7778); there is no message for this error

Can anyone help me with this one?

mpmccarthy

You need to include the table name in the link.

ascnd

ASKER

That does not make sense. The table name is already there. Remember I am just changing the connection string. The table already exists.

mpmccarthy

You are reconnecting each table. The link to a table includes a reference to the tablename.

ascnd

ASKER

Look at the first function I posted. It connects to the database without any refence to the table. Only when I declare a SELECT statement do I specify the table name. Therefore, the connection string works without a reference to a table. The second function which is the one I cannot get to work doesn't like the connection string. Have you done this before? I am mean have you reconnected Access linked tables through code before?

atherh

'Copy and paste this function into module

'==========================================================

Public Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String

For Each td In CurrentDb.TableDefs
If td.name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next

If Len(stUsername) = 0 Then
'//Use trusted authentication if stUsername is not supplied.
stConnect = "ODBC;DRIVER=Sybase SQL Anywhere 5.0
;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
'//WARNING: This will save the username and the password with the linked table information.
stConnect = "ODBC;DRIVER=Sybase SQL Anywhere 5.0
;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
Exit Function

AttachDSNLessTable_Err:

AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function

Public sub setupDSN()
AttachDSNLessTable "local table name", "Remote table Name", "SybaseServer Name ", "DatabaseName", "SybaseUserName", "Sybase Password"

end sub

atherh

change the Driver to Oracle and it will work.
DRIVER=Sybase SQL Anywhere 5.0

ascnd

ASKER

atherh,

Thanks but that did not work.

ascnd

ASKER

Please refund my points.

ASKER CERTIFIED SOLUTION