Link to home
Start Free TrialLog in
Avatar of Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScFlag for Zambia

asked on

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:

For Each details In Json
     With rs
            .AddNew 

Open in new window




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

Open in new window


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


User generated imageTraining.accdb
finish.txt
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

You did not say what is the error you get ?
gowflow
and just a quick guess try changing this
dbOpenSnapshot

by this
dbOpenDynaset

gowflow
Avatar of Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

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
Avatar of Bill Prew
Bill Prew

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.
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

 
  "ESDTime": "20200407191641",
	"TerminalID":	"010300000080",
	"InvoiceCode":	"000200110000",
	"InvoiceNumber":	"00009314",
	"FiscalCode":	"59309674430436600405",

Open in new window


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

Open in new window



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

User generated imageTraining.accdb
finish.txt
Will there only be one row in the input text file, or will there be multiple rows?
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you so much Bill Prew that what I wanted.

Regards

Chris
Welcome.

»bp