Possible data problem

[Donnie]

going thru my nightly database routine I have come across something I have never experienced before. I downloaded and exported as normal, but when I went to process the reports, the maketable query I have run for the past 4 years suddenly became VERY slow. Turns out it was importing 375,000+ records into my Contender's table. I scrolled thru the records and quickly noticed that there were 2 horses who become duplicated over and over and over......All Bold (race 9) and another I don't recall now. I have never seen this before. I restructured the make-table query to NOT use the MNR data and it built my tables and reports with no problems.

Anyone ever hear of this ever happening and what may be the cause....??

I did go thru the routine of re-exporting the data out of HTR2 a second time and that did not fix the problem!

[dehere]

Donnie, I experienced a similar problem lately with slowness and then I got an "invalid argument" error message popping up and a real mess with my database. When I checked for duplicates in one of my tables (created through another "maketable" query) I discovered a bunch of them. My solution was to limit the maketable query to just newly imported data and then append the results of that table to the much larger base table. Hopefully, that will do the trick. At least it will maintain the integrity of my base maketable query and if future problems arise they will be limited to a much smaller portion of the database.

[Donnie]

Yeah Henry, this was just my daily table. There should have been just over 2000 records for today....was shocked because it dumps 375,000+ into my daily table....which is wiped clean every nite. My wife tells me she has seen this problem before on her computer at work. Glad I have a fast processor! Woulda taken forever on a slow machine!

[DanG]

Donnie / Henry…

Naïve question…:confused:

How could that happen if your primary keys are properly set up?

[Donnie]

Dan--
this is a table I use for my main report (I call it The Diamonds). Everyday I clear the table, export from HTR2, then import into Access and use a "make table" query that then repopulates this table. So it should only be receiving the next days' data upon each run of this query. I don't know that you can designate a primary key(s) while running this query, which is a remedy I entertained. Everytime you run the query it deletes the previous table and replaces the entire table with a new one populated with the info that a normal query would just display on the screen.

The only thing I can think is that there is an error that is either in the MNR file or my system is not interpetting this part of the export file correctly. The latter is probably more likely since I have never had this problem before, but once in a while I tend to get a problem here or there from my system. This one I have never seen before.

[Rick]

A possible work around.

Use make table query to make table.

Delete records from table.

Set Primary Key.

Change query to an append query.

[Donnie]

I will probably go that route if it still has the same problem tonight! Thanks for the suggestion Rick. Or else just not worry about MNR thru the rest of the contest.....? :eek:

[DanG]

Gotcha Donnie…

Rick’s work around is what I do for many “make-table” projects.

I just run Delete Query’s and Append to a Primary Keyed table. It’s a little more of a pain to set up but it does solve it as you know better than I.

I did run into an issue when I first started with access.

I did the delete / append routine for a project and never knew it always left the deleted table in “memory”…It hit the access 2 mg limit and locked me out of the DB for good and I had to trash it and start over.:mad:

Needless to say I compact and repair all my projects now after they run.

A nice Microsoft feature would be to let you know you’re nearing the 2 mg limit.:rolleyes:

Best of luck…

[MikeDee]

Donnie,

Don't know what to tell ya. I am downloading the contest tracks and playing from access like I always do and I have no problems with mnr or any of the contest tracks.

I am downloading the "j" file

[Donnie]

Guys-
I think it is more on my end than anywhere else. Was wondering if it has happened to anyone before.

Dan...in all your db's do this:

TOOLS>>>>>OPTIONS>>>>>go to the GENERAL tab>>>>>>on the left side turn on the checkbox next to COMPACT ON CLOSE. Don't know why they don't make that a default setting?! This makes sure all your dbs are running lean and mean!

[dehere]

I was trying to say that am now going the append route with my main table having primary keys to eliminate duplications - in my own inarticulate way. I like the "compact on close" idea Donnie - will give it a try.

[Donnie]

Found the problem.....it was on my end.
I had 5 MNR race files in the HTR2 directory that did not delete out completely. Each showed a size of 0 KB. The program, when exporting, must of tried to export these, but because they were corrupt, the data got skewed in my database and caused all the duplicates. Once I deleted out those five racefiles and re-exported, all is fine in Iowa again!