Access - Pasting rows without table

Question

I have a form where I need the user to be able to enter data in three columns, this data will be between 10 and 200 rows.

I then want to iterate this data to be able to convert it into an XML document to be passed into a remote SQL server instance.

This data will be pasted from several sources, mainly Excel documents so ideally I want to use method that allows pasting natively like a datasheet.

I wanted to avoid using a temp table to store this data as this will be used by multiple users and I might run into concurrency problems.

If I add a datasheet that is Unbound as a subform it will only allow a single row of data.

Is there any way to either bind a datasheet subform to a variable or to use another control that will allow pasting that can be unbound?

I've deleted my previous answer and posted a new one per your request, also provided a solution that can handle tabs and pretty much anything a spreadsheet can throw at you. — Erik A, Nov 05 '18 at 19:52

score 1 · Answer 1 · answered Nov 05 '18 at 13:57

1

Your database should be split into frontend and backend. Every user should have their own frontend.

Then you can simply use a temp table in the frontend, and all this becomes trivial.

On opening the form you do DELETE * FROM myTempTable.

answered Nov 05 '18 at 13:57

Andre

26,751
7
36
80

My database is linked to azure sql server but is shared over a network, having separate copies per user inst really feasible. – bendataclear Nov 05 '18 at 14:03
It doesn't really matter what the backend is, a shared frontend is a recipe for disaster and problems. You just need a method/script to distribute the frontend to the users, initially and when there is a new version. See e.g. https://stackoverflow.com/questions/50948829/how-to-manage-releases-with-ms-access and https://stackoverflow.com/questions/40893768/clarification-on-splitting-an-access-database – Andre Nov 05 '18 at 14:32
What is the issue with a shared frontend if all data sources are remote? – bendataclear Nov 05 '18 at 14:37
All the issues with sharing the front-end aren't applicable to my project, users don't have access to write or modify the front end file and trying to jerry-rig a system to keep the file up to date is a nightmare. – bendataclear Nov 05 '18 at 15:02
No, this is not an issue with the setup, it's a limitation of Access. – bendataclear Nov 05 '18 at 15:24
You wouldn't expect two or ten people to be able to edit a shared spreadsheet at the same time, or a word document, why do you think Access would behave any differently? – Minty Nov 05 '18 at 15:26
If it's read only then what are you doing pasting "new data" in? If you don't need to do that all this becomes a moot point. What you could do is give a user the ability to link to the excel sheet and do your manipulation ton the linked table, but again problematic on a shared FE. – Minty Nov 05 '18 at 15:47
@Minty, it's linked to Azure SQL server via linked tables, they need to insert new data into the form which is passed to a SQL stored procedure, the Access front end is ready only. – bendataclear Nov 05 '18 at 16:08

Erik A · Accepted Answer · 2018-11-05T19:56:41.573

I'm going to share two solutions:

First, the quick & dirty solution:

Create a multi-line textbox
Paste to that
Parse the result

Note that pasting text results in it being pasted as a TSV (tab-separated values) file. This can cause problems if you have values that can't be represented in the TSV format.

Second, the overcomplicated, WinAPI clipboard answer. I recommend the first answer for novice users since it's much easier to understand, the second is mainly useful for advanced users

First, we need declarations to a lot of clipboard functions, global memory handling functions, and string functions. My declarations are VBA6 incompatible, but should be 64-bit compatible:

'Global mem functions
Public Declare PtrSafe Function GlobalSize Lib "Kernel32" (ByVal hMem As LongPtr) As LongPtr
Public Declare PtrSafe Function GlobalLock Lib "Kernel32" (ByVal hMem As LongPtr) As LongPtr
Public Declare PtrSafe Function GlobalUnlock Lib "Kernel32" (ByVal hMem As LongPtr) As Boolean
'Clipboard functions
Public Declare PtrSafe Function GetClipboardData Lib "User32" (ByVal uFormat As Long) As LongPtr
Public Declare PtrSafe Function GetClipboardFormatNameW Lib "User32" (ByVal format As Long, ByVal lpszFormatName As LongPtr, ByVal cchMaxCount As Long) As Long
Public Declare PtrSafe Function OpenClipboard Lib "User32" (Optional ByVal hWndNewOwner As LongPtr) As Boolean
Public Declare PtrSafe Function CloseClipboard Lib "User32" () As Boolean
Public Declare PtrSafe Function EnumClipboardFormats Lib "User32" (ByVal format As Long) As Long
Public Declare PtrSafe Function CountClipboardFormats Lib "User32" () As Long
'String functions
Public Declare PtrSafe Function lstrcpyA Lib "Kernel32" (ByVal lpString1 As LongPtr, ByVal lpString2 As LongPtr) As LongPtr

Then, we're going to get the XML spreadsheet from the clipboard, in 2 steps:

Public Function GetClipboardSpreadsheetFormat() As Long
    'Requires clipboard to be open, doesn't close it, returns 0 if not exists
    Dim format As Long
    Dim b As String
    Dim l As Long
    For l = 1 To CountClipboardFormats
        b = String(100, vbNullChar) 'Initialize string buffer
        format = EnumClipboardFormats(format) 'Get next format
        GetClipboardFormatNameW format, StrPtr(b), 100 'Copy name to buffer
        If Left(b, 15) = "XML Spreadsheet" Then
             GetClipboardSpreadsheetFormat = format
             Exit Function
        End If
    Next
End Function

Public Function GetClipboardSpreadsheetXML() As String
    OpenClipboard 'Open (lock) the clipboard
    Dim format As Long 
    format = GetClipboardSpreadsheetFormat
    If format = 0 Then
         Debug.Print "Spreadsheet data not available, display message here"
         GoTo ExitHandler
    End If
    Dim hMem As LongPtr
    hMem = GetClipboardData(format) 'Get handle to clipboard data
    Dim memSize As LongPtr
    memSize = GlobalSize(hMem) 'Get memory size
    Dim buf() As Byte
    Redim buf(0 To memSize - 1) 'Initialize buffer to hold XML
    Dim memPtr As Long 
    memPtr = GlobalLock(hmem) 'Lock memory, get pointer to address
    lstrcpyA VarPtr(buf(0)), memPtr 'Copy memory to buffer
    GetClipboardSpreadsheetXML = StrConv(buf, vbUnicode) 'Convert ASCII string to unicode, return as result
ExitHandler:
    If memPtr <> 0 Then GlobalUnlock (hMem) 'If memory pointer has been acquired, unlock global
    CloseClipboard 'Close and unlock clipboard
End Function

Since you intend to convert the spreadsheet to XML, you can retrieve the spreadsheet XML by calling GetClipboardSpreadsheetXML, and then use an XSLT to convert it to the desired format.

For non-Excel data sources, you can either use HTML Format instead of XML Spreadsheet to retrieve HTML, if available, and else you can use 1 (CF_TEXT) as your format and not iterate through all possible formats to retrieve plain text from the clipboard.

You can read more about the Clipboard API on Microsoft Docs

Access - Pasting rows without table

2 Answers2