Showing a list of SQL Tables under ODBC
Hi Everyone
I'm developing a macro to query SQL databases. Currently
I require the users to type in the name of their SQL
database for the code to work. Typing errors in the name
cause the code to fail. Is there any code I can use to
bring up the Data/Get External Data/New Database Query
Dialog box? Or better yet, a list of just the SQL
databases available through ODBC.
My current code is as follows.
Sub DemoQuickTrans()
Dim strConnectionString As String
Dim strSQLDatabaseName, strSQLName, strSQLDatabase,
strLoginID, strPassword As String
Dim strSqlString, strVersion, strSUNDatabase,
strPeriodFrom, strPeriodTo As String
Dim intFindDot As Integer
'Poplulate Login Strings
strLoginID = Range("Login_ID").Value
strPassword = Range("Password").Value
strSQLDatabaseName = Range("SQL_Database_Name").Value
'strSQLDatabaseName = YOUR-HPBMYE9TMB.SUNDB
'was hoping for
'strSQLDatabaseName = dialog .... .show or a list of SQL
databases
intFindDot = Application.WorksheetFunction.Find(".",
strSQLDatabaseName, 1)
strSQLName = Left(strSQLDatabaseName, intFindDot - 1)
strSQLDatabase = Mid(strSQLDatabaseName, intFindDot + 1,
100)
'I saw somewhere on the Google search that the WSID is
not necessary, can anyone tell me what it is and if not
why not?
strConnectionString = _
"ODBC;DSN=" & strSQLDatabaseName & ";UID=" & strLoginID
& ";PWD=" & strPassword & ";SQL " & _
";WSID=" & strSQLName & ";DATABASE=" & strSQLDatabase
& ";AutoTranslate=No,"
'and so on
End Sub
Thanks in advance
Matt
|