Access VBA - How to split data in one field and insert into other fields in an existing table

[Peter_W]

Hello all,

I have a piece of code regarding the "Split" function in Access VBA. It works great.

That is, basically, my sub takes a block of data, as a string, received by the Access database as a copy from the clipboard.

And then the current code here, converts the string to an array, and then places each line of data into a field called INPUT_FIELD in an Access table called VALUES. I get the right number of rows in the table after running the code

However, that data consists of 3 different columns of data from the original source file.

So, I would like to Split the data currently in INPUT_FIELD as follows, and separated by "pipes":

9834 | 7729 | 1536

Into inputs into INPUT_FIELD2, INPUT_FIELD3, and INPUT_FIELD4 respectively, which are existing fields within the same table VALUES, as follows:

9834 to INPUT_FIELD2 (different column in same row of data)
7729 to INPUT_FIELD3 " " "
1526 to INPUT_FIELD4 " " "


I sense I need the Split function, and/or maybe combined with Insert Into...Values code, but have failed on many attempts, while searching a lot too. Thank you in advance for any feedback. I am at the point of waving my white flag of surrender to VBA.


Working code:

Sub CopyFromClipToTable

Dim strString As String
Dim arrString() As String
Dim rs As DAO.Recordset
Dim i As Integer
Dim Db As DAO.Database


Set Db = CurrentDb
Set rs = Db.OpenRecordset("VALUES")

' Retrieve clipboard contents into data object
DataObj.GetFromClipboard

' Clipboard to string variable
strString = DataObj.GetText

' Convert string variable to array
arrString = Split(strString, vbNewLine)
' Post Array to Table using DAO recordset

For i = 0 To UBound(arrString) - 1
'Update Table with data from clipboard
rs.AddNew
rs!INPUT_FIELD = arrString(i)
rs.Update
Next I



Set rs = Nothing

End Sub

 

[Peter_W]

Thank you stumac for the de-bug. I will try this fix soon and get back. I have this distracting thing called my day job that I have to attend to now. All the best, Peter_W

Stumac, I got your code to work, and it works fine! Thank you! I believe a couple of times I was overwhelming the clipboard with too much data, and that may have prevented instances from running, but again, its good, and I'm all set now. Thanks again!