How to fetch User DSN Names and show it as a Drop Down

[Kandavalli.Kiran]

Team,

I got this code from one of the website, when I execute the below code the results are showing in a Message Box. I need to show it in a Drop Down Box and when I hit Connect Button, it should connect to the respective Data Source Selected.

As I am new to VBA Programming, can Someone help me in changing this code please.

Please Login or Register  to view this content.

[dflak]

This works for me. Basically, instead of building a string to display in a message box, I write the results out to a sheet called Lookups. This, in turn, is overlaid with a named dynamic range called List_Connections and this is used as the source for a list type data validation on the Dropdown sheet. The changed code is shown in red.

Please Login or Register  to view this content.

[dflak]

Ooops, just noticed that I only answered half the question. I'll get back to you.

[dflak]

I have some follow on questions. What is the purpose for changing the connection? Also are all your connections of the same type? I have a mix of Access, Excel, ODBC to Oracle databases and several text file directories. Each has a different connection string.

Then after you connect to the source, what are you going to do with it, do you have a SQL string?

It would help if you could define the bigger picture.

[Kandavalli.Kiran]

Hi,

Thanks for your response & big Thanks to you!

In my User DSN's I also have 'MS Access Database' and 'dBASE File' and 'Denodo_DEV'. I am not sure what is connection string is

For Denodo_DEV I tried to record the Macro. so when I connect through Data --> Get External Data --> Denodo DEV

The code it generated is. Please let me know if it helpful. For Other ones MS- Access and dBase Files those are by Default DSN's which are not added by me.

Please Login or Register  to view this content.

Thanks!
Kiran

[dflak]

Each type of connection has a different format. We are missing part of the puzzle here: we have the connection name, we do not have the connection type. We'd need that information in a case statement to build a proper connection string.

If you can tell me what the end result is, there may be alternatives.

[Kandavalli.Kiran]

The End Result is.

I just want to compare Source & target Queries.

Example

1) Lets say we have Two Databases

a) Source Database
b) Target Database

2) I need to Compare Source Table Data with Target Table Data

3) For Comparison, I will create Source Query & Target Query and Store it in two file

4) We have to create User Form

a) Text Box 1 - with Browse Button to select the Source Query File
b) Drop Down 1 - to select Source DataSource - which should be the Drop down of User DSN's
c) Text Box 2 - with Browse button to select the Target Query File
d) Drop Down 2 - to select Target DataSource - Which should be the Drop down of User DSN's
e) Now we have to compare with Source Query File with Target Query file based on Data sources selected in Drop down's.
f) In a separate sheet we have to populate the Unmatched Records


Thanks!
Kiran



a) Text Box1 with browse Button.

b) Text Box 2

[dflak]

OK - got it. I'll be out of the office until later next week.

I already have a system for rewriting SQL on the fly. I am fairly certain that once I know the type of connection I can substitute the specific database in. Figuring out how to find the type of connection will be the issue. I might tear into that initial code and see if it can provide type as well.

[Kandavalli.Kiran]

I will ask the Database Team here.

Also, can you please check if the below URL is helpful in getting the info.

https://community.denodo.com/tutoria...ect2odbcclient

[dflak]

I dug into the original code. It reads the windows registry, so I followed the path and the registry entry they use does have the driver information. However, I don't know how to extract it ... yet. This could be a bit of a research project.

[dflak]

I made some headway and am now at the next impasse. The attachment shows the connections on my machine. There would be no issue on making a connection string for the oracle databases. However for the rest of them, you have to know the location of the text file, spreadsheet or access database for it to happen. There is a default dir for the text files, but not for Access or Excel.

So, in addition to providing the connection name, if the connection is one of these other types, then you will also have to provide the full pathname to the Excel or Access file or the directory that contains the text files.

Is this what you had in mind?

[Kandavalli.Kiran]

Hello,

I am connecting to Denodo / Netezza through Postgress and the Driver (Postgress DLL file) is there in the below path.

PATH

C:\Program Files (x86)\psqlODBC\0905\bin\psqlodbc30a.DLL

and I added User / System DSN as in the attached file and for PORT NUMBER and Server Name please refer to the below code.

I am still need to get the info on Connection Name. Database team said the Connection Name is the Name what we give in creating DSN.


Thanks!
Kiran

[dflak]

Is this the only kind of database to which you wish to connect? Also can you provide a sample connection string.

Also run the macro in the attachment and show me your results. We may be closing in on this.