Private cn As ADODB.Connection
Private rs As ADODB.Recordset

Private Sub Form_Load()
Dim cmd As String
Dim sql As String

'создать строку подключения
cmd = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=ABC;Data Source=shtet"

'установить соединение с базой данных
Set cn = New ADODB.Connection
cn.ConnectionString = cmd
cn.Open

Set rs = New ADODB.Recordset

Set rs = cn.OpenSchema(adSchemaTables)

'заполняем lstTables таблицами
Do Until rs.EOF
'если не системная таблица то добавляем
  If Left(rs!TABLE_TYPE,  6 ) <> "SYSTEM" Then
    lstTables.AddItem rs!TABLE_NAME
  End If
  rs.MoveNext
Loop

End Sub

Private Sub Form_Unload(Cancel As Integer)
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

End Sub

Private Sub lstTables_Click()

''''''rs.Source = "select * from " & lstTables
'что тут писать, ну не как не пойму

End Sub

rs.Open sql, cn, adOpenStatic, adLockPessimistic

Run-time error '3219'
Operation is not allowed in this context

rs.Close

Private Sub lstTables_Click()
Dim cmd As String
Dim sql As String
sql = "select * from " & lstTables.Text
rs.Close 'вот тут на некоторых таблицах вываливается
rs.Open sql, cn, adOpenStatic, adLockPessimistic

Set DataGrid_table.DataSource = rs
DataGrid_table.ReBind

End Sub

Private Sub lstTables_Click()
Dim cmd As String
Dim sql As String
sql = "select * from " & lstTables.Text

If rs.State=adStateOpen then rs.Close ' на всякий случай

Set rs=Nothing
Set rs = New ADODB.Recordset '

rs.Open sql, cn, adOpenStatic, adLockPessimistic

Set DataGrid_table.DataSource = rs
DataGrid_table.ReBind

End Sub

Run-time error '7004':
The rowset is not bookmarkable

Set DataGrid_table.DataSource = rs