Sub ShowGridData()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Set cn = New ADODB.Connection
cn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database password=hsxr1314;Data Source=" + App.Path + "\data.mdb"
'查询"用户登记表"中的所有记录
sql = "select * from person Union All select * from zperson"
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenForwardOnly, adLockOptimistic '____________将记录集内容显示到MSHFlexGrid1中______________
Set MSHFlexGrid1.Recordset = rs With MSHFlexGrid1 '------变色
For i = 1 To .Rows - 1 If .TextMatrix(i, 8) = "男" Then
.Row = i
.Col = 8
.CellForeColor = vbRed End If
Next i
End With
MSHFlexGrid1.Refresh
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
。
。
。-------------------------因为要经常用到所以独立了这段代码ShowGridData显示没问题,但添加person zperson 这两个表修改数据后,zperson的最后一条不能马上显示修改后的数据。把窗口关了再打开又显示了。操作是在同一个窗口。修改Command_Click后执行了CALL ShowGridData 。奇怪的是就只有zperson的最后一条。
选中某行删除Command也是,有的时候很正常CALL ShowGridData 后被删的那条记录不显示了,有的时候会还显示在那,再选中按删除Command 提示记录不存在。这是不是MS的BUG啊?
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Set cn = New ADODB.Connection
cn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database password=hsxr1314;Data Source=" + App.Path + "\data.mdb"
'查询"用户登记表"中的所有记录
sql = "select * from person Union All select * from zperson"
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenForwardOnly, adLockOptimistic '____________将记录集内容显示到MSHFlexGrid1中______________
Set MSHFlexGrid1.Recordset = rs With MSHFlexGrid1 '------变色
For i = 1 To .Rows - 1 If .TextMatrix(i, 8) = "男" Then
.Row = i
.Col = 8
.CellForeColor = vbRed End If
Next i
End With
MSHFlexGrid1.Refresh
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
。
。
。-------------------------因为要经常用到所以独立了这段代码ShowGridData显示没问题,但添加person zperson 这两个表修改数据后,zperson的最后一条不能马上显示修改后的数据。把窗口关了再打开又显示了。操作是在同一个窗口。修改Command_Click后执行了CALL ShowGridData 。奇怪的是就只有zperson的最后一条。
选中某行删除Command也是,有的时候很正常CALL ShowGridData 后被删的那条记录不显示了,有的时候会还显示在那,再选中按删除Command 提示记录不存在。这是不是MS的BUG啊?
那是多大的工程量啊,大大给个代码SEE SEE
如果量实在太大,用DATAGRID也不错
Dim str As String, rs As New adodb.Recordset
Dim cmd As New adodb.Command
Dim ReturnValue As String
Dim i As Integer
Dim txtSQL As String
Dim x
x = Form2.RC1
str = "Driver={SQL Server};Server=sagemserver;Database=Suivi;Uid=devtest;Pwd=test"
Set cmd.ActiveConnection = Cnn(str)
DataGrid1.Refresh
If Text1.Text = "" Then
MsgBox ("Please input PN!")
Else
' cmd.CommandText = "SELECT ni_num,DATEDIFF(Minute,min(mt_dmvt),max(mt_dmvt)) From MvtTest WHERE ni_num not in (select ni_num from MvtTest where mt_statut='GO' and mt_passage=1 and mt_test='FUNCT_TEST') and pt_num ='" & Text1.Text & "'and mt_dmvt<'" & DTPicker2.Value & "' and mt_dmvt>'" & DTPicker1.Value & "' and MvtTest.mt_test ='" & Combo1.Text & "'AND MvtTest.mt_passage>1 group by ni_num,mt_testeur having DATEDIFF(Minute,min(mt_dmvt),max(mt_dmvt))>20 or ni_num in (select ni_num from MvtTest where mt_testeur='" & Form2.RC1 & "' or mt_testeur='" & Form2.RC2 & "' or mt_testeur='" & Form2.RC3 & "' ) "
cmd.CommandText = "SELECT ni_num,DATEDIFF(Minute,min(mt_dmvt),max(mt_dmvt)) From MvtTest WHERE pt_num ='" & Text1.Text & "'and mt_dmvt<'" & DTPicker2.Value & "' and mt_dmvt>'" & DTPicker1.Value & "' and MvtTest.mt_test ='" & Combo1.Text & "'AND MvtTest.mt_passage>1 and mt_statut='GO' group by ni_num having (DATEDIFF(Minute,min(mt_dmvt),max(mt_dmvt))>20 and ni_num not in (select ni_num from MvtTest where mt_statut='GO' and mt_passage=1 and mt_test='" & Combo1.Text & "') ) or ni_num in (select ni_num from MvtTest where mt_testeur='" & Form2.RC1 & "' or mt_testeur='" & Form2.RC2 & "' or mt_testeur='" & Form2.RC3 & "' ) "
Set rs = New adodb.Recordset
rs.CursorType = adOpenDynamic
rs.CursorLocation = adUseClient
cmd.Execute
Set rs.Source = cmd
rs.Open
str = rs.RecordCount
If Not rs.EOF Then
rs.MoveFirst
Set Adodc1.Recordset = rs
End If
End IfEnd Sub
如果要快速的话俺就改用DATAGRID
国际歌说的好,世上本来就没有救世主
还得靠自己
With MSHFlexGrid1 '------变色
For i = 1 To .Rows - 1 If .TextMatrix(i, 8) = "男" Then
.Row = i
.Col = 8
.CellForeColor = vbRed End If
Next i
End With我想问下DATA没有rows属性这段代码要怎么实现呢?
MSHFlexGrid1.Refresh With MSHFlexGrid1 '------变色
For i = 1 To .Rows - 1 If .TextMatrix(i, 8) = "男" Then
.Row = i
.Col = 8
.CellForeColor = vbRed End If
Next i
End With
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
'////////楼主试一试这代码
我还特地去看数据库,点一下的时候数据库已经存进数据了,但就是不显示,而且就是最后一条。删除也是,有的时候删了就马上不会在MS里显示了,有的时候删了就还会显示在MS里。怪异怪异。
这样很容易出现数据刷新不一致的问题。
你这样尝试一下,在一个模块里,定义一个公共的数据连接对象,程序启动时就连接数据库,
程序关闭时就关闭数据连接并卸载对象。
在程序里,全部用同一个数据连接对象,相信问题就解决了。
如:'========== 模块里定义 ==========
Public Conn As Connection'程序启动
Sub Main()
Set Conn = New Connection
Conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database password=hsxr1314;Data Source=" & App.Path & "\data.mdb"
...
Form1.Show
End Sub'========== 窗口代码 ==========
'退出程序
Private Sub Form_Unload(Cancel As Integer)
Conn.Close
Set Conn = Nothing
End Sub'自定义使用过程
Private Sub ShowGridData()
Dim rs As New Recordset
Dim sql As String
'...
'这里就直接用模块里的公共变量就可以了
rs.Open sql, Conn, 3, 3
'...
End Sub