467,169 Members | 992 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,169 developers. It's quick & easy.

HELP: duplicated info when re-reading Access db Tables

VB.net 2003 c/w Framework 1.1 and MS Access db

We have a commercial program that does our Acounting and Time Sheets
(Timberline). At least once a day our Accounting department runs a Script
file that Exports info into an MS Access db file and which has 7 Tables (stuff
like Project info, Project numbers, User names, etc).

I wrote a Time Sheet entry application to read this info and to allow
Employees to write their Time Sheet data into another MS Access db that gets
Imported back into Timberline...all works fine.

I then wrote another application that I read the Timberline exported file
(that has 7 Tables) and build a new Access db into a Single Table (I do this
to simply speed up my Time Sheet application as there is at least a 2-3 second
load hit for each Table that I have to read when my application starts). This
'update' application runs on my PC and every few minutes checks for an updated
Timberline Export and then creates my single table db. I basically read each
table and dump the info into an Array... then write the Array to a new db

Now my update application sort of works fine. I've found out that it will
work flawlessly the FIRST time it does an update... but in subsequent updates
the 7 Tables Data appears to multiple. In other words, the first time, say
there is User "A"... the second time the update happens, User "A" is shown
Twice, etc.

I've tried figuring out what happens and the only thing I can think of is that
somehow the application is re-reading the table info while keeping previous
table info in memory... IF SO... how do I 'CLEAR' out the 7 tables from memory
before I re-read the updated info???? I've tried closing the Connection for
the 7 tables file but that doesn't appear to work...

Here is some of my applicable Code:

In an UPDATE data adapters and schema's are done (7 of them) --- I used the
Wizard to open my Connections and Db files, etc.

' Master Category Table

' Master Cost Code Table
' Select ONLY Current Codes (having Boolean as 0)
daMstrCode.SelectCommand.Parameters(0).Value = 0
Then each table info is put into an Array (one sub-routine per table for ease
of programming):

Private Sub MstrCat()


If TextBox3.Text > ArraySize Then
ArraySize = TextBox3.Text
ReDim Preserve ProjArray(16, ArraySize)
End If


' Get Info from Data Base
For cntr = 0 To DsMstrCat.MASTER_JCM_STANDARD_CATEGORY.Rows.Count - 1
Dim dr As DataRow = dtDataTbl.Rows(cntr)
ProjArray(9, cntr) = dr("category")
ProjArray(10, cntr) = dr("description")
Next ' cntr

End Sub
Then I write the Array to my new 1 Table db --- I'm currently writing each
line (about 450 lines) to my db one at a time as I thought that was the

For cntr = 0 To ArraySize - 1
' Open Connection and ADD New Data

With DsDataUpdate.Tables("TSUpdate")
Dim NewDr As DataRow = .NewRow
NewDr("Employee") = ProjArray(0, cntr)
NewDr("Employee_Name") = ProjArray(1, cntr)
NewDr("Class") = ProjArray(2, cntr)
NewDr("Chargeout_Level") = ProjArray(3, cntr)
NewDr("Pay_ID") = ProjArray(4, cntr)

NewDr("Employee1") = ProjArray(5, cntr)
NewDr("Pay_Type") = ProjArray(6, cntr)
NewDr("Pay_ID1") = ProjArray(7, cntr)
NewDr("Amount") = Val(ProjArray(8, cntr))

NewDr("category") = ProjArray(9, cntr)
NewDr("Description") = ProjArray(10, cntr)

NewDr("Cost_Code") = ProjArray(11, cntr)
NewDr("Description1") = ProjArray(12, cntr)
NewDr("Group_Cost_Code") = System.Convert.ToBoolean(ProjArray(13,

NewDr("Job") = ProjArray(14, cntr)
NewDr("Description2") = ProjArray(15, cntr)
NewDr("Status") = ProjArray(16, cntr)

End With

' Send Changes to DataBase and Disconnect
daDataUpdate.Update(DsDataUpdate, "TSUpdate")
'Carry out your code to do stuff to database here...
Catch dbEx As System.Data.OleDb.OleDbException
End Try

' Next Line
Next cntr
Then I clear all my variables:

' Reset Variables
Erase ProjArray
cntr = 0
cntrA = 0
ArraySize = 0
fd1 = ""

Nov 21 '05 #1
  • viewed: 1273
1 Reply
Mr. B.

I thought that the most change for the problem you have is when you don't
have the primary key in your schema.

Just my thought,

Nov 21 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by onTilt | last post: by
reply views Thread by Eric Chaves | last post: by
10 posts views Thread by One Handed Man [ OHM ] | last post: by
reply views Thread by Olivier Bonneau | last post: by
6 posts views Thread by hzgt9b@nopost.com | last post: by
4 posts views Thread by matpac4@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.