Solved Save Screenshot to Table (1 Viewer)

Spalle

New member
Local time
Today, 10:06
Joined
Dec 29, 2023
Messages
22
Hello, i want to save a Screenshot to an OLE Field (varbinary on the server).
I have come this far:

Private Declare PtrSafe Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

Private Const KEYEVENTF_KEYUP = &H2
Private Const VK_SNAPSHOT = &H2C
Private Const VK_MENU = &H12

Sub PrintScreen()

keybd_event VK_MENU, 0, 0, 0
keybd_event VK_SNAPSHOT, 0, 0, 0
keybd_event VK_SNAPSHOT, 0, KEYEVENTF_KEYUP, 0
keybd_event VK_MENU, 0, KEYEVENTF_KEYUP, 0

End Sub

This takes a Screenshot and saves it to the clipboard but from there on i am stuck.

The Purpose is to take Screenshots when an Error occurs on another Frontend so i can see what exectly happens there.

Thank you in Advance :)
 

Spalle

New member
Local time
Today, 10:06
Joined
Dec 29, 2023
Messages
22
The code is no longer functioning properly, at least not for me.

The initial issue arose when the screenshot tool was triggered to open unexpectedly. I managed to address this by incorporating segments from my previous code.

However, the second problem persists in the form of an error.
 

Attachments

  • Bild_2024-04-03_002230694.png
    Bild_2024-04-03_002230694.png
    49.5 KB · Views: 22

June7

AWF VIP
Local time
Today, 00:06
Joined
Mar 9, 2014
Messages
5,468
It's been a while since I first found this code and tested it. So tested again. First time I got "out of memory" error. Second time created an image that is only a small area of screen. Third time image is entire screen. Fourth and subsequent times image is only the VBA editor window which is where I ran code from.

I got nothing else to offer.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:06
Joined
Sep 21, 2011
Messages
14,253
So does that file still exist?
 

Spalle

New member
Local time
Today, 10:06
Joined
Dec 29, 2023
Messages
22
Yes, this saves the screenshot. That was the initial step.

Now, I need to save this screenshot into a table. How can I accomplish that?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:06
Joined
May 7, 2009
Messages
19,230
this is what ChatGPT suggests (note you test it, i don't have msssql):
Code:
Sub SaveImageToDatabase(ByVal ImagePath As String)
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    
    ' Establish connection to SQL Server
    conn.ConnectionString = "Provider=SQLOLEDB;Data Source=YourServerName;Initial Catalog=YourDatabaseName;User ID=YourUserID;Password=YourPassword;"
    conn.Open
    

    Dim imageStream As Object
    Dim imageData As Variant
    Dim sql As String
    
    ' Path to the image file
    ImagePath = "C:\path\to\your\image.jpg"
    
    ' Read the image file as binary data
    Set imageStream = CreateObject("ADODB.Stream")
    imageStream.Type = 1 ' adTypeBinary
    imageStream.Open
    imageStream.LoadFromFile ImagePath
    imageData = imageStream.Read
    
    ' Close the stream
    imageStream.Close
    
    ' SQL statement to insert binary data into the database
    sql = "INSERT INTO YourTableName (ImageColumn) VALUES (?)"
    
    Dim cmd As Object
    Set cmd = CreateObject("ADODB.Command")
    With cmd
        .ActiveConnection = conn
        .CommandText = sql
        .CommandType = 1 ' adCmdText
        .Parameters.Append .CreateParameter("Image", 205, 1, LenB(imageData), imageData) ' 205 is adVarBinary
        .Execute
    End With
    
    ' Close the connection
    conn.Close
    
    MsgBox "Image saved to database successfully."
End Sub
 

Spalle

New member
Local time
Today, 10:06
Joined
Dec 29, 2023
Messages
22
It doesn't have to be this complicated! I have a linked table in the database where the field is an OLE field; there's no need for a server connection here. Do you know of a simpler approach?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:06
Joined
May 7, 2009
Messages
19,230
type with ChatGPT or CoPilot, i am sure you have either.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:06
Joined
May 7, 2009
Messages
19,230
here is what i got (and modified) from ChatGPT:

Code:
Sub SaveImageToLinkedTable(ByVal filePath As String, Optional ByVal PK_ID As Long = 0)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim imageStream As Object
    Dim imageData As Variant
   
   
    ' Read the image file as binary data
    Set imageStream = CreateObject("ADODB.Stream")
    imageStream.Type = 1 ' adTypeBinary
    imageStream.Open
    imageStream.LoadFromFile filePath
    imageData = imageStream.Read
   
    ' Close the stream
    imageStream.Close
   
    ' Open the linked table in the current database
    Set db = CurrentDb
    Set rs = db.OpenRecordset("yourLinkedSQLTableHere", dbOpenDynaset, dbSeeChanges)
   
    If PK_ID = 0 Then
        ' Move to a new record
        rs.AddNew
   
    Else
        rs.FindFirst "PKFieldHere = " & PK_ID
        rs.Edit
    End If
    ' Set the field value to the binary data
    rs("yourVarbinaryFieldNameHere").AppendChunk imageData
   
    ' Save the record
    rs.Update
   
    ' Close the recordset and database
    rs.Close
    Set rs = Nothing
    Set db = Nothing
   
    'MsgBox "Image saved to linked table successfully."
End Sub
 

Spalle

New member
Local time
Today, 10:06
Joined
Dec 29, 2023
Messages
22
Okey i got it working, thank you all very much :)
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:06
Joined
Sep 21, 2011
Messages
14,253
Okey i got it working, thank you all very much :)
So please post your solution. It might help others. After all that is what this site is for after all?
 

Spalle

New member
Local time
Today, 10:06
Joined
Dec 29, 2023
Messages
22
Yes of course :)

Credit goes primarily to arnelgp
 

Attachments

  • Screenshot Upload and Download.accdb
    512 KB · Views: 19

Users who are viewing this thread

Top Bottom