Creating Oracle DSN's Programatically

[Gaylord]

Hi,

Does anyone know how to create a DSN for Oracle programatically using VB? I have found code snipets for SQL server, but nothing so far for Oracle.... The reason for doing this is that I have (potentially) 400 or more seperate DB's to use at any one time and the prospect of setting these up on more than one machine, which incidentally there may be between 5 and 10 to do.... I am scared that my sanity will last that long!

If anyone has any ideas then please let me know!

Very best regards

Jamie

 

[sem]

Are you sure your application would work with 400 databases, and not with 400 tables or schemas?

Regards, Dima

http://seminihin.narod.ru

 

[Gaylord]

Ahh ok.

Not to go into two much information, but each database exists uniquely as a district or smaller region.

These are then queried and updated individually. The data structure is identical and really only the amount of data is different, depening on the region, eg a borough of London would be considerably higher than something in the highlands......

But I sill need to find a way to connect to each database to enable the queries and updates to be performed....

Any ideas?

Thanks

Jamie

 

[sem]

Ok, do you know what Oracle terms database, instance, schema mean? Again, do you need to connect to 400 databases? I have some doubt that such complex task may be charged with VB

Regards, Dima

http://seminihin.narod.ru

 

[Gaylord]

Ahh,

Possibly not.....

What I am trying to do is to create the ODBC DSN links to the databases through VB. Now, I can do this quite easily for SQL server databases, but unfortunately this time I have been given an Oracle database(s) to work with.

I think it should be quite simple, but I keep being directed to SQL examples.... Any ideas?

Thanks

Jamie

 

[taupirho]

Gaylord, this is how I did it for microsoft text driver.
You probably want to change the
strdriver = ... and
atts = ....

lines below to something like:-

strdriver = "Microsoft ODBC for ORACLE"
atts = "DSN=" & "Oracle Files"

Hope this gives you some extra clues


Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" _
(ByVal hwndparent As Long, ByVal frequest As Long, _
ByVal lpszdriver As String, ByVal lpszattributes As String) _
As Long

Sub Main()

Dim intret As Long
Dim atts As String
Dim strdriver As String

strdriver = "Microsoft Text Driver (*.txt; *.csv)"
atts = "DSN=" & "Text Files"
intret = SQLConfigDataSource(0, 1, strdriver, atts)

If intret Then
Msgbox("DNS created sucessfully")
Else
MsgBox ("There was a problem setting up the DSN, please contact support")
End If

End Sub

 

[Gaylord]

Thanks for this. I'll give it a try. Hopefully this is the light at the end of the tunnel!

Regards

Jamie