Solved

How update MS Access mdb file?

Posted on 2007-11-15
6
3,744 Views
Last Modified: 2013-12-25
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
Comment
Question by:marpeto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 16

Expert Comment

by:gnoon
ID: 20288892
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
 

Author Comment

by:marpeto
ID: 20289641
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
 
LVL 2

Expert Comment

by:RayAtVittoria
ID: 20289999
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 16

Accepted Solution

by:
gnoon earned 500 total points
ID: 20294960
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
 

Author Comment

by:marpeto
ID: 20296661
Tanks - looks great! Exactle what I was hoping to get!
0
 

Expert Comment

by:NBHmember
ID: 20845126
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month11 days, 1 hour left to enroll

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question