How to update data from Ms Access VBA to the database table using UPDATE query
I'm having an error parsing data into Ms Access from the notepad string , please note that the information and the problem is highly summarized to ensure that everyone is able to follow easily and a small database with one table and a form are the only objects included. the error occurs on the small code below:
VBA CODE
If you want to use or see the real issue then have a look at the attached small database and the attached notepad details to written in the Ms Access table
Regards
Chris
Training.accdb
finish.txt
For Each details In Json
With rs
.AddNew
VBA CODE
Option Compare Database
Option Explicit
Private Sub CmdParsers_Click()
'Declaration of objects
Dim Json As Object
Dim details As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Z As Integer
'String from notepad required to parsed into a table called tblEfdReceipts
Dim intFile As Integer
Dim strFile As String
Dim strText As String
Dim strInput As String
intFile = FreeFile()
strFile = "C:\FolderName\FileName.txt"
Dim strFilename As String: strFilename = "C:\Users\chris.hankwembo\Desktop\Latest test 2020\finish.txt"
Dim strFileContent As String
Dim iFile As Integer: iFile = FreeFile
Open strFilename For Input As #iFile
strFileContent = Input(LOF(iFile), iFile)
Close #iFile
MsgBox "FileContent:" & vbCrLf & strFileContent
'Processing data from the string above
Set db = CurrentDb
Set rs = db.OpenRecordset("tblEfdReceipts", dbOpenSnapshot, dbSeeChanges)
Set Json = ParseJson(strFileContent)
Z = 2
' Process data.
For Each details In Json
With rs
.AddNew
![ESDTime] = details("ESDTime")
![TerminalID] = details("TerminalID")
![InvoiceCode] = details("InvoiceCode")
![InvoiceNumber] = details("InvoiceCode")
![FiscalCode] = details("FiscalCode")
![InternalRef] = Me.txtinternalref
.Update
End With
Z = Z + 1
Next
rs.Close
Set rs = Nothing
Set db = Nothing
Set Json = Nothing
MsgBox ("Complete")
End Sub
If you want to use or see the real issue then have a look at the attached small database and the attached notepad details to written in the Ms Access table
Regards
Chris
Training.accdb
finish.txt
and just a quick guess try changing this
dbOpenSnapshot
by this
dbOpenDynaset
gowflow
dbOpenSnapshot
by this
dbOpenDynaset
gowflow
ASKER
The error is error number 13, kindly see the attached database to see it for your self , just put the text file in a folder and change the path appropriate.
Regards
Chris
Regards
Chris
Okay, here are some issues I see:
Set rs = db.OpenRecordset("tblEfdReceipts", dbOpenSnapshot, dbSeeChanges)
Change dbOpenSnapshot to dbOpenDynaset.
For Each details In Json
This indicates JSON is some sort of a collection, but when I tested here it was just a Dictionary object. As such there is only one row returned by JSON after parsing the text file, and the key value pairs have the column data in them. Maybe this is just the test data, not sure...
![ESDTime] = details("ESDTime")
This throws a data type error since you are trying to place text date in a DATE/TIME field. You will need to convert the JSON returned string data to a date/time data type to store in that table column.
Set rs = db.OpenRecordset("tblEfdReceipts", dbOpenSnapshot, dbSeeChanges)
Change dbOpenSnapshot to dbOpenDynaset.
For Each details In Json
This indicates JSON is some sort of a collection, but when I tested here it was just a Dictionary object. As such there is only one row returned by JSON after parsing the text file, and the key value pairs have the column data in them. Maybe this is just the test data, not sure...
![ESDTime] = details("ESDTime")
This throws a data type error since you are trying to place text date in a DATE/TIME field. You will need to convert the JSON returned string data to a date/time data type to store in that table column.
ASKER
Okay
I have made the changes as per your suggestion and the new code now looks as below but its now pasting the title instead of the require & correct values see below:
KEY/VALUE
New VBA Code
See the screen shoot of wrong pasting and its multiplied by 4 instead of once ONLY I do not want to paste the headers /KEYS , but the VALUES ONLY , the headers are already on the top in the table all I need is the Values as per key header
Training.accdb
finish.txt
I have made the changes as per your suggestion and the new code now looks as below but its now pasting the title instead of the require & correct values see below:
KEY/VALUE
"ESDTime": "20200407191641",
"TerminalID": "010300000080",
"InvoiceCode": "000200110000",
"InvoiceNumber": "00009314",
"FiscalCode": "59309674430436600405",
New VBA Code
Option Compare Database
Option Explicit
Private Sub CmdParsers_Click()
'Declaration of objects
Dim Json As Object
Dim details As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Z As Long
'String from notepad required to parsed into a table called tblEfdReceipts
Dim intFile As Integer
Dim strFile As String
Dim strText As String
Dim strInput As String
intFile = FreeFile()
strFile = "C:\FolderName\FileName.txt"
Dim strFilename As String: strFilename = "C:\Users\chris.hankwembo\Desktop\Latest test 2020\finish.txt"
Dim strFileContent As String
Dim iFile As Integer: iFile = FreeFile
Open strFilename For Input As #iFile
strFileContent = Input(LOF(iFile), iFile)
Close #iFile
MsgBox "FileContent:" & vbCrLf & strFileContent
'Processing data from the string above
Set db = CurrentDb
Set rs = db.OpenRecordset("tblEfdReceipts", dbOpenDynaset, dbSeeChanges)
Set Json = JsonConverter.ParseJson(strFileContent)
Z = 1
' Process data.
For Each details In Json
With rs
.AddNew
rs![ESDTime] = ("ESDTime")
rs![TerminalID] = ("TerminalID")
rs![InvoiceCode] = ("InvoiceCode")
rs![InvoiceNumber] = ("InvoiceCode")
rs![FiscalCode] = ("FiscalCode")
rs![InternalRef] = Me.txtinternalref
rs.Update
End With
Z = 1
Next
rs.Close
Set rs = Nothing
Set db = Nothing
Set Json = Nothing
MsgBox ("Complete")
End Sub
See the screen shoot of wrong pasting and its multiplied by 4 instead of once ONLY I do not want to paste the headers /KEYS , but the VALUES ONLY , the headers are already on the top in the table all I need is the Values as per key header
Training.accdb
finish.txt
Will there only be one row in the input text file, or will there be multiple rows?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much Bill Prew that what I wanted.
Regards
Chris
Regards
Chris
Welcome.
»bp
»bp
gowflow