Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help getting records from Oracle Database

Status
Not open for further replies.
bernie10

bernie10

Technical User
Sep 13, 2004
219
US
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:

Code: 
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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?

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

breezett93
  • Locked
  • Question
Converting from 32bit to 64 bit. After converting, database takes significantly longer to open 1
Replies
14
Views
1K
dhookom
dhookom
BFroeschlKC
  • Locked
  • Question
Publisher Automation from Access Frustration
Replies
3
Views
3K
dhookom
dhookom
FrankMars
  • Locked
  • Question
Exporting Records to Excel 1
Replies
6
Views
769
Andrzejek
Andrzejek
DarkOne72
  • Locked
  • Question
Message Box Popup on certain days help !
2
Replies
23
Views
6K
Norman Sherlock
Norman Sherlock
Viswa.R
  • Locked
  • Question
Dimension import from Autocad to Excel
Replies
0
Views
522
Viswa.R
Viswa.R

Part and Inventory Search

Sponsor

Back
Top