Help getting records from Oracle Database

[bernie10]

Hi,
I have a form establishes an ADO DSN-less connection to an oracle database (I hope I said that right). I can successfully make the connection, but I need a little help getting the records to a table within my Access database. The table on the database changes hourly so I want the capability to use the form button to update my Access table whenever desired. I assume we'll either use a record set or some sort of SQL statement? If anybody has some code they could direct me to I would appreciate it.

I'll post what I have so far:

Private Sub cmdGetData_Click()
   Dim conn As String
   Dim cn As ADODB.Connection
   Dim strSQL As String
   
    conn = "UID= myID;PWD=myPW;DRIVER={Microsoft ODBC for Oracle};SERVER=myServer;"
    
    Set cn = New ADODB.Connection
    
    With cn
       .ConnectionString = conn
       .CursorLocation = adUseClient
       .Open
       
    End With

 

[PHV]

Why not simply use a linked table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bernie10]

I'm trying to get around other users having to have their own password to get the reports they need. This way I can just programatically make the connection get the data and only show the generated reports to the end user. Using linked tables, each time the reports are accessed the user would have to enter a password for the database.

I'm really new at this so correct me if I'm wrong about anything...

Thanks,
CM

 

[lewds]

Hi...

I believe PHV is correct in using a linked table...

We also interface with an Oracle database for reports and data collection.

Ask the DBA to give you a user and password with read-only access and use this to make the links to the tables you want.

When making the links to tables by using the manager , there is a checkbox to remember the password, this should appear when selecting tables.


In this way, there is only one connection to Oracle that can be used by anyone without further ID and password entries.

This way you can scrape for the data that you want with simple queries treating the link as just another table.

Hope this helps?

 

[bernie10]

Yea that might be what we have to do... I have a password to the table, but the users will not. We clicked remember remember password but still everytime we open up the table or a query based on the linked table we are asked again for a password. Not sure what the problem is...

CM

 

[lewds]

Hi..

Did you save and close your database after linking the tables?

Did you attempt to use the same connection code before you saved and closed?

Once you link a table, Access maintains the connection for you, you need not code it again.

 

[bernie10]

Yea Access has maintained the connection, but each time I close Access and reopen it, I get a "Logon to Oracle" box asking for user name and password when trying to open the linked table. Don't know why the password isn't being saved.

 

[lewds]

Hi...

DO you have a DSN on your machine for Oracle?

I'm sorry, I may have oversimplified the solution.

Here we have one Oracle application that provides us with a DSN ODBC driver.

We use this driver to establish a connection to the Oracle database.

We then link each table we want and save the password.


Are you linking multiple tables? Each table link must save the pasword.

 

[bernie10]

No DSN on my machine. I am linking to two tables off the oracle database. I only have to enter the password one time and that gets me into both tables. Then, as I mentioned earlier, each time I restart Access I have to login to Oracle again (even though I clicked save password when I initially setup the connection).

Not sure if there is a solution besides just coding it. Anyway thanks for your help.