用ado,函数如下:Function ConvertToTable(ByVal MSHFlexGrid As MSHFlexGrid, ByVal TableName As String, ByVal Connstr As String) As Long On Error GoTo Errline Dim Conn As New Connection Dim 字段() As String Dim SqlStr As String Dim i As Long Dim ii As Long With MSHFlexGrid If .Rows < 2 Then Exit Function '若表格中无数据则退出转换过程 ReDim 字段(.Cols - 1) As String Conn.Open Connstr '打开数据库连接 For i = 0 To UBound(字段) 字段(i) = .TextMatrix(1, i) SqlStr = SqlStr & "'" & 字段(i) & "' as " & .TextMatrix(0, i) & "," Next SqlStr = "select " & Mid(SqlStr, 1, Len(SqlStr) - 1) & " into " & TableName
Conn.Execute SqlStr '建立表 '插入行 For i = 2 To .Rows - 1 SqlStr = "" For ii = 0 To .Cols - 1 字段(ii) = .TextMatrix(i, ii) SqlStr = SqlStr & "'" & 字段(ii) & "'," Next SqlStr = Mid(SqlStr, 1, Len(SqlStr) - 1) SqlStr = "insert into " & TableName & " values(" & SqlStr & ")" Conn.Execute SqlStr '插入一行 Next
End With
Conn.Close Set Conn = Nothing ConvertToTable = 0 Exit Function
Errline: Select Case Err.Number Case "-2147217900" MsgBox Err.Description Conn.Close ConvertToTable = Err.Number Case Else MsgBox "异常错误" Debug.Print Err.Number ConvertToTable = Err.Number End Select End Function调用例子: Dim Connstr As String Connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb;Persist Security Info=False" If ConvertToTable(MSHFlexGrid1, "新建", Connstr) = 0 Then MsgBox "转换成功" End If
比如: Dim Conn As New Connection Dim rs as ADODB.Recordset Dim k Dim Sql ...... rs.Open Sql, Conn, adOpenKeyset, adLockOptimistic For k=1 to MSHFlexGrid1.Rows-1 rs.AddNew With rs .Fields(0)= MSHFlexGrid1.TextMatrix(k, 1) .Fields(1)= MSHFlexGrid1.TextMatrix(k, 2) .Fields(2)= MSHFlexGrid1.TextMatrix(k, 3) ...... End With rs.Update
On Error GoTo Errline
Dim Conn As New Connection
Dim 字段() As String
Dim SqlStr As String
Dim i As Long
Dim ii As Long With MSHFlexGrid
If .Rows < 2 Then Exit Function '若表格中无数据则退出转换过程
ReDim 字段(.Cols - 1) As String
Conn.Open Connstr '打开数据库连接
For i = 0 To UBound(字段)
字段(i) = .TextMatrix(1, i)
SqlStr = SqlStr & "'" & 字段(i) & "' as " & .TextMatrix(0, i) & ","
Next
SqlStr = "select " & Mid(SqlStr, 1, Len(SqlStr) - 1) & " into " & TableName
Conn.Execute SqlStr '建立表
'插入行
For i = 2 To .Rows - 1
SqlStr = ""
For ii = 0 To .Cols - 1
字段(ii) = .TextMatrix(i, ii)
SqlStr = SqlStr & "'" & 字段(ii) & "',"
Next
SqlStr = Mid(SqlStr, 1, Len(SqlStr) - 1)
SqlStr = "insert into " & TableName & " values(" & SqlStr & ")"
Conn.Execute SqlStr '插入一行
Next
End With
Conn.Close
Set Conn = Nothing
ConvertToTable = 0
Exit Function
Errline:
Select Case Err.Number
Case "-2147217900"
MsgBox Err.Description
Conn.Close
ConvertToTable = Err.Number
Case Else
MsgBox "异常错误"
Debug.Print Err.Number
ConvertToTable = Err.Number
End Select
End Function调用例子:
Dim Connstr As String
Connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb;Persist Security Info=False" If ConvertToTable(MSHFlexGrid1, "新建", Connstr) = 0 Then
MsgBox "转换成功"
End If
数据库我已经打开了,
当我修改时或加一行时,我如何将mshflexgrid数据保存到数据库中?
Dim Conn As New Connection
Dim rs as ADODB.Recordset
Dim k
Dim Sql
......
rs.Open Sql, Conn, adOpenKeyset, adLockOptimistic
For k=1 to MSHFlexGrid1.Rows-1
rs.AddNew
With rs
.Fields(0)= MSHFlexGrid1.TextMatrix(k, 1)
.Fields(1)= MSHFlexGrid1.TextMatrix(k, 2)
.Fields(2)= MSHFlexGrid1.TextMatrix(k, 3)
......
End With
rs.Update