Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3752
  • Last Modified:

How update MS Access mdb file?

I'm reading a logfile with two fields - FieldA and Field - both strings 15 characters long. I want to check if the records exists in the mdb-file where Field A is the primare key with no duplicate keys allowed. If the key is not there add the record, otherwise update it. The logfile is very big, so I want to do it one by one. Can any1 give me a good example on how to do it?
0
marpeto
Asked:
marpeto
1 Solution
 
gnoonCommented:
All things you would like to do can be done easily if you can import the logfile into a temp table of the mdb file, then run both insert and update commands in a database engine. So, try to import first. If unluck, you'll need another way.

After imported, follow these steps
1. run update command first using FieldA as a relation to update (using inner join to update)

    update mytb set mytb.fieldB = tmp.fieldB
    from mytb inner join tmp on mytb.fieldA = tmp.fieldA

2. run insert command for only new records

    insert into mytb(fieldA, fieldB)
    select tmp.fieldA, tmp.fieldB
    from tmp left outer join mytb on tmp.fieldA = mytb.fieldA
    where mytb.fieldA is null

Note: mytb = the existing table in mdb, tmp = the temporary table you created for import
0
 
marpetoAuthor Commented:
Thanks for the tip, but the question is asked in VB .net and I'd prefer a VB .net example on how you could open up a mdb file and two examples on the update resp add of the database. Find some examples but not in the way I need it. Often they read the whole database into a dataset and the updates it back. The database is about 500.000 records, and I want to update about 20.000 of them (not manually <grin>). Would like to bring one record into vb .net (if it exist) and then update it (or add if its new) - pretty simple for an expert I suppose (but i'm not).
0
 
RayAtVittoriaCommented:
Check out this free tool from codeproject . It simplifies accessing and updating mdb tables by putting it all in one dll. You can dld and use it as is. I found it very useful.

http://www.codeproject.com/vb/net/atcl.asp

Cheers
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
gnoonCommented:
This code might help you to start on track, but it's not the solution. You need to modify it to adapt your requirement.

    Private Sub Synchronize()
        Dim dbConn As System.Data.OleDb.OleDbConnection = Nothing
        Dim fileReader As System.IO.StreamReader = Nothing
        Try
            'open file for read
            fileReader = System.IO.File.OpenText("c:\logfile")
            'open database for read/write
            dbConn = New System.Data.OleDb.OleDbConnection( _
                "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\database.mdb")
            'prepare command object to send/receive command
            Dim dbComm As System.Data.OleDb.OleDbCommand = dbConn.CreateCommand()
            dbComm.CommandType = CommandType.Text
            'process each line of logfile
            Dim line As String = fileReader.ReadLine
            Dim fieldA As String, fieldB As String, sql As String
            Dim affectedRows As Integer
            While Not line Is Nothing
                'extract fields
                fieldA = line.Substring(0, 15)
                fieldB = line.Substring(15)
                'try to update database first
                dbComm.CommandText = _
                    "update mytb set fieldB=@B where fieldA=@A"
                dbComm.Parameters.Add("@A", System.Data.OleDb.OleDbType.VarChar, 15).Value = fieldA
                dbComm.Parameters.Add("@B", System.Data.OleDb.OleDbType.VarChar, 15).Value = fieldB
                dbComm.Prepare()
                affectedRows = dbComm.ExecuteNonQuery()
                'if no record affected by the update command, insert as new record
                If affectedRows = 0 Then
                    dbComm.CommandText = _
                        "insert into mytb(fieldA, fieldB) values(@A, @B)"
                    dbComm.Prepare()
                    affectedRows = dbComm.ExecuteNonQuery()
                End If
                'read next line
                line = fileReader.ReadLine
            End While
            dbComm.Dispose()
        Catch ex As Exception
            Throw New Exception("Synchronize failed: " & ex.Message)
        Finally
            'close file handle
            If Not fileReader Is Nothing Then fileReader.Close()
            'close database handle
            If Not dbConn Is Nothing Then dbConn.Close()
        End Try
    End Sub
0
 
marpetoAuthor Commented:
Tanks - looks great! Exactle what I was hoping to get!
0
 
NBHmemberCommented:
You've gotten the answer you asked for here, but not the answer you're going to eventually use. 20K records, updated via one or two trips to the .mdb is going to take forever. That's why the examples you've seen perform a batch process.

Probably better to put all the data into a transient table in your mdb and run queries to do the updates. If all the work can be done within the database, it'll be much faster than a converse to the VB code for every record.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now