【数据库备份功能】【数据排序处理】【随机跳转记录】
数据库备份功能
Private Sub Command备份_Click()
'----------------------------------选择备份到的位置
Dim folderpath As String
Dim dlgOpen As FileDialog
Set dlgOpen = Application.FileDialog(msoFileDialogFolderPicker)
With dlgOpen
.AllowMultiSelect = False
If .Show = -1 Then
folderpath = .SelectedItems(1)
Else
Exit Sub
End If
End With
'----------------------------------备份
Dim dbpathname As String
dbpathname = CurrentProject.FullName
Dim bakname As String
bakname = "bak_" & Format(Now, "yyyymmddhhnnss") & CurrentProject.Name
Dim fs As Object
Dim objfile As Object
Set fs = CreateObject("Scripting.FileSystemObject")
fs.copyfile dbpathname, folderpath & "\" & bakname
MsgBox "备份完成"
End Sub
数据排序处理
Function 排序去重(ByVal datatext As String, ByVal splittext As String, ByVal sortype As String, ByVal typetext As String) As String
Dim data_array
data_array = Split(datatext, splittext)
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 拆分数据表"
DoCmd.RunSQL del_sql
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("拆分数据表", dbOpenTable)
Dim i
With add_rs
For i = 0 To UBound(data_array)
.AddNew
!数据名称.Value = data_array(i)
.Update
Next i
.Close
End With
Set add_rs = Nothing
'生成处理结果
Dim data_text As String
Dim search_rs As DAO.Recordset
Dim search_sql As String
If typetext = "排序去重" Then
If sortype = "升序" Then
search_sql = "Select * From 筛选不重复数据查询" & " order by 数据名称 ASC"
Else
search_sql = "Select * From 筛选不重复数据查询" & " order by 数据名称 DESC"
End If
End If
If typetext = "排序" Then
If sortype = "升序" Then
search_sql = "Select * From 拆分数据表" & " order by 数据名称 ASC"
Else
search_sql = "Select * From 拆分数据表" & " order by 数据名称 DESC"
End If
End If
Set search_rs = CurrentDb.OpenRecordset(search_sql, dbOpenDynaset)
Do While search_rs.EOF = False
If data_text <> "" Then
data_text = data_text & splittext & search_rs!数据名称.Value
Else
data_text = search_rs!数据名称.Value
End If
search_rs.MoveNext
Loop
search_rs.Close
Set search_rs = Nothing
排序去重 = data_text
End Function
Private Sub Command降序_Click()
If Me.原数据 <> "" Then
If Me.分隔符 <> "" Then
Me.处理结果 = 排序去重(Me.原数据, Me.分隔符, "降序", "排序")
Else
MsgBox "分隔符不能为空"
End If
Else
Me.处理结果 = ""
End If
End Sub
Private Sub Command降序去重_Click()
If Me.原数据 <> "" Then
If Me.分隔符 <> "" Then
Me.处理结果 = 排序去重(Me.原数据, Me.分隔符, "降序", "排序去重")
Else
MsgBox "分隔符不能为空"
End If
Else
Me.处理结果 = ""
End If
End Sub
Private Sub Command升序_Click()
If Me.原数据 <> "" Then
If Me.分隔符 <> "" Then
Me.处理结果 = 排序去重(Me.原数据, Me.分隔符, "升序", "排序")
Else
MsgBox "分隔符不能为空"
End If
Else
Me.处理结果 = ""
End If
End Sub
Private Sub Command升序去重_Click()
If Me.原数据 <> "" Then
If Me.分隔符 <> "" Then
Me.处理结果 = 排序去重(Me.原数据, Me.分隔符, "升序", "排序去重")
Else
MsgBox "分隔符不能为空"
End If
Else
Me.处理结果 = ""
End If
End Sub
随机跳转记录
Dim rs1 As DAO.Recordset '定义记录集变量
Private Sub Command随机_Click()
Dim Record_count As Long
Record_count = rs1.RecordCount
Dim rnd_i As Long
'--------------------------------------------------------
Randomize
rnd_i = Int((Record_count - 1 + 1) * Rnd + 1)
rs1.MoveFirst
rs1.Move (rnd_i - 1)
Me.单词 = rs1.Fields("单词").Value
Me.词义 = rs1.Fields("词义").Value
End Sub
Private Sub Form_Load()
Set rs1 = CurrentDb.OpenRecordset("英语单词表", dbOpenTable)
End Sub
