access large files 2

[rmill]

The file I want to use are about 1.7GB Can Access handle that size file without taking a long time to process..If so how do you deal with that issue please.

 

[Tejanorey]

explain your question? Tejanorey

"Do not attack the First Marine Division. Leave the yellowlegs alone. Strike the American Army."
- Orders given to Communist troops in the Korean War; shortly afterward, the Marines were ordered to not wear their khaki leggings to keep the enemy from immediately fleeing

 

[rmill]

I want to write a query using a 1.7 GB table. How long will it take to run the query..I have heard that Access does not handle that large a file

 

[SgtJarrow]

Access 2K and above have a 2G database limit size....as you your processing, it is hard to say how long it will take without some knowledge of what you are doing.... ****************************
Only two things are infinite, the
universe and human stupidity, and
I'm not sure about the former.
(Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[rmill]

I want to join 2 files by order# and select a status and an entry date..one file is 234M and one 1.7 G..what else do you need to know? I want to know if it is feasiable to use Access for this type of query..Thanks

 

[SgtJarrow]

Okay...let's begin with the basics...you being fairly new to Tekp-Tips, you should check out faq181-2886 and there are several threads floating around as to how you should format your question.

Now to the question at hand. You have two text files...one is 1.7G and is structured how? How many fields? Provide an example of one or two lines so we can see. You have a second file, approx 250M. Again what is the structure???

You want to compare the two and and find a match by order #, and save some of the fields but not all. Sound about right???

This can be done, although I would probably steer you away from a query or two and do this through VBA code. Reasoning: To use queries, you will have to import both files, and may very likely crash the db as it will go over the 2G limit.

You can do the following:

You would import the smaller file and then use VBA code to go through wach line of the larger file (which you did not import, don't worry, you can do this!) and look for a match in the smaller file. If a match is found, save the data you want into a new table. After you have gone through the large file you are done. The speed of this operation will vary on the actual number of lines (or records) in each file. Again, please provide some more information (Be as detailed as you can...input examples, output example, etc) and we will gladly help. Without more detailed info, I can't halp you more. ****************************
Only two things are infinite, the
universe and human stupidity, and
I'm not sure about the former.
(Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[rmill]

Actually I am researching..we now use Foxpro .dbf files which we download each night converting from Betrieve Dat files. Looking to get off of Foxpro. My larger table has
20 fields and the smaller one about 70 plus we then access 4 or 4 other tables in order to bring in other information, sometimes grouping, sometimes not. We just looking into what Report writer would do the job best. Crystal is also slow..

 

[SgtJarrow]

Kewl...Understand what you are saying. I don't know if Access will be much better. If you design a report in Access and have time to catch it hitting the printer, you will see it registers in the print spool as a Crystal Report anyway....don't know if that means anything.....

To do the search and fill probably wouldn't take all that long. The import of the smaller file, if you didn't include all 70 fields, would probably only take a moment or so....I have a 2500 page word doc that imports in about 10 seconds.....The connection o the larger file and the searching for matches is hard to determine. Once that is done, the report generation, properly set up, shoudl only take a moment or so also.

So barring the search and fill portion, there is probably only a minute or two worth of overhead. But again, it will take some testing on your part to determine the time. ****************************
Only two things are infinite, the
universe and human stupidity, and
I'm not sure about the former.
(Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[rmill]

Thanks..that helps..Think we'll test Visual Foxpro..now with Foxpro 2.6 we can manipulate the data in Foxpro for a complex report and then go to Crystal to finish some of the things Foxpro won't do..or use Access.
Thanks again..

 

[MichaelRed]

Look into the &quot;MSDE&quot; capability of Ms. A. It is really a versoin of MS SQL Server, and so can deal with larger recordsets. More importantly, it supports 'stored' procedures and / or pass through queries. This CAN (should?) enable Ms. A. to obtain ONLY the relevant records/fields for your reporting processes. Hopefully this would be <<<<<<<<< less than the GIG sizes you mention. If not, then I would STILL suggest using MSDE, but also re-structure the reporting into smaller chunks. A (hopefully) last thought on the subject, is that neteorks in most environs are set up with defaults which favor the more common office apps (memo type documents, e-mail, occassional spreadsheets of MODEST complexity / size, occassional Power Point presentations, ...) even the normal db doesn't do well in this environment, and the ~~ GIG sized things you mention often cause sever problems with hte network itself, other users o the network AND the 'net cops'. To avoid some of these, it may be better to have place the whole magillia gorillia on your local machine and do ALL the processing locally.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places