Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return MS-SQL database names to excel?
I have a workbook to which I want to return the database names residing in a
specific SQL database. I need the names in order to allow a user to choose a database name. This name will then be inserted into a connection string designed to import the data from the indicated database. I am pasting in the basic connection string used once the database name is known. Also, if anyone knows how to translate this string into one that doesn't use the array of arrays format... Thanks in advance, Mike ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DRIVER=SQL Server;SERVER=CONTROLSERV001\WINCC;UID=;APP=Micros oft Office XP;WSID=THE_MAN;DATABASE=CONTROLSERV001_H2gen#pc1_ TLG_S" _ ), Array("_200409171428;Trusted_Connection=Yes")), Destination:=Range("A1")) .CommandText = Array( _ "SELECT Archive.ValueName, TagUncompressed.TimeStamp, TagUncompressed.RealValue" & Chr(13) & "" & Chr(10) & "FROM ""CONTROLSERV001_H2gen#pc1_TLG_S_200409171428"".db o.Archive Archive, ""CONTROLSERV001_H2gen#pc1_TLG_S_200409171428"".db " _ , _ "o.TagUncompressed TagUncompressed" & Chr(13) & "" & Chr(10) & "WHERE Archive.ValueID = TagUncompressed.ValueID" & Chr(13) & "" & Chr(10) & "ORDER BY Archive.ValueName, TagUncompressed.TimeStamp" _ ) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return MS-SQL database names to excel?
<M.Streeks wrote ...
I have a workbook to which I want to return the database names residing in a specific SQL database. I need the names in order to allow a user to choose a database name. I assume you mean, 'return the database names residing on a specific SQL Server.' You can use ADO's OpenSchema method to do this e.g. Option Explicit Sub TestLateBound() Const CONN_STRING As String = "" & _ "Provider=SQLOLEDB.1;" & _ "Data Source=MYSERVER;" & _ "User Id=MYLOGON;password=MYPASSWORD;" Dim oConn As Object Dim oRs As Object Set oConn = CreateObject("ADODB.Connection") With oConn .CursorLocation = 3 ' adUseClient .ConnectionString = CONN_STRING .Open Set oRs = .OpenSchema(1) ' adSchemaCatalogs End With Dim lngRows As Long Dim lngCounter As Long With oRs lngRows = .RecordCount For lngCounter = 0 To lngRows - 1 Debug.Print !CATALOG_NAME .MoveNext Next .Close End With oConn.Close End Sub If you meant, 'return the table names residing in a specific SQL Server database', you can also use OpenSchema (using adSchemaTables). For details, see: http://msdn.microsoft.com/library/de...schemaenum.asp If you additionally need to enumerate SQL Servers, take a look at this thread from the archive: http://groups.google.com/groups?thre...ing.google.com Jamie. -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return MS-SQL database names to excel?
Hi
Just wondering, why don't you use the WinCC OLEDB Provider to get the data out? That way you wouldn't have to worry about the database names. Makes my life a lot easier when reporting on WinCC systems. cheers Salma |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return a list of names, from a large list of repeated names. | Excel Worksheet Functions | |||
How do I change column heading names in Excel database spreadsheet | Excel Discussion (Misc queries) | |||
Merge two spreadsheets with names database then search for duplica | Excel Discussion (Misc queries) | |||
DCOUNT to Return Values from Access database | Excel Discussion (Misc queries) | |||
How do I build a database of names and addresses? | Excel Discussion (Misc queries) |