VBA & Get Select Result in visio

[unicorn4007]

Hi , I work with VBA in visio , and i conected to my Instance in sql sever 2005
,and now i want execute Select query and get the result of this query from one of my table.
i dont know how can i retrive the result of this qury without datatabel, database , and qureyDef

i cant define DAO.Database , QueryDef ,...

i use below code to conect database


Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data Source=unicorn-pc\mssqlserver2005;Initial Catalog=EEPDC;user id ='sa';password='12345'"

Set MyConn = CreateObject("ADODB.Connection")

Set myCommand = CreateObject("ADODB.Command")

MyConn.Open DB_CONNECT_STRING

Set myCommand.ActiveConnection = MyConn

myCommand.CommandText = "select RevisionID from dbo.Revision where ObjectID_FK=1 "


myCommand.Execute

MyConn.Close

 

[MarkK]

Hi. Welcome to the forum.
To work with data retrieved from a table using a SQL SELECT query you almost always need a recordset, not a command.

ADO is not my specialty but this code--which presumes you've set a reference to ADO--might get you started...

   Dim cn As New ADODB.Connection
   Dim rs As New ADODB.Recordset
   Dim text As String
   
   cn.ConnectionString = CurrentProject.Connection
[COLOR="Green"]   ' "Provider=SQLOLEDB.1;Data Source=unicorn-pc\mssqlserver2005;Initial Catalog=EEPDC;user id ='sa';password='12345'"[/COLOR]
   cn.Open
   With rs
      .Open "select RevisionID from dbo.Revision where ObjectID_FK=1", cn
[COLOR="Green"]      'work with recordset data here[/COLOR]
      Do While Not .EOF
         text = text & .Fields(0).Value & vbCrLf
         .MoveNext
      Loop
      If Not text = "" Then MsgBox text
      .Close
   End With
   cn.Close

 

[unicorn4007]

Hi lagbolt

thanks alot
you said exactly what i needed