Linking to oracle Results

Hi,

This is the first time I am posting through this site.
When I tried to link to Oracle 8/Oracle 8i database through MSACCESS 2000 I get the following error.

ODBC-call failed
[Microsoft][ODBC driver for Oracle][Oracle]ORA-03121: no interface driver connected - function not performed(#3121)[Microsoft][ODBC Driver Manager]Driver's SQLSetConnectAttr failed(#0) [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed(#0)


PLEASE HELP!!!

Sonali

Hi All,
Using Access 2003 to link to Oracle I get the following message on some tables;

'Invalid field definition 'DATE_CONFIRMED' in definition of index or relationship'

Any ideas on what may hbe causing this?

see attached diagram:
Oracle provides a View. Can SQL Server link to this view (read only) and basically let me create a 2nd Read Only view in SQL Server?

This way the Access Front End (client) can avoid all that messy Oracle ODBC driver mess.

This is my first time using an access database to link to oracle tables. What's the verbage needed to connect to the tables?

Thanks

I found Banana's ODBC post but nothing there for this situation.

I wanted to package an app with dev tools and I wanted to distribute it as an ACCDE. When I convert the app to ACCDE I can't get a connection to an ODBC Oracle table working. I read at MS this affects VBA but it doesn't mention anything about externally linked tables.

Anything I can do about getting external links to work in an ACCDE?

I should include that I haven' tested it this way with the Full Access, only on a runtime box where it wouldn't work.

I'm trying to establish links to tables within an Oracle database. When I use the standard import objects pointing to the ODBC database, it produces a list of tables that only shows the first approx 100 tables in the Oracle instance. There is no way for me to scroll further down the list to pick a table name starting with "M" for instance. Is there anyway for me to explicitly define the table name I want to import so I don't run into the UI limitations of the import wizard?

I have an Access DB with links to tables several tables in an Oracle DB. I want to create a button on one of my forms which will prompt the user to enter their User ID and Password for the Oracle DB. The Oracle DB name is CSEMEPRD. Can someone help me please? Thanks

Hi ,

I have a MS access db with linked ORACLE tables.
I used the ORACLE passwd when i linked the tables.

Instead of relinking all tables , can i link automatically with ORACLE via code/ autoexec macro , when i open the database ?

Would appreciate any help.
Thanks,
Gina.

Hi,


Currently, I'm learning how to open databases by using DAO objects and retrieve Recordsets.

I would like to know whether it is possible to connect a DAO object to Oracle. I have been googling for finding this, but so far, what I have found is all about ADO and not DAO. In the case, where it is possible to connect Access to Oracle by using DAO, could anyone kindly give me an indication (code, link to a tutorial, etc.)


Thanks in advance,

Kind Regards
Dariyoosh

Hi Everyone - I have an access database that has linked tables in an Oracle Database. When the user logs into the access app it does a connection to oracle using their username to determine fine grain access privileges. When I had this database using an old Oracle 9i ODBC client it worked fine, now with Oracle Instant Client 11.2.0.3 ODBC i get the following message:

"The connection string is too long. The connection string cannot exceed 255 characters. (3210) encountered."

The connection string is like this:
PHP Code:
ODBC;DRIVER={Oracle in OraHome92};DBQ=XXXXXXXX;UID=XXXXXXXX;PWD=XXXXXXXXX 

This is WAY less then 255 characters so i'm confused.

This is the code it's using to connect (The varConnect is the connection string from above):
PHP Code:
Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, varConnect) 

The Internet has been less the helpful. Any assistance is appreciated.

Our warehouse management system is being upgraded to oracle 9i.

Up till now our ODBC links to the oracle 7.4 db have been working just fine,
but now we have a problem with the new datatypes "Timestamp with local time zone" that oracle uses.

We need to get msaccess2000 to work with the upgraded db, but our odbc-drivers seem to be unable to handle it, which means that a lot of applications that we've built will not be working anymore. This is a huge problem for our company.

Does anybody have suggestions as to how we can solve this problem.
Has anybody else encountered this?

If I haven't been clear enough, don't hesitate to ask, we just need to solve this thing.

Best regards,
Jeroen Radersma.

Hi

I found the DSNStripper add-in on mvps, nice little tool.
But I would like to try and get it to work with linked tables from Oracle and make them DSN-less.

Can anyone give me any suggestions on how to do this?
I don't really understand how the code works, but I have changed what I think are the sections that will work for Oracle, but I keep getting the following message when I add it into the db I'm working on and running it.

View Message

However, if I run the original code to change the tables from SQL Server to DSN-less it works fine.

Is there anything wrong with my code, which could cause this message?

Code:


	Code:
	Option Compare Database
Option Explicit

Const strSupportTable As String = "USysDSNStripper"

Private Sub cmdHelp_Click()
   MsgBox "This utility will iterate through your ODBC linked tables and convert the links to DSN-less links. It assumes that
all ODBC links are to the above-named SQL Server. Do NOT use if you have ODBC links to databases other than SQL Server.", _
    vbInformation + vbOKOnly, "DSN Stripper 4 Oracle"
End Sub

