link oracle tables to access 97

From: <lefante_at_my-deja.com>
Date: 2000/04/11
Message-ID: <8cvipq$tqm$1@nnrp1.deja.com>#1/1

I have a utility writeen which allows users to refresh links to SQL Server tables, and I am trying to modify it to run with Oracle tables. Most of the information is stored in a table. My main question is what do I need to change to get this to work with Oracle? I need the settings for both Orcale 8 and Oracle 8i.

Thanks. Here's the code I have so far.

Set DB = CurrentDb
Set RS = DB.OpenRecordset("Select * from tblODBCDataSources where Link_Now = True;")
With RS
  While Not .EOF
    DBEngine.RegisterDatabase RS("DSN"), "SQL Server", True, "Description=VSS - " & RS("DataBase") & Chr(13) & "Server=" & RS("Server") & Chr(13) & "Database=" & RS("DataBase")
' ---------------------------------------------
' Link table
' ---------------------------------------------

    strTblName = RS("LocalTableName")

    strConn = "ODBC;"
    strConn = strConn & "DSN=" & RS("DSN") & ";"
    strConn = strConn & "APP=Microsoft Access;"
    strConn = strConn & "DATABASE=" & RS("DataBase") & ";"
    strConn = strConn & "UID=" & UID_var & ";"
    strConn = strConn & "PWD=" & PWD_var & ";"
    strConn = strConn & "TABLE=" & RS("ODBCTableName")
    If (DoesTblExist(strTblName) = False) Then
        Set tbl = DB.CreateTableDef(strTblName, dbAttachSavePWD,
RS("ODBCTableName"), strConn)
        DB.TableDefs.Append tbl
    Else:
        Set tbl = DB.TableDefs(strTblName)
        tbl.Connect = strConn
        tbl.RefreshLink

    End If
    RS.MoveNext
  Wend
End With

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Apr 11 2000 - 00:00:00 CDT