|
楼主 |
发表于 2011-7-23 17:21
|
显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
'将二进制数据从记录中取出
'函数名:RecodeToFile
'参数: P_Cnn ADODB连接,TabName 源数据表,FldName 源字段名, WhereStr 取字段条件,FileType 生成临时文件的类型
'返回值:'一个临时文件名
'例: GetTmpFile=RecodeToFile(P_Conn,"achgoods","achphoto","where gdsid='001',"bmp")
Public Function RecodeToFile(ByRef P_Cnn As ADODB.Connection, _
TabName As String, _
FldName As String, _
WhereStr As String, _
Optional FileType As String = "Bmp") As String
Dim Rs As New ADODB.Recordset
Dim StrSql As String
Dim Bytes() As Byte
Dim File_Name As String
Dim File_Num As Integer
Dim File_Length As Long
Dim Num_Blocks As Long
Dim Left_Over As Long
Dim Block_Num As Long
Dim WorkPath As String
Dim TmpDir As New SmSysCls
Err.Clear
On Error Resume Next
WorkPath = TmpDir.GetFolder(SmWinTempDirectory)
If Dir$(WorkPath, vbDirectory) = "" Then WorkPath = App.Path
If Right$(WorkPath, 1) <> "/" Then WorkPath = WorkPath & "/"
If Left$(Trim$(UCase$(WhereStr)), Len("where")) <> UCase$("where") Then WhereStr = "Where " & Trim$(WhereStr)
StrSql = "Select Top 1 [" & TabName & "].[" & FldName & "] From [" & TabName & "] " & WhereStr
Set Rs = RsOpen(P_Cnn, StrSql)
If Rs.BOF And Rs.EOF Then Exit Function
If P_Cnn.State <> 1 Then P_Cnn.Open
If Not IsNull(Rs.Fields(FldName)) Then
File_Name = WorkPath & "TmpFile." & FileType
If Len(Dir(File_Name)) <> 0 Then Kill File_Name
File_Num = FreeFile
Open File_Name For Binary As #File_Num
File_Length = CT.ToLng(Rs.Fields(FldName).ActualSize) '取字段的实际大小
'/不分块读写
'/ If File_Length > 0 Then
'/ Bytes() = Rs.Fields(FldName).GetChunk(File_Length)
'/ Put #File_Num, , Bytes()
'/ Else
'/ Err = -1
'/ End If
'/分块读写
Num_Blocks = File_Length / Block_Size
Left_Over = File_Length Mod Block_Size
For Block_Num = 1 To Num_Blocks
Bytes() = Rs.Fields(FldName).GetChunk(Block_Size)
Put #File_Num, , Bytes()
Next
If Left_Over > 0 Then
Bytes() = Rs.Fields(FldName).GetChunk(Left_Over)
Put #File_Num, , Bytes()
End If
Erase Bytes
Close #File_Num
If Rs.State = adStateOpen Then
Rs.Close
Set Rs = Nothing
End If
Erase Bytes
End If
RecodeToFile = IIf(Err.Number = 0, File_Name, "")
Set TmpDir = Nothing
Err.Clear
End Function
'
'对组合框赋值(直接从数据库取值,如果有多个值,则只取第一个值.)
'函数名:SetFrmCtrlValue
'参数: P_Cnn ADODB连接,StrSql 取值SQL语句,CtrFiedList 动态参数列表
'返回值:
'例: CALL SetGroupVal(P_Cnn,"Select AchGds.* From AchGds Where GdsID='001'",TxtWNGdsID,"GdsID",TxtWNGdsName,"GdsName")
'*注:动态参数列表(CtrFiedList)的奇数位是 目标名,偶数位 是对应字段名.
Public Function SetGroupVal(ByRef P_Cnn As ADODB.Connection, _
StrSql As String, _
ParamArray CtrFiedList() As Variant) As Boolean
Dim Rs As New ADODB.Recordset
Dim N As Long
Dim id As Long
Dim ConFiedArr() As SmPutGroup
Dim ReturnVal As Boolean
Err.Clear
On Error Resume Next
ReturnVal = False
If P_Cnn.State <> 1 Then P_Cnn.Open
Set Rs = RsOpen(P_Cnn, StrSql)
If Not (Rs.EOF And Rs.BOF) Then
Rs.MoveFirst
id = 0
'/分解控件与字段名
For N = 0 To UBound(CtrFiedList, 1)
If N Mod 2 = 0 Then
id = id + 1
ReDim Preserve ConFiedArr(id - 1)
'/控件
Set ConFiedArr(id - 1).FrmControl = CtrFiedList(N)
Else
'/字段名
ConFiedArr(id - 1).FldName = CtrFiedList(N)
End If
Next
'/对控件赋值
For N = 0 To UBound(ConFiedArr, 1)
ConFiedArr(N).FrmControl = CStr("" & (Rs.Fields(ConFiedArr(N).FldName)))
Next
ReturnVal = True
Else
ReturnVal = False
End If
SetGroupVal = ReturnVal
If Rs.State = adStateOpen Then
Rs.Close
Set Rs = Nothing
End If
Err.Clear
End Function
'
'返回单个数据字段值.
'函数名:SetFrmCtrlValue
'参数: P_Cnn ADODB连接,DbTabName 源数据表名,FldName 源数据字段名,WhereStr 取值的条件语句
'返回值:相对应的字段值
'例: GdsNameVal=GetOneValue(P_CNN,"ACHGOODS","GDSNAME","WHERE GDSID='001'")
Public Function GetOneValue(ByRef P_Cnn As ADODB.Connection, _
DbTabname As String, _
FldName As String, _
WhereStr As String) As String
Dim StrSql As String
Dim Rs As New ADODB.Recordset
Err.Clear
On Error Resume Next
If P_Cnn.State <> 1 Then P_Cnn.Open
If Left$(Trim$(UCase$(WhereStr)), Len("where")) <> UCase$("where") Then WhereStr = "Where " & Trim$(WhereStr)
StrSql = "Select Top 1 [" & DbTabname & "].[" & FldName & "] From [" & DbTabname & "] " & WhereStr
Set Rs = RsOpen(P_Cnn, StrSql)
If Not (Rs.EOF And Rs.BOF) Then
Rs.MoveFirst
GetOneValue = CT.ToStr(Rs.Fields(FldName))
Else
GetOneValue = ""
End If
If Rs.State = adStateOpen Then
Rs.Close
Set Rs = Nothing
End If
Err.Clear
End Function
'
'删除记录
'函数:KillRecode
'参数:FldName 字段名,FldVal 字段值,TabName 表名
'
Function KillRecode(ByRef P_Cnn As ADODB.Connection, _
TabName As String, _
FldName As String, _
FldVal As String)
Dim StrSql As String
If P_Cnn.State <> 1 Then P_Cnn.Open
StrSql = "Delete " & TabName & " From " & TabName & " Where " & FldName & "='" & FldVal & "'"
P_Cnn.Execute StrSql
Err.Clear
End Function
'
'取最大单号
'前二位.单据类型.+四位年+二位月+二位日+4位单据流水号
'函数:GetMaxBillID
'参数:FldName 字段名(BillID),BillStyle 单据类型,TabName 表名
'返回值:可用最大单号
Function GetMaxBillID(ByRef P_Cnn As ADODB.Connection, _
TabName As String, _
FldName As String, _
BillStyle As String) As String
Dim BillSD As String
Dim StrSql As String
Dim Rs As New ADODB.Recordset
Dim BillNo As Long
Dim NewBillID As Long
Dim lLen As Long
Dim ReturnVal As String
Dim RNum As Long
Dim RLen As Long
Dim FmtStr As String
Dim N As Long
Err.Clear
On Error Resume Next
If P_Cnn.State <> 1 Then P_Cnn.Open
BillSD = BillStyle & Format$(Date, "YYYYMMDD")
'/--------------------------------------------------
lLen = Len(BillSD): RLen = 4 '单据流水号位数
'/--------------------------------------------------
For N = 1 To RLen
FmtStr = FmtStr & "0"
Next
'/--------------------------------------------------
StrSql = "Select (Max(" & FldName & ")) AS MaxID From " & TabName & " Where LEFT(" & FldName & "," & lLen & ")='" & BillSD & "'"
Set Rs = RsOpen(P_Cnn, StrSql)
If Not (Rs.EOF And Rs.BOF) Then
If Len(CT.ToStr(Rs.Fields("MaxID"))) > 0 Then
RNum = Right$(CT.ToStr(Rs.Fields("MaxID")), RLen)
Else
RNum = 0
End If
NewBillID = CT.ToLng(RNum) + 1
Else
NewBillID = 1
End If
If Rs.State = adStateOpen Then
Rs.Close
Set Rs = Nothing
End If
ReturnVal = BillSD & "-" & Format$(NewBillID, FmtStr)
GetMaxBillID = IIf(Err.Number = 0, ReturnVal, "")
Err.Clear
End Function
'
'压缩MDB数据库
'函数名:ZipMdb
'参数:P_Cnn ADODB连接,SourFileName 源文件名,ObjFileName 目标文件名,
' Provider Provider参数(视JET版而定,默认是4.0),UserID 用户名,
' UserPwd 密码
'返回值:TRUE 成功,FALSE 失败.
Public Function ZipMdb(P_Cnn As ADODB.Connection, _
MdbFileName As String, _
Optional Provider As String = "Microsoft.Jet.OLEDB.4.0", _
Optional UserID As String = "admin", _
Optional UserPwd As String = "") As Boolean
Dim Yjro As New JRO.JetEngine
Dim WorkPath As String
Dim TmpName As String
Dim FileCon As SmFileCls
Err.Clear
On Error Resume Next
'/关闭连接
P_Cnn.Close: Set P_Cnn = Nothing
DoEvents
WorkPath = FileCon.FilePath(MdbFileName)
TmpName = WorkPath & "mdbTmp.bak"
'/-------------------------------
DoEvents
'/压缩
Yjro.CompactDatabase "Provider=" & Provider & ";Data Source=" & MdbFileName & ";" & _
"Jet OLEDB:Database Password=" & UserPwd & ";" & _
"User ID=" & UserID & ";", _
"Provider=" & Provider & ";Data Source=" & TmpName & ";" & _
"Jet OLEDB:Database Password=" & UserPwd & ";" & _
"User ID=" & UserID & ";"
DoEvents
'/删除旧文件,将压缩后的文件COPY到旧位置
If FileCon.FileCheck(MdbFileName) And FileCon.FileCheck(TmpName) Then
Kill MdbFileName
DoEvents
Call FileCopy(TmpName, MdbFileName)
DoEvents
Kill TmpName
DoEvents
'/重新连接
Call CreateMdbConn(P_Cnn, MdbFileName, , UserID, UserPwd)
Else
Err.Number = -1
End If
Set Yjro = Nothing
Set FileCon = Nothing
Err.Clear
ZipMdb = (Err.Number = 0)
Err.Clear
End Function
'
'恢复和备份MDB数据库
'函数名:BakResumeMdb
'参数:P_Cnn ADODB连接,SourFileName 源文件名,ObjFileName 目标文件名,
' Provider Provider参数(视JET版而定,默认是4.0),UserID 用户名,
' UserPwd 密码,WorkType 操作类型(0 备份,1 恢复)
'返回值:TRUE 成功,FALSE 失败.
'注:当WorkType=0时,源文件名是要备份文件,目标文件名是备份文件.
' 当WorkType=1时,源文件名是备份文件,目标文件名要恢复的文件.
Public Function BakResumeMDB(P_Cnn As ADODB.Connection, _
SourFileName As String, _
ObjFileName As String, _
Optional Provider As String = "Microsoft.Jet.OLEDB.4.0", _
Optional UserID As String = "admin", _
Optional UserPwd As String = "", _
Optional WorkType As Long = 0) As Boolean
Dim Yjro As New JRO.JetEngine
Dim WorkPath As String
Dim FileCon As New SmFileCls
Err.Clear
On Error Resume Next
'/关闭连接
P_Cnn.Close: Set P_Cnn = Nothing
DoEvents
'/-------------------------------
'/压缩
Yjro.CompactDatabase "Provider=" & Provider & SourFileName & ";" & _
"Jet OLEDB:Database Password=" & UserPwd & ";" & _
"User ID=" & UserID & ";", _
"Provider=" & Provider & ";Data Source=" & ObjFileName & ";" & _
"Jet OLEDB:Database Password=" & UserPwd & ";" & _
"User ID=" & UserID & ";"
DoEvents
'/删除旧文件,将压缩后的文件COPY到旧位置
If Not (FileCon.FileCheck(SourFileName) And FileCon.FileCheck(ObjFileName)) Then
If WorkType = 0 Then
'/备份。
Call CreateMdbConn(P_Cnn, SourFileName, , UserID, UserPwd)
Else
'/恢复
Call CreateMdbConn(P_Cnn, ObjFileName, , UserID, UserPwd)
End If
Err.Number = -1
End If
Set FileCon = Nothing
Set Yjro = Nothing: Err.Clear
BakResumeMDB = (Err.Number = 0)
Err.Clear
End Function
'
'解读身份证信息
'函数名:GetIDCard
'参数:P_Cnn ADODB连接,IDCode 身份证编号,RevCodeInfo EmpCodeInfo(用于返回),
'返回值:无
Public Function GetIDCard(ByRef P_Cnn As ADODB.Connection, IDCode As String, ByRef RevCodeInfo As EmpCodeInfo)
Dim Rs As New ADODB.Recordset
Dim StrSql As String
Dim I As Long
Dim TAdd(6) As String
Dim AddStr(6) As String
Dim UserAdd As String
Dim BirthStr As String
Dim SexStr As String
Err.Clear
On Error Resume Next
AddStr(0) = Left$(IDCode, 2) & "0000" '省
AddStr(1) = Left$(IDCode, 4) & "00" '市
AddStr(2) = Left$(IDCode, 6) '县及县级市
UserAdd = ""
If P_Cnn.State <> 1 Then P_Cnn.Open
'取籍贯
For I = 0 To UBound(AddStr)
If Len(AddStr(I)) > 0 Then
StrSql = "SELECT * FROM [Reglism] Where Code='" & AddStr(I) & "'"
Set Rs = Nothing
Rs.Open StrSql, P_Cnn, adOpenStatic, adLockReadOnly
If Not (Rs.EOF And Rs.BOF) Then
TAdd(I) = "" & Rs.Fields("Name")
UserAdd = UserAdd & Rs.Fields("Name")
End If
End If
Next
RevCodeInfo.NativePlace = UserAdd
'取电话区号
For I = UBound(TAdd) To 0 Step -1
If Len(TAdd(I)) > 1 Then
TAdd(I) = Left$(TAdd(I), 2)
StrSql = "SELECT * FROM [PhoCode] WHERE [Name] like '" & TAdd(I) & "%'"
Set Rs = Nothing
Rs.Open StrSql, P_Cnn, adOpenStatic, adLockReadOnly
If Not (Rs.EOF And Rs.BOF) Then
Rs.MoveFirst
RevCodeInfo.PhoCode = Format$(Rs.Fields("Code"), "0000")
Exit For
End If
End If
Next
'取邮政编码
For I = UBound(TAdd) To 0 Step -1
If Len(TAdd(I)) > 1 Then
TAdd(I) = Left$(TAdd(I), 2)
StrSql = "SELECT * FROM [MailCode] WHERE [Name] Like '" & TAdd(I) & "%'"
Set Rs = Nothing
Rs.Open StrSql, P_Cnn, adOpenStatic, adLockReadOnly
If Not (Rs.EOF And Rs.BOF) Then
Rs.MoveFirst
RevCodeInfo.MailCode = Format$(Rs.Fields("Code"), "0000")
Exit For
End If
End If
Next
'生日/性别
If Len(IDCode) = 15 Then '旧身份证号码.
BirthStr = Mid$(IDCode, 7, Len(IDCode) - 6 - 3) '出生日期
BirthStr = "19" & BirthStr
SexStr = CLng(Right$(IDCode, 1)) Mod 2 '顺序码奇数是男.偶数是女
Else '新身份证号码.
BirthStr = Mid$(IDCode, 7, Len(IDCode) - 6 - 4) '出生日期
SexStr = CLng(Mid$(IDCode, Len(IDCode) - 3, 3)) Mod 2 '顺序码奇数是男.偶数是女
End If
BirthStr = Left$(BirthStr, 4) & "/" & Mid$(BirthStr, 5, 2) & "/" & Right$(BirthStr, 2)
RevCodeInfo.Birthday = BirthStr
RevCodeInfo.Sex = SexStr
If Rs.State = adStateOpen Then
Rs.Close
Set Rs = Nothing
End If
Err.Clear
End Function
Private Sub Class_Initialize()
Dim T As New ClsRev
Set CT = New SmDataDiap
Call T.GetIniVal
Set T = Nothing
End Sub
Private Sub Class_Terminate()
On Error Resume Next
Set CT = Nothing
End Sub |
|