Connecting to Oracle Database with VBA

[LaBelette]

Hi,

I try to connect to an Oracle Database using VBA, but i'm prompted to insert my username and password, which is a bad, bad thing!

Does anyone knows the syntax to put the username and password in my connection string?

 

[Pat Hartman]

Searching the archives will frequently answer your questions.
http://www.access-programmers.co.uk/forums/showthread.php?t=55986&highlight=oracle+connection

 

[LaBelette]

Still have a problem...

Searching the archives will frequently answer your questions.
http://www.access-programmers.co.uk/forums/showthread.php?t=55986&highlight=oracle+connection

Well, it does in some way, and thanks for the code , but I still have an unsolved problem, an AOP (Access Oriented Problem ).

I made some queries that uses some linked Oracle tables. The Oracle DB is secured by a username and password. When I run a query, an Oracle window pops, asking for a username/password, even if I already opened a connection to the Oracle DB in VBA, using Pat code, which I really apreciate . Pat Rocks!

Since those queries are called by a macro which is called by a VB application that runs overnight (using some automation), and since my client doesn't want to pay a guy to stay until 3 a.m. to enter the password , I need to bypass that.

Do anyone knows how to do that?

Anything except "stay there until 3 a.m." will do...

 

[Pat Hartman]

Notice the title of the function

ConnectToOracleBatch

. It was a function that I wrote specifically to connect when the database ran in unattended mode. The db that this came from opened sometime between 3 and 5 AM and ran shop floor reports for the upcomming day's production schedule and printed them to printers on the shop floor. The interactive db, used a different connection string because each user had an individual ID so the DBA could see who was "in" the db at any point in time.

If you are still getting prompted, either your security is set up incorrectly or you are using the wrong userid/password. Post your connection string and we'll look at it for syntax.

 

[LaBelette]

Notice the title of the function . It was a function that I wrote specifically to connect when the database ran in unattended mode. The db that this came from opened sometime between 3 and 5 AM and ran shop floor reports for the upcomming day's production schedule and printed them to printers on the shop floor. The interactive db, used a different connection string because each user had an individual ID so the DBA could see who was "in" the db at any point in time.

If you are still getting prompted, either your security is set up incorrectly or you are using the wrong userid/password. Post your connection string and we'll look at it for syntax.

Here it is:

Public Sub OuvrirConnexion()
    Dim Conn
    Set Conn = New ADODB.Connection
    Dim strConn As String
    
    strConn = "Server=myserver; DSN=myDSN; UID=myusername; PWD=mypassword" 
[COLOR=DarkGreen]' The connection works, I have no doubt about it. If I don't provide a password, it prompts for it. I know those infos are good, since I can link a table using them.[/COLOR]
    Conn.Open strConn
    
    DoCmd.OpenForm "FTest2" [COLOR=DarkGreen]' It prompt for a password here  [/COLOR] :(  

    Conn.Close
End Sub

 

[Pat Hartman]

Doesn't the string need to start with "ODBC;"? I don't have access to Oracle so I can't test anything for you.

 

[LaBelette]

Doesn't the string need to start with "ODBC;"? I don't have access to Oracle so I can't test anything for you.

I'm not sure if it should, but i'll test it next time I can access the Oracle Database... the 16th of June!

Since then, I'll just try yo get more info on this one... thanks a lot, Pat!