Solved Save Screenshot to Table

[Spalle]

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

 

[June7]

I think need to save as a file that can then be referenced by name. Review https://stackoverflow.com/questions...ay-to-take-a-screenshot-in-ms-access-with-vba

 

[Spalle]

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.

 

[June7]

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]

So does that file still exist?

 

[arnelgp]

see post #5 on MS ACCESS Form 64bit version to save as Image | Access World Forums (access-programmers.co.uk)

 

[Spalle]

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]

this is what ChatGPT suggests (note you test it, i don't have msssql):

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]

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]

type with ChatGPT or CoPilot, i am sure you have either.

 

[arnelgp]

here is what i got (and modified) from ChatGPT:

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]

Okey i got it working, thank you all very much

 

[Gasman]

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]

Yes of course

Credit goes primarily to arnelgp