VB txt文件导入access及MSSQL数据库实例

模块内容： '*********************************************************** '**模 块 名 connection '*********************************************************** Option Explicit Public rz As New Recordset Public Const constrPubs = "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=pubs;Data Source=xxx" Public Const constrAccess = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xx.mdb;Persist Security Info=False" 模块内容： '*********************************************************** '**设计窗体代码 Form1 '*********************************************************** Option Explicit '''''''以下引用的API函数用于“选择目录”： Private Declare Function SHBrowseForFolder Lib "shell32" (lpbi As BrowseInfo) As Long Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _ "SHGetPathFromIDListA" (ByVal pIdl As Long, ByVal pszPath As String) As Long Private Type BrowseInfo hwndOwner As Long piDLroot As Long pszdisplayName As String lpsztitle As String ulFlags As Long lpfncallback As Long lParam As Long iImage As Long End Type Private Sub Command1_Click() On Error GoTo HaveErr Dim str As String ''''用于暂存从txt文件中读取的一行数据。 str = "" Dim strFields() As String ''''用于暂存从txt文件中读取的一行数据（即一条记录）分割后的各字段。 Dim pos, count, spacelen As Integer Dim lngRow, lngLoop, lngCount As Long Dim freenum As Integer Dim lngTitle As Long '''''用于生成MSHFlexGrid1控件标题栏 freenum = FileSystem.FreeFile '''获取一个空的文件号 With CommonDialog1 .Filter = App.Path & "*.txt|*.txt" .FileName = "" .ShowOpen If Trim(.FileName) = "" Then Exit Sub End If '''''语句：InStrRev(.FileName, "\")用于获取文件绝对路径字符串（“.FileName”）的最后一个分割符“\”的位置（数字）。 Label6.Caption = Trim(Mid(.FileName, InStrRev(.FileName, "\") + 1)) '''''如下语句的含义：Replace(.FileName, App.Path, vbNullString)。 count = LineCount(.FileName) ''''获取txt文件的行数 Me.MSHFlexGrid1.Clear Me.MSHFlexGrid1.Rows = count + 1 - 8 Me.MSHFlexGrid1.Cols = colCount(.FileName) + 1 lngRow = 0 Open .FileName For Input As freenum For lngLoop = 1 To 6 Line Input #freenum, str strFields = Split(str, Chr(9), , vbTextCompare) ''''读取以{Tab}分割的一行文本 lngCount = UBound(strFields) - LBound(strFields) + 1 '''''获取数组中元素的个数。 Select Case lngLoop Case 1 If Trim(strFields(0)) <> "" Then Label1.Caption = Trim(strFields(0)) Else MsgBox "导入的文件内容有所更改，请核对！" Exit Sub End If Case 3 If Trim(strFields(0)) <> "" And Trim(strFields(1)) <> "" And Trim(strFields(3)) <> "" Then Label2.Caption = Trim(strFields(0)) Label3.Caption = Trim(strFields(1)) Label4.Caption = Trim(strFields(3)) Else MsgBox "导入的文件内容有所更改，请核对！" Exit Sub End If Case 5 '''''生成MSHFlexGrid1控件标题栏 Me.MSHFlexGrid1.Cols = lngCount + 1 For lngTitle = 1 To lngCount Me.MSHFlexGrid1.TextMatrix(0, lngTitle) = strFields(lngTitle - 1) Next End Select Next For lngRow = 1 To Me.MSHFlexGrid1.Rows - 1 ''''''Do While Not EOF(freenum) ''''lngRow = lngRow + 1 Line Input #freenum, str str = Trim(str) '''pos = InStr(1, str, " ", vbTextCompare) '''获取获取第一个空格所在的位置。 strFields = Split(str, Chr(9), , vbTextCompare) ''''读取以{Tab}分割的一行文本 lngCount = UBound(strFields) - LBound(strFields) + 1 '''''获取数组中元素的个数。 Me.MSHFlexGrid1.Cols = lngCount + 1 Me.MSHFlexGrid1.TextMatrix(lngRow, 0) = lngRow For lngLoop = 1 To lngCount Me.MSHFlexGrid1.TextMatrix(lngRow, lngLoop) = strFields(lngLoop - 1) Next Next Line Input #freenum, str Line Input #freenum, str '''''执行两遍本语句，以读取文本文件的最后一行 strFields = Split(str, Chr(9), , vbTextCompare) ''''读取以{Tab}分割的一行文本 If Trim(strFields(0)) <> "" Then Label5.Caption = Trim(strFields(0)) Else MsgBox "导入的文件内容有所更改，请核对！" Exit Sub End If Close freenum End With Exit Sub HaveErr: If MsgBox(Err.Description, vbRetryCancel, "错误号: " & Err.Number) = vbRetry Then Resume End If End Sub Private Function LineCount(sFilePath As String) As Integer '''''用于获取指定文件的行数 Dim ICount As Integer '定义变量 记录文件行数 Dim MyLine As String Dim freenum As Integer freenum = FileSystem.FreeFile '''获取一个空的文件号 Open sFilePath For Input As #freenum ' 打开文件 Do While Not EOF(freenum) ' 循环至文件尾 Line Input #freenum, MyLine ' 读入一行数据并将其赋予某变量 ICount = ICount + 1 Loop Close #freenum LineCount = ICount End Function Private Function colCount(sFilePath As String) As Integer '''''用于获取指定文件的最大列数(最大列数是相对而言的，本例是根据所有行数据中“双空格”的最大数量来统计的)。 Dim CCount, colMaxCount As Integer '定义变量 记录文件列数 Dim freenum As Integer Dim strFields() As String Dim str As String freenum = FileSystem.FreeFile '''获取一个空的文件号 Open sFilePath For Input As #freenum ' 打开文件 Do While Not EOF(freenum) ' 循环至文件尾 Line Input #freenum, str ' 读入一行数据并将其赋予某变量 str = Trim(str) strFields = Split(str, " ") CCount = UBound(strFields) - LBound(strFields) + 1 If CCount > colMaxCount Then colMaxCount = CCount End If Loop Close #freenum colCount = colMaxCount End Function '''Private Sub Form_Load() '''Set cs_con_bas = New ADODB.Connection '''cs_con_bas.Open constrTT '''Set rz = New Recordset '''rz.Open "select top 20 * from dx1w ", cs_con_bas, adOpenKeyset, adLockPessimistic '''Set MSHFlexGrid1.DataSource = rz '''End Sub Private Sub Command2_Click() On Error GoTo CodeHaveError If MsgBox("确认保存吗?", vbOKCancel, "操作提示") = vbCancel Then Exit Sub End If Dim lngRow As Long Dim lngRowCount As Long Dim lngCol As Long Dim lngPsnt As Long Dim cs As New ADODB.Connection Dim rs As New ADODB.Recordset Set cs = New ADODB.Connection cs.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=pubs;Data Source=cxj" Set rs = New Recordset cs.BeginTrans rs.ActiveConnection = cs rs.CursorLocation = adUseClient rs.CursorType = adOpenKeyset rs.LockType = adLockOptimistic Dim strYlposqsMax_No, strYlposqsmxMax_No As String rs.Open " select max(ylposqs_No) as ylposqs_No from ylposqs where qsdate='" & Trim(Label3.Caption) & "'" strYlposqsMax_No = IIf(IsNull(rs("ylposqs_No")), "0", Right(rs("ylposqs_No"), 2)) strYlposqsMax_No = Right(Trim(Label3.Caption), 8) & Format(Val(strYlposqsMax_No) + 1, "00") rs.Close Dim strSql As String strSql = " select * from ylposqs where qsdate='" & Trim(Label3.Caption) & "'" rs.Open strSql If rs.EOF Then rs.AddNew rs("yl