Access Limit of 148000 Records?

[amerifax]

Finally after 2 1/2 days I have the ability to bring a dbase 5.5 file in to Access 2007.

1.Do a normal export of a DBF file out all dbase.
2. Excel 2007 - open the DBF file. Note: dBase Files (*.dbf)
3. Save as a .xlsx file.
4. Access 2007 - right click on table in left column\Import\excel
5, Note: You'll be looking at a window with a yellow bar across the top.
Browse to your *.xlsx file and open.
6. Select "OK"\show Worksheets\ next\First Row Contains Column Headings\Next\Next\No Primary Key\next\Finish\close
7. Double Click on file in left column and there it is.

Never got other methods to work, sad to say.

After a day to get to this result, the problems begin. Every time we would change one character field from 50 char to 6 char, or whatever, the out of memory or disk space error would come up. I have 250 gig available and I'm running 4gig of memory with nothing loaded but Access 2007. I even shut down my virus checker.

So I spent a day or two trying to find a wining combo (A File Size That Would Work). And here it is, 148500 records that comprise of a 21.3m file is the breaking point. If I change a character field, 250char to 6 char, I'm ok. If I change a couple more it get the error message. 150,000 will get a memory/Drive error no matter what is changed.

Is there a option in Access that should be on. Because I find it hard to believe that an old dBase program, one step up from DOS, can handle 350,00 records all day long and Access chocks on 148000 records. Up until now I thought Access was the best thing that happened. There has to be something wrong, no software designer would have done this. I even checked for a corrupt file, the best I could. If there is a file checked out there, I’m open to it.

 

[amerifax]

Dbase - Excel - Access

The more I think of it. The problem might be the PoGo Stick dance I'm doing to get my data into Access. I'm going to try to find away to go dBase to Access, again. Wish me luck.

Bob

 

[amerifax]

Access Vanishes with dbase Import Attempt

I’m using a book called MS Access 2007 Bible. Seems like a good manual. It suggested I go to External Data\more\dBase File, which I did. When I went to one of the files that came with the book and clicked (the program vanished like a greased goose).

 

[neileg]

This may help.
http://support.microsoft.com/kb/286153/

 

[amerifax]

No Luck

I tried changing the MaxLocksPerFile value in the registry and that did nothing. I still get the same error "Microsoft Access can't change the data type. There isn't enough disk space or memory."

The girl in my office is working on the same problem, I think you may have replied to her as well. I do appreciate the insight but I was wondering if there is anything else that may be causing this. This just doesn't make any sense. I can't even do just a plain ole' import from dBase. Everything I try to do a File - Open, as soon as I click on the file, Access immediately closes (shuts down) the program. I am really starting to wonder if there may be something wrong with my installation.

Bob

 

[amerifax]

This may help.
http://support.microsoft.com/kb/286153/

This did'nt work. We called MS and sent the file to them. They said it should work. I told them we got lots of good advice but nothing seems to work. Now it's there turn I guess.

Bob

 

[The_Doc_Man]

If there is a way to export this stuff to a delimited text file as an intermediate, that might help. Sometimes you find yourself stuck because of oddball formatting and all you have to do is break the formatting link. By going through something that doesn't make so many bad assumptions.

In the final analysis, if nothing else worked, I would try to export from DBase to a .CSV or .TXT file with appropriate delimiters. Then I would write my own VBA code to read the records of the file using BASIC interpretation rules, then populate the table via Recordset operations. If the table exists already and you clean up the text fields before attempt the .Update, you should get along pretty well.

This is a case where the Access Wizards do the wrong thing and they are so dumb that they cannot be forced to do the right thing. It happens now and again. After all, this IS a Bill Gates product.

 

[amerifax]

Another Long Night

In the final analysis, if nothing else worked, I would try to export from DBase to a .CSV or .TXT.

We have tried a txt file. I will extend it to include the about. But first I'm going to try the following:

1. Import the dbase file MAKE NO CANGES. and close it.
3. Re open and delete all records and close.
4. Create the proper structure in Access.
5. Append the problem database "dbf" to the newly created Access structure.
6. Assuming this will work, I will then try changing the fields to test.

I'm kind of betting on this.

Note. MS (Good People - bit of a language problem, they need to try Dragon) I Kind of suggested the direction. They mentioned that if I append and I won't have to change the structure on the import. NO NO NO. The problem is the problem. I was disappointed in the fact they would suggest forgetting about the fact that the memory error came up. I want to know “why” more than fixing the problem.


Bob

 

[Moniker]

Don't set the MaxLocks in the registry. That will most likely be overwritten. Do it at the DB level, and call it either in an AutoExec macro (as a RunCode), in the Form Load on your main form, or even in the immediate window. The code is:

Application.DBEngine.SetOption dbMaxLocksPerFile, 10000000

That should kill the "disk space or memory" crap at least.

Doc Man's suggestion will also work, although it's much slower than a straight input. It's a lot more flexible and it doesn't have some of the built-in limitations of using the built-in importing functionality, but the time difference is rather significant.

As for the 148,000 limit, I have tables with well over 6,000,000 records in them. The limit is not in the record count, but rather the memory availability.

 

[The_Doc_Man]

I am convinced that part of the problem is that auto-conversion to text fields 255 bytes per pop for 150K records x however many text fields per record.

If you force the operation to go through a text intermediate, you can control it better.