Private Sub cmdStrip_Click()

   On Error GoTo HandleErrors

   If Len(txtServer & "") > 0 Then
      ServerSave txtServer.Value
            
      Dim db As DAO.Database
      Dim tdf As DAO.TableDef
      Dim strCnx As String
      Dim strName As String
      Dim intPosDsn As Integer
      Dim intPosNextSemi As Integer
      Dim astrCnx(3) As String
      Dim strMsg As String
      Dim strAOName As String
      
      Set db = CurrentDb()
      
      For Each tdf In db.TableDefs
          If Not tdf.Name Like "MSys*" And Not tdf.Name Like "USys*" Then
              strName = tdf.Name
              lblProgress.Caption = "Inspecting " & strName & "..."
              Me.Repaint
            
              strCnx = tdf.Connect
              
              If Len(strCnx) > 0 Then
                If Left(strCnx, 5) = "ODBC;" Then
                  lblProgress.Caption = "Converting link for " & strName & "..."
                  Me.Repaint
                  
                  intPosDsn = InStr(strCnx, ";DSN=")
                  If intPosDsn > 0 Then
                     intPosNextSemi = InStr(intPosDsn + 1, strCnx, ";")
                     
                     
                     astrCnx(1) = Left(strCnx, intPosDsn - 1)
                     astrCnx(2) = ";DRIVER=Microsoft ODBC for Oracle;SERVER=" & txtServer.Value
                     astrCnx(3) = Mid(strCnx, intPosNextSemi)
                     strCnx = astrCnx(1) & astrCnx(2) & astrCnx(3)
                     
                     ' Now refresh the link
                     tdf.Connect = strCnx
                     tdf.RefreshLink
                  End If
              
                End If
              End If
              
          End If

      Next
      
      lblProgress.Caption = "Done."
      Me.Repaint
   Else
      MsgBox "In order to perform the conversion you must supply the Oracle Server name.", vbCritical + vbOKOnly, _
       "DSN Stripper 4 Oracle"
   End If
   
ExitHere:
     On Error Resume Next
     Exit Sub
   
HandleErrors:
     Select Case Err.Number
       Case Else
         strMsg = "Unexpected error " & Err.Number & ": " & vbCrLf & Err.Description
     End Select
     MsgBox strMsg, vbCritical + vbOKOnly, "Itemize Database Error"
     Resume ExitHere
End Sub

Private Sub Form_Load()
   txtServer.Value = ServerGet()
   
End Sub

Function ServerGet()
   Dim db As DAO.Database
   Dim rst As DAO.Recordset
   Dim strReturn As String
   
   On Error Resume Next
   
   Set db = CodeDb
   Set rst = db.OpenRecordset(strSupportTable, dbOpenTable)
   If Not rst.EOF Then
      strReturn = rst.Fields("ServerName")
   End If
   
   rst.Close
   
   ServerGet = strReturn
End Function

Sub ServerSave(strServer As String)
   Dim db As DAO.Database
   Dim rst As DAO.Recordset
   
   On Error Resume Next
   
   Set db = CodeDb
   Set rst = db.OpenRecordset(strSupportTable, dbOpenTable)
   If Not rst.EOF Then
      rst.Edit
         rst.Fields("ServerName") = strServer
      rst.Update
   Else
      rst.AddNew
         rst.Fields("ServerName") = strServer
      rst.Update
   End If
   
   rst.Close
End Sub

Thanks

Dears,

I am trying to create a VBA application which has a button that imports data from oracle database.
I searched the net on how to connect to oracle via VBA, and execute the import but couldnt find anything

Can anyone help ?

And is there a link that describes the import commands from oracle to access and how to use them ?

I've used Access as a front end for Oracle for years. Recently upgraded to Windows7 64 bit and 64 bit Office.

I am able to connect to an Oracle database and run queries that were developed with Access 2007, but am unable to link to Oracle tables that were not previously linked to create new queries.

When I attempt to link, I receive the following error: Reserved error (-7732); there is no message for this error.

Anyone know how to get around this?

Hello All.

Anyone know how to transfer an Access DB to oracle ? I mean i have tables and links etc..etc..etc..

I want to move all that to Oracle.

Can Anyone Help ?

I'm in the process of migrating an Access 2000 mdb to Oracle, intending to link to Oracle backend and use the Access frontend. Using the Migration Workbench we've managed to do the migration and create the link to the Oracle backend, no problems there. However the performance is terrible. Forms, which are all based on saved queries, are taking ages to open and moving through records is slow too. Any suggestions. I'm assuming the way to go is to bypass the Jet Engine but not sure how to go about that. Am I going to have to re-write all my queries in SQL recognised by Oracle?

I have a Acces 2000 front end that links to an Oracle 9i back end. Everytime i open a form/table the ODBC dialog box appears asking for username/password/server. Is there a way to bypass this using vba code so that when the Access app starts the connection is established behind the scenes and therefore preventing the dialog box appearing.

thx,

Si

We have a couple of end-users that are running into an error when closing the data view of links to Oracle tables, indicating the following (or something similar):

"This action will reset the current code in break mode... Do you want to continue?"

If you hit Yes, the window just pops up again. If you hit No, you return to the data view, but you still cannot close it, nor can you simply quit Access. The end-users have to use the Task Manager to kill Access.

If anyone knows how to get around this, that would be great. I hope to hear from you.

Thanks in advance, and happy programming.

Richard

Is it possible in access to link to both oracle AND access tables in the same
access application?

Hi everyone,

I'm linking to ORACLE tables in my Ver. 97 MSaccess db. I've used the Oracle ODBC driver to link them, and they have all worked apart from one. The one failure shows "#Deleted" in all the fields, although there are the correct number of records.

Does anyone know why this may have happened?

Thanks in advance


Not finding an answer? Try a Google search.