Create ODBC connection using VBA?

[bst...@my-deja.com]

I have created a number of VBA procedures in various files that query
ODBC data-sources and return the data to Excel for formatting or further
manipulation. These files are generally for use ultimately by parties
other than myself. Currently, when I'm ready to pass off one of these
files to the end-user, I have to manually check their machine to see
that they have an OBDC data source name and mapping that match what I
have set up on my PC, or I have to modify the code of the query to match
what they may already have set up. (Unfortunately, there's no
standardization of ODBC names)
So, is it possible, using VBA to:

1) Check for the existence of the correct ODBC DSN and mapping?;

2) Create an ODBC connection, if the matching one is not found?

Any help on this is greatly appreciated.

Strack

Sent via Deja.com http://www.deja.com/
Before you buy.

[Harald Staff]

Hi Strack

This is one of the problems that "what excel version?" is important. If you
use 2000, then you don't really need the data source to be defined by the
user. Address the database directly by name.
This is code I use to retrieve Oracle data (keywords altered for security
reasons :-).

Sub GetOracleList(Valg As Long)

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
Dim Sok As String

Sok = "SELECT PROG_ID, TITTEL" & Chr(10)
Sok = Sok & "FROM ORADUMMY.PROG" & Chr(10)
Sok = Sok & "WHERE PROG_ID = " & Valg
'here it is:
cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
"Server=ORADUMMY.world;" & _
"Uid=HaraldAtWork;" & _
"Pwd=NiceTryHarald;"
rst.Open Sok, cnn, adOpenForwardOnly, adLockReadOnly

Sheets(1).Cells(2, 1).CopyFromRecordset rst
rst.Close

End Sub

See http://www.able-consulting.com/tech.htm on how to connect to different
databases and for more info on this technique.

Best wishes Harald

<bst...@my-deja.com> skrev i news:8s0l81$ahb$1...@nnrp1.deja.com...

[bst...@my-deja.com]

Thanks for the reply, Harald. I'm using Excel 97 SR2 on an NT
platform -- so, let me know if that changes the illustration you
provided.

Thanks,
Strack

In article <ebpAYh1MAHA.249@cppssbbsa05>,

[Harald Staff]

Yes it does. You have to use DAO -which I know really nothing about. Anyone
?
(If not try starting a new thread including Excel version and database type)

Best wishes Harald

<bst...@my-deja.com> skrev i news:8s1rio$7p7$1...@nnrp1.deja.com...