It ALSO hits me that in order to change this stuff, you have to have the table in memory TWICE. Once where field X is 255 bytes long (and filled with blanks) and once where field X in the new table is shorter. BUT the other fields are all still there. So you doubled your table size. 255 x 148,000 says that ONE FIELD worth of data amounts to 37.74 Mb - plus the 6 x 148,000 from the updating table, another 888Kb. Now, add together all of the OTHER text fields that ALSO still have to be in memory. You might be blowing memory either of two places... the buffer in Access or swap space in Windows. How many of these text fields do you have to convert anyway? (I.e. how many text fields in a single record?)

If you export from DBase to a linear CSV and then create an EMPTY Access table with correct recordsizes, then do the import as an append to an existing table, the table only has to exist once. Also, you will be able to do various edits on it by using the VBA method. Like, Trim$ function among other things. My method is DEFINITELY slower (as Moniker correctly points out) but is more memory-friendly.

 

[amerifax]

How Many?

(I.e. how many text fields in a single record?)
One 308,000 Records
Two 15,000 Records
Two 3000 Records

18 Years of work.

I just had a crazy thought. I wounder how long it would take to type?

Bob

I will be back tomorrow to give the above ideas consideration. Burnt out for the day.

 

[Moniker]

Bob and Heather,

If you can upload your dBase export (just the first 100 records or so and mask out sensitive data), it will be a ton easier to see what's happening here. What you're experiencing is not necessarily uncommon as old DBs may or may not export in a fashion that the new stuff readily reads, but the amount of trouble you're going through is far too much for what is ultimately a simple transistion. If you're not comfortable posting a sample of the data, feel free to PM me and we can discuss it over an IM of your choice.

Like many programmers, I'm much more visually oriented. If I can see it and play around, it's a lot better than stabbing in the dark at potential solutions. I can test in Access 2003 or 2007 -- your choice -- but a sampling of the data that's causing so many issues would produce a result far quicker.

 

[amerifax]

Import Blind folded

PM me and we can discuss it over an IM of your choice.QUOTE] ??

MS tech is kind of stumped himself. He seems to be saying, language, I should be happy with 255 char field. I'm not; even if I need not change it down to 7 char. The point is broke is broke.

Yes I've tried ever witch way there is and then some.

I would have bet the ranch on my last brain storm. I went into my dbase program, Visual dBase, and created the structure I would need. Then I appended my records into the clean structure. Next I did the dance:

1. Open in excel 2007.
2. Expand columns.
3. Save as spreadsheet.

4. Open Access 2007\rt click on table under "all Tables" and do the import dance.

When I try to change a text filed from 255 to 5 I get the memory-drive not big.

First off the text change problem; that makes me skittish on Access. I still can’t wait to jump into to Access.

The file has 308,000 records with a total file size of 44m. If I only use 147,000 records I have no problem. Over that the text change problem occurs.

It seems that Access chews the whole file to change the text file size. Someone said they had a file with 6 million records. I could only see that if he never changed the structure.

Yes I could sure use some help. Sorry don't know PM or IM. Yes I know what dos is. Kidding.

Bob

PS Yes I can know import blind folder.

 

[BrettStah]

Try this:

1) Export the data as a text file, but delete everything except the first couple of records, and then save the extremely-truncated version with a new name.

2) Import this truncated version into Access, and see what happens.

3) If you still get the same error, then the problem is not with the number of records.

4) If you don't get the error, then resize the fields to the sizes that you want, and then import the bigger text file into a DIFFERENT table. Then use an append query to take the data (delete the records from this second table to are already in the first table) from the second table and append it into the first table.

 

[amerifax]

Agree with the Append Idea

Try this:
1) Export the data as a text files, but delete everything except the first couple of records, and then save the extremely-truncated version with a new name.
2) Import this truncated version into Access, and see what happens.
4) If you don't get the error, then resize the fields to the sizes that you want, and then import the bigger text file into a DIFFERENT table. Then use an append query to take the data (delete the records from this second table to are already in the first table) from the second table and append it into the first table.

I Did 1 & 2.
I agree with the append. I read up on it last night and it’s a bit confusing.

In dbase its simple
Use Permit excl
append from per_2
And it's done.

I think if I create a new structure in dBase then append the wanted records into the structure I will have perfectly clean records. Then an append into Access 2007 should work or there is a bug. I'm starting to look that way. I'm the guy that found the "File Open" error, just before Aston Tate sold dbase. That was right around the switch to Windows Platform. No one would admit to it tell I nailed it on the head. You should be able to change a text size on any structure that Access will open, as long as they give you the permission, not grayed out.

Bob

PS
Actually the append is more than an idea. It should be the safest solution. Append on both sides has to WORK. If not, I'm thinking something is crawling around.

 

[amerifax]

I'm sorry we did not close this issue with a solution to our problem. Don't ready know what was at fault.

I can tell you that after we reinstalled Access the problem was gone.

Bob

 

[The_Doc_Man]

Reinstalled and problem went away?

Did you deinstall first or just over-write install?

Did you by any chance ALSO do a Windows Update to include Office Update? Because when a re-install fixes a problem, it points to one of several errors related to .DLL files.

First, you could have had a corrupted .DLL that a re-install fixed.

Second, you could have failed in your original installation to include appropriate options on the DB Conversion utilities.

Third, if you did an auto-update of Office from the MS update site, that could have fixed some trivial little bug that nobody was thinking about but that apparently made a huge difference for your case.

Fourth, if the previous install was somehow a mixed environment, the re-install "unmixed" it in all likelihood. Which always helps. Homogenous installs ALWAYS work better than heterogenous installs.