Conneting Oracle database with Excel using Macros

Question

I'm trying to connect to oracle database with excel using macros.

I was able to do it by writing the userid and password in the code which makes it static. I want to connect to the database using a login popup and make it dynamic.

I have created a sheet and was trying to do the same using the Vlookup functionality, but the code is not executing properly.

Here is the code I have used to connect to the database:

strConnection = "Provider=OraOLEDB.Oracle;" & _
                "Data Source=(DESCRIPTION=(CID=xxxxxx)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=xxx)(HOST=xxxx)(PORT=xxxx)))" & _
                "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxxxx)));" & _
                "User Id=myusrid;" & _
                "Mypswd;"

Any help or suggestion is appreciated.

Siddharth Rout · Accepted Answer · 2019-03-11 09:03:19Z

Let's undestand the basics first. Consider this code

s = "This is a sample string with ID:=MyID and password:=MyPassword"

Let's say you want to replace MyID and MyPassword at run time. So all you have to do is store the details in 2 variables and then use it in the main string. For Example

Sub sample()
    Dim s As String
    Dim id As String, pass As String

    id = "Sid"
    pass = "SomePassword"

    s = "This is a sample string with ID:=" & id & " and password:=" & pass

    Debug.Print s
End Sub

When you run this, you will see that the values have been replaced.

Now let's apply this to your question.

Your string can be written as

strConnection = "Provider=OraOLEDB.Oracle;" & _
                "Data Source=(DESCRIPTION=(CID=xxxxxx)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=xxx)(HOST=xxxx)(PORT=xxxx)))" & _
                "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxxxx)));" & _
                "User Id=" & ID & ";" & _
                PASS & ";"

Now all you have to do is accept the input from the user and store then in ID and PASS.

Thanks for the explanation. But how can i create a popup window, where i can put my username and password and it should connect to the database only if the credentials are right. It's quite a task but I'm trying to figure out the solution. But no luck yet. if you have any suggestion, kindly update me. Thanks in advance. — P. Mehta, Commented Mar 11, 2019 at 9:22
exceldashboardschool.com/userform-data-entry-vba Also do a search in Google on how to accept inputs via userform — Siddharth Rout, Commented Mar 11, 2019 at 9:26

Collectives™ on Stack Overflow

Conneting Oracle database with Excel using Macros

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related