Automatically Connecting to Oracle database in code... 1

[THoey]

I am reposting this question to see if I might be able to figure out a problem I am having. I have an Oracle 7.3.4 table that I want to download nightly into Access 97. I have almost everything else set up to automatically run this process except I can't get Access to connect or link to the Oracle database without someone being there to provide the login and password.

I think my problem boils down to the connect string. Does anyone have an example of a connect string to an Oracle database that they run in VB? I have all the information and connect to this table manually all the time, but just need to figure out the format to do it in code. I have read and reread the help and example and, well, they are very vague due to the numerous "other" databases that Access will link to.

All help would be appreciated. Thanks...


Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[PaulF]

when you set up your link to the Oracle database (using ODBC), you should see a checkbox that asks if you want to save password (on the tab where you select the table from), click this and you should not have to enter the data again, it gets saved as part of the connect string.

PaulF

 

[THoey]

Thanks Paul. This works fine. I was just thinking that I could do it all in code. Access has the functionality, but I just couldn't get the syntax right.

But, Thanks a lot!

Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[huzza]

Hi Terry,
I not sure what code you want it depends if you want to open an ODBC database or link to it. However you can get most of these details from the access help. If I read what you're saying correctly then you want to know what connection string to use.
The easiest way to find this out is to create a new query in access and cancel the table/query selection window. Then from the query menu choose SQL specific, pass-through. This changes your query to a pass-through query. Now go into the properties (either right click on the query title bar or click on the properties button on the toolbar). The property window has an OBDC connect string field. Click in this the click on the build (button with ...) this allows you to select a DSN from your ODBC drivers and enter a user name and password. When you've done this it'll ask if you want to save the password in the connection string. Say yes/ok to this and viola you have the connection string you need.

If you look at the following faq I wrote it tells you how to refresh ODBC linked tables in code.

http://www.tek-tips.com/faqs.cfm?spid=181&sfid=305&CFID=8833112&CFTOKEN=85920935

Let me know if you have any more questions,
Richard