Hello,
I want to use the following code taken from this website (which is for SQL Server) for my access database which have access backend. How can I do so? What change do I have to make in the code. The code is to save an image file as BLOB and then retrieve the image from BLOB. Code is as below.
TIA
Best Regards,
Abdullah
I want to use the following code taken from this website (which is for SQL Server) for my access database which have access backend. How can I do so? What change do I have to make in the code. The code is to save an image file as BLOB and then retrieve the image from BLOB. Code is as below.
TIA
Best Regards,
Abdullah
Rich (BB code):
'To save a file in a table as binary
Sub SaveAsBinary()
Dim adoStream As Object
Dim adoCmd As Object
Dim strFilePath As String
Dim adoCon As Object
Const strDB As String = "" 'Database name
Const strServerName As String = "" 'Server Name
Set adoCon = CreateObject( "ADODB.Connection")
Set adoStream = CreateObject( "ADODB.Stream")
Set adoCmd = CreateObject( "ADODB.Command")
'--Open Connection to SQL server
adoCon.CursorLocation = adUseClient
adoCon.Open "Provider=SQLOLEDB;Data Source=" & strServerName & ";Initial Catalog = " & strDB & ";Integrated Security=SSPI;"
'----
strFilePath = "C:\1.JPG" ' File to upload
adoStream.Type = adTypeBinary
adoStream.Open
adoStream.LoadFromFile strFilePath 'It fails if file is open
With adoCmd
.CommandText = "INSERT INTO Employee VALUES (?,?)" ' Query
.CommandType = adCmdText
'---adding parameters
.Parameters.Append .CreateParameter( "@Id",adInteger,adParamInput,0,1)
.Parameters.Append .CreateParameter( "@Image",adVarBinary,adParamInput,adoStream.Size,adoStream.Read)
'---
End With
adoCmd.ActiveConnection = adoCon
adoCmd.Execute
adoCon.Close
End Sub
Rich (BB code):
'To read binary stream from DB and save the same on system drive
Sub ReadBinary()
Dim adoRs As Object
Dim adoStream As Object
Dim adoCon As Object
Const strDB As String = "" 'Database name
Const strServerName As String = "" 'Server Name
Set adoCon = CreateObject( "ADODB.Connection")
Set adoRs = CreateObject( "ADODB.Recordset")
Set adoStream = CreateObject( "ADODB.Stream")
'--Open Connection to SQL server
adoCon.CursorLocation = adUseClient
adoCon.Open "Provider=SQLOLEDB;Data Source=" & strServerName & ";Initial Catalog = " & strDB & ";Integrated Security=SSPI;"
'--
adoRs.Open "SELECT ID,Image FROM Employee WHERE ID = 1",adoCon,adOpenStatic,adLockOptimistic
adoStream.Type = adTypeBinary
adoStream.Open
adoStream.Write adoRs.Fields( "Image").Value ' FieldName that contains binary data
adoStream.SaveToFile "C:\" & adoRs.Fields( "ID").Value & ".jpg",adSaveCreateOverWrite
adoRs.Close
adoCon.Close
End Sub