Solved

How update MS Access mdb file?

Posted on 2007-11-15
6
3,739 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
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

830 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