Solved

How update MS Access mdb file?

Posted on 2007-11-15
6
3,742 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

752 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