' Contenido del formulario:
' - 1 Text
' - 1 MSFlexGrid
' - 1 CommandButton
Option Explicit
Private cnn As ADODB.Connection
Private rst As ADODB.Recordset
Private Sub Command1_Click()
llenagrilla
End Sub
Private Sub Form_Load()
llenagrilla
End Sub
Sub llenagrilla()
' Por si ya estaba abierta...
Set cnn = Nothing
Set rst = Nothing
' Crear los objetos
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
Dim ni As Integer, strcnn As String, sql As String
Dim tField As ADODB.Field
' abrir la base usando las credenciales de Windows
strcnn = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=True;" & _
"Initial Catalog=pubs"
cnn.Open strcnn
' abrir el recordset indicando la tabla a la que queremos acceder
If Text1.Text = "" Then
sql = "SELECT titles.title, authors.au_lname, titles.price " & _
"FROM authors INNER JOIN " & _
"titleauthor ON authors.au_id = titleauthor.au_id INNER JOIN " & _
"titles ON titleauthor.title_id = titles.title_id"
rst.Open sql, cnn, adOpenDynamic, adLockOptimistic
Else
sql = "SELECT titles.title, authors.au_lname, titles.price " & _
"FROM authors INNER JOIN " & _
"titleauthor ON authors.au_id = titleauthor.au_id INNER JOIN " & _
"titles ON titleauthor.title_id = titles.title_id " & _
"WHERE titles.title like '%" & Text1.Text & "%'"
rst.Open sql, cnn, adOpenDynamic, adLockOptimistic
End If
MSFlexGrid1.Rows = 1
MSFlexGrid1.FixedCols = 0
ni = 1
If Not rst.EOF Then
MSFlexGrid1.Cols = rst.Fields.Count
MSFlexGrid1.ColAlignment(2) = 4
MSFlexGrid1.ColWidth(0) = 2000
MSFlexGrid1.ColWidth(1) = 1000
MSFlexGrid1.ColWidth(2) = 1000
Do While Not rst.EOF
MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
MSFlexGrid1.TextMatrix(ni, 0) = rst.Fields.Item(0).Value 'rst!COMUNA
MSFlexGrid1.TextMatrix(ni, 1) = rst!au_lname
MSFlexGrid1.TextMatrix(ni, 2) = IIf(IsNull(rst!price) = False, rst!price, "")
ni = ni + 1
rst.MoveNext
Loop
End If
rst.Close
cnn.Close
End Sub