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
file.

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.

QUESTION IS:
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
DaMstrCat.Fill(DsMstrCat.MASTER_JCM_STANDARD_CATEG ORY)

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

Private Sub MstrCat()

TextBox3.Text = DsMstrCat.MASTER_JCM_STANDARD_CATEGORY.Rows.Count

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

Dim dtDataTbl As DataTable = DsMstrCat.MASTER_JCM_STANDARD_CATEGORY

' 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
problem):

For cntr = 0 To ArraySize - 1
' Open Connection and ADD New Data
OleDbConnection1.Open()

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,
cntr))

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

End With

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

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

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

Bruce
Nov 21 '05 #1
  • viewed: 1273
Share:
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,

Cor
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.