?
Solved

Access and a Large Text File:  over 8GB

Posted on 2013-10-24
8
Medium Priority
?
544 Views
Last Modified: 2013-10-30
Hello All,

I was hoping to get suggestions on the best way to handle this situation.   We have a large text file with many fields and many records.  The size of the file is over 8GB.   We need to be able to look at the data to ensure fields contain correct data, and to be able to massage some of the data.   We will be using MS Access 2010 as the front end.

I know there is a 2GB limit for an Access file, so I was thinking that we could import all the data to a SQL Server table.  We could then use Access to view and massage the data using vba code.    

I want to install SQL Server on my desktop and create a database with that.  Then I could link to the table that houses all the imported information.

My question is, can you install SQL Server and create a database all on your desktop and not on a server?
0
Comment
Question by:sgtoverlord
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 58
ID: 39598348
<<My question is, can you install SQL Server and create a database all on your desktop and not on a server? >>

 Yes, the Express edition is free and you'll have no problems doing that.

 What you've outlined is doable.

Jim.
0
 

Author Comment

by:sgtoverlord
ID: 39598407
Awesome.  

Now I am fairly astute at Access and VBA code, but I haven't created a SQL Server Database before.   Is this something that I should be able to fumble my way thru, or is it completely different?  If so, do you know of a good website or web tutorial that would assist me with this endeavor (and gives correct information)?

One other question:  Will this method be more efficient (faster) than using access to link to the text file as a table itself?   I am hoping that this is the case.  Our process currently takes hours & hours.   I was hoping that using Access with a local SQL Server db would increase the efficiency.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 39598568
I believe the express edition has a 4gb limit.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:sgtoverlord
ID: 39598816
poo
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 39598863
0
 

Author Comment

by:sgtoverlord
ID: 39598972
Well, I do see that there is a 4GB limit with SQL Server 2005 Express Edition.   And A 10GB limit with SQL Server 2008 R2.  

My company has licenses for SQL 2005 Business Intelligence Development Studio.  Does anyone know the size limit for this?

But is this file size what I am supposed to be worried about.   Let me explain:   Currently, we are using Access 2003 to link to a text file that is 8GB+ in size.   We are able to do some crunching with this arrangement, it just takes forever (7 hours+).    

I guess my main goal is to start using Sql Server to increase the speed at which this stuff is processed.  

Any thoughts on this???
0
 
LVL 58
ID: 39599974
<<But is this file size what I am supposed to be worried about.   Let me explain:   Currently, we are using Access 2003 to link to a text file that is 8GB+ in size.   >>

 Just because it's 8GB in the file doesn't mean it will be 8GB in the database.   Some may be duplicated for example.  

 When brought into a DB and normalized, it's possible that size would go down.

<<We are able to do some crunching with this arrangement, it just takes forever (7 hours+).  >>

 Depends on what your doing there may be ways to speed that up as it stands.

<<I guess my main goal is to start using Sql Server to increase the speed at which this stuff is processed.  

Any thoughts on this??? >>

  Too hard to say without a lot more detail (sample of the file, what your looking to get out of it, etc).

Jim.
0
 

Author Closing Comment

by:sgtoverlord
ID: 39612362
Thank you for the information.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question