Solved

How update MS Access mdb file?

Posted on 2007-11-15
6
3,736 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 16

Accepted Solution

by:
gnoon earned 500 total points
Comment Utility
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
Comment Utility
Tanks - looks great! Exactle what I was hoping to get!
0
 

Expert Comment

by:NBHmember
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now