Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3351
  • Last Modified:

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?
0
ascnd
Asked:
ascnd
  • 5
  • 2
  • 2
  • +1
1 Solution
 
mpmccarthyCommented:
You need to include the table name in the link.
0
 
ascndAuthor Commented:
That does not make sense.  The table name is already there.  Remember I am just changing the connection string.  The table already exists.
0
 
mpmccarthyCommented:
You are reconnecting each table.  The link to a table includes a reference to the tablename.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ascndAuthor Commented:
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?
0
 
atherhCommented:
'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
0
 
atherhCommented:
change the Driver  to Oracle and it will work.
DRIVER=Sybase SQL Anywhere 5.0
0
 
ascndAuthor Commented:
atherh,

Thanks but that did not work.
0
 
ascndAuthor Commented:
Please refund my points.
0
 
dentabCommented:
Did you ever get the answer?

If not:

Does this code work on any of the machines?
If so then:
   What is the minimum version of Oracle Client installed and what is the minimum version that works with this script to your knowlege.

Finally, if I cannot come up with a solution to work as linked tables:
  Are the tables used to update the Oracle DB (never done it but guess its possible) I am guessing not esp given the user/pw combo you have shown, in which case would it matter if the re was simply a macro to empty and re-populate the access table?

Also have you tried the Oracle version of the conn string instead?
I think its as simple as

SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mysrv.usa.domain.com)(PORT=7001))(CONNECT_DATA=(SERVICE_NAME=mysrv)));uid=read;pwd=read;

I'll dig it up from my notes if that connection string doesn't work.

Finally if we cant fix it any other way, or if you are fedup of playing about, an ugly work around could be to use a SQL Server to link to the Oracle DB and access link to the SQL Server... probably not the best idea, but I thought I'd mention it anyway.
0
 
ascndAuthor Commented:
dentab,

Thanks for the help but I am not working on that project any longer.  So I am going to give you the points to close this question.
0
 
dentabCommented:
lol again.

Thanks though.  Well I owe you an answer or two, and you have my email.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now