Public Enum DataBaseType ACCESS97 = 0 SQLSVR70 = 1 ACCESS2K = 2 SQLSVR2K = 3 End Enum Public Type iMyval Server As String UID As String PWD As String Con As New ADODB.Connection Com As New ADODB.Command Par As New ADODB.Parameter rs As New ADODB.Recordset ConType As Integer DBType As DataBaseType DBName As String DBPath As String DBFile As String DBPWD As String APPNAME As String SysPath As String OpenOK As Boolean WildChar As String End Type Public MyVAl As iMyval Public IsFirSt As Long Public Sub EndApp() On Error Resume Next MyVAl.rs.Close MyVAl.Con.Close Set MyVAl.rs = Nothing Set MyVAl.Con = Nothing End End Sub Public Sub Main() App.Title = "照片转换" ChDir App.Path If App.PrevInstance Then MsgBox "程序已经启动!", vbExclamation, App.Title Exit Sub End If MyVAl.APPNAME = App.Title MyVAl.DBType = ACCESS97 MyVAl.DBName = "DB2" MyVAl.DBFile = "DB2.mdb" MyVAl.Server = "" MyVAl.UID = "ADMIN" MyVAl.PWD = "" MyVAl.DBPWD = "" MyVAl.WildChar = "%" frmUpdatePhoto.Show Exit Sub ERR_SQL: ProcSQLError Screen.MousePointer = 0 End Sub Public Function OpenDB() As Boolean OpenDB = False
On Error Resume Next Kill MyVAl.DBPath & "\" & MyVAl.DBName & ".ldb" On Error GoTo Error_Event Set MyVAl.Con = New ADODB.Connection MyVAl.Con.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data Source=" & MyVAl.DBFile & ";user ID=" & MyVAl.UID & ";Password=" & MyVAl.PWD & ";Jet OLEDB:Database Password=" & MyVAl.DBPWD MyVAl.Con.CommandTimeout = 60 MyVAl.Con.Open OpenDB = True Screen.MousePointer = 0 Exit Function Error_Event: Screen.MousePointer = 0 MsgBox GetOdbcErrors & vbCrLf & Err.Description, vbCritical End Function Public Function OpenRS(sqlstr As String, Optional ReadOnly As Boolean = False, Optional Con As ADODB.Connection = Nothing) As ADODB.Recordset On Error GoTo openrsErrorhandle If Con Is Nothing Then Set Con = MyVAl.Con
Set OpenRS = New ADODB.Recordset OpenRS.CursorLocation = adUseClient If ReadOnly Then OpenRS.Open sqlstr, Con, adOpenDynamic, adLockReadOnly Else OpenRS.Open sqlstr, Con, adOpenDynamic, adLockOptimistic End If Exit Function openrsErrorhandle: MsgBox " 致命错误:" & GetOdbcErrors() & Chr(13) & " 来自SQL命令:" & sqlstr & Chr(13) & " 无法继续执行", vbOKOnly + vbCritical, "致命错误" End End Function Public Function ExecRs(sqlstr As String, Optional Con As ADODB.Connection = Nothing) If Con Is Nothing Then Set Con = MyVAl.Con End If ExecRs = Con.Execute(sqlstr) Exit Function dbexecuteErrorhandle: MsgBox " 致命错误:" & GetOdbcErrors() & Chr(13) & " 来自SQL命令:" & sqlstr & Chr(13) & " 无法继续执行", vbOKOnly + vbCritical, "致命错误" End End Function Public Function GetOdbcErrors() Dim a As String Dim i As Long a = "" For i = 0 To MyVAl.Con.Errors.Count - 1 a = a & IIf(a = "", "", Chr(13)) & MyVAl.Con.Errors(i).Description Next i GetOdbcErrors = a End Function Function ProcSQLError() As Boolean Screen.MousePointer = 0 MsgBox "发生以下错误:" & " 与数据库连接失败。" & vbCrLf & GetOdbcErrors, vbCritical + vbOKOnly + vbApplicationModal, "登录错误" End Function
Private Sub Command1_Click() Dim rs As New ADODB.Recordset Dim vPhoto As Variant Set rs = OpenRS("Select id,Picture From TABLE1 ", False) vPhoto = rs("Picture").GetChunk(rs.Fields("picture").ActualSize) rs("Picture").AppendChunk vPhoto rs.Update rs.Close End Sub
With Data1 .DatabaseName="C:\学生.mdb" .RecordSource="Select Name, ID, Age From Student Where Age>=16" .Refesh End With明白了吗?
要把图片转化为二进制存进ACCESS里,例如: DataFile = 1 If FNAME <> "" Then Open FNAME For Binary Access Read As DataFile Fl = LOF(DataFile) If Fl = 0 Then Close DataFile Exit Sub End If block = Fl \ blocksize Fragment = Fl Mod blocksize ReDim Chunk(Fragment) Get DataFile, , Chunk() Adodc1.Recordset.Fields("照片").AppendChunk Chunk() ReDim Chunk(blocksize) For I = 1 To block Get DataFile, , Chunk() Adodc1.Recordset.Fields("照片").AppendChunk Chunk() Next I Close DataFile 其中 Public FNAME As String Public rs As Recordset Public DataFile As Integer Public Fl As Long Public block As Integer Public Const blocksize As Integer = 16384 Public Fragment As Integer Public Chunk() As Byte Public I As Integer
ACCESS97 = 0
SQLSVR70 = 1
ACCESS2K = 2
SQLSVR2K = 3
End Enum
Public Type iMyval
Server As String
UID As String
PWD As String
Con As New ADODB.Connection
Com As New ADODB.Command
Par As New ADODB.Parameter
rs As New ADODB.Recordset
ConType As Integer
DBType As DataBaseType
DBName As String
DBPath As String
DBFile As String
DBPWD As String
APPNAME As String
SysPath As String
OpenOK As Boolean
WildChar As String
End Type
Public MyVAl As iMyval
Public IsFirSt As Long
Public Sub EndApp()
On Error Resume Next
MyVAl.rs.Close
MyVAl.Con.Close
Set MyVAl.rs = Nothing
Set MyVAl.Con = Nothing
End
End Sub
Public Sub Main()
App.Title = "照片转换"
ChDir App.Path
If App.PrevInstance Then
MsgBox "程序已经启动!", vbExclamation, App.Title
Exit Sub
End If
MyVAl.APPNAME = App.Title
MyVAl.DBType = ACCESS97
MyVAl.DBName = "DB2"
MyVAl.DBFile = "DB2.mdb"
MyVAl.Server = ""
MyVAl.UID = "ADMIN"
MyVAl.PWD = ""
MyVAl.DBPWD = ""
MyVAl.WildChar = "%"
frmUpdatePhoto.Show
Exit Sub
ERR_SQL:
ProcSQLError
Screen.MousePointer = 0
End Sub
Public Function OpenDB() As Boolean
OpenDB = False
On Error Resume Next
Kill MyVAl.DBPath & "\" & MyVAl.DBName & ".ldb"
On Error GoTo Error_Event
Set MyVAl.Con = New ADODB.Connection
MyVAl.Con.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data Source=" & MyVAl.DBFile & ";user ID=" & MyVAl.UID & ";Password=" & MyVAl.PWD & ";Jet OLEDB:Database Password=" & MyVAl.DBPWD
MyVAl.Con.CommandTimeout = 60
MyVAl.Con.Open
OpenDB = True
Screen.MousePointer = 0
Exit Function
Error_Event:
Screen.MousePointer = 0
MsgBox GetOdbcErrors & vbCrLf & Err.Description, vbCritical
End Function
Public Function OpenRS(sqlstr As String, Optional ReadOnly As Boolean = False, Optional Con As ADODB.Connection = Nothing) As ADODB.Recordset
On Error GoTo openrsErrorhandle
If Con Is Nothing Then Set Con = MyVAl.Con
Set OpenRS = New ADODB.Recordset
OpenRS.CursorLocation = adUseClient
If ReadOnly Then
OpenRS.Open sqlstr, Con, adOpenDynamic, adLockReadOnly
Else
OpenRS.Open sqlstr, Con, adOpenDynamic, adLockOptimistic
End If
Exit Function
openrsErrorhandle:
MsgBox " 致命错误:" & GetOdbcErrors() & Chr(13) & " 来自SQL命令:" & sqlstr & Chr(13) & " 无法继续执行", vbOKOnly + vbCritical, "致命错误"
End
End Function
Public Function ExecRs(sqlstr As String, Optional Con As ADODB.Connection = Nothing)
If Con Is Nothing Then
Set Con = MyVAl.Con
End If
ExecRs = Con.Execute(sqlstr)
Exit Function
dbexecuteErrorhandle:
MsgBox " 致命错误:" & GetOdbcErrors() & Chr(13) & " 来自SQL命令:" & sqlstr & Chr(13) & " 无法继续执行", vbOKOnly + vbCritical, "致命错误"
End
End Function
Public Function GetOdbcErrors()
Dim a As String
Dim i As Long
a = ""
For i = 0 To MyVAl.Con.Errors.Count - 1
a = a & IIf(a = "", "", Chr(13)) & MyVAl.Con.Errors(i).Description
Next i
GetOdbcErrors = a
End Function
Function ProcSQLError() As Boolean
Screen.MousePointer = 0
MsgBox "发生以下错误:" & " 与数据库连接失败。" & vbCrLf & GetOdbcErrors, vbCritical + vbOKOnly + vbApplicationModal, "登录错误"
End Function
Dim rs As New ADODB.Recordset
Dim vPhoto As Variant
Set rs = OpenRS("Select id,Picture From TABLE1 ", False)
vPhoto = rs("Picture").GetChunk(rs.Fields("picture").ActualSize)
rs("Picture").AppendChunk vPhoto
rs.Update
rs.Close
End Sub
.DatabaseName="C:\学生.mdb"
.RecordSource="Select Name, ID, Age From Student Where Age>=16"
.Refesh
End With明白了吗?
DataFile = 1
If FNAME <> "" Then
Open FNAME For Binary Access Read As DataFile
Fl = LOF(DataFile)
If Fl = 0 Then
Close DataFile
Exit Sub
End If
block = Fl \ blocksize
Fragment = Fl Mod blocksize
ReDim Chunk(Fragment)
Get DataFile, , Chunk()
Adodc1.Recordset.Fields("照片").AppendChunk Chunk()
ReDim Chunk(blocksize)
For I = 1 To block
Get DataFile, , Chunk()
Adodc1.Recordset.Fields("照片").AppendChunk Chunk()
Next I
Close DataFile
其中
Public FNAME As String
Public rs As Recordset
Public DataFile As Integer
Public Fl As Long
Public block As Integer
Public Const blocksize As Integer = 16384
Public Fragment As Integer
Public Chunk() As Byte
Public I As Integer
http://www.dapha.net/down/list.asp?id=1826