Access Tables -> Change column type

[Fherrera]

Hi i'm trying to change the type of a column in a table but i'm getting a "not enouph disk space/not enouph memory" error.

How might I go about getting around this?

Can i break the table up in chunks, do it, then join them together again?
(I'd like to know how to do this regardless as I have 3 db's that have 1 table each which i'd like to join but each one is about 600mb and I was told 1gb is the limit for Access 97)

In any case, would this be somethign I should do in VBA?

It literally takes 20-40 minutes to do queries on this dB... or any kind of change...

Any help, pointers, tips would be appreciated.

Thx

 

[THWatson]

Fherrera
Have you tried "splitting" the database into a Front End and Back end operation? Typically, the Back End is the smaller part of the database.

You could make a copy of the database, do the splitting on it, and see what happens. Then you wouldn't be nervous about wrecking your project.

Secondly, can you compact the database and reduce the size that is taken up on a disk?

Tom

 

[Fherrera]

Hi THWatson, thanks for the quick reply.

I'm not sure what you mean by "splitting" into front and back end, although it does sound intriguing. Could you elaborate please?

This all started with 1.5 gigz of CSV data. All of that data being in just 3 columns... I had to break up the csv file and import each into 3 separate access databases as I hope to access these form Excel later on to graph trends...

In any case, compacting doesn't help

Frank

 

[THWatson]

Frank
I use Access 2000, not Access 97, but I think database splitting is available in that version.

Go to Tools, Database Utilities, Database Splitter.

Tom

 

[Fherrera]

Thanks Tom,

I actually did a keyword search on the forum to find out what it was

It under Tools->Add Ins->Database Splitter for access 97.

Now I can ask some questions concerning your first post:

I don't have a server/client. I'm both. Will splitting help even if the "server" is just on my own machine?

If the backend holds the Table and the front end holds queries, reports, forms, etc. WOudln't the backend be bigger? Since that's where the data itself is stored?
Atleast in my case... I Have 600Mb of raw data and not so much as far as queries or anything... I'm just trying to format the data...

To import it I had to have the date be text because in the format it was in, Access could not convert it to a date. So I did an update query after I had imported all the information with the CDate() function... Which allows the column to be converted to a date now (so I can use the field as an actual date and do comparisons properly) But, like I said there's too much information to be able to do this (not enouph mem/hd space)

Thanks

Frank

 

[THWatson]

Frank
I have several databases on a stand-alone machine. I have them split into FrontEnd/BackEnd. In my situations, since there are several queries, forms and reports, the FrontEnd is always considerably larger. However, with your case - 3 separate databases with each having 600+ meg of data - probably your tables are larger.

Part of which means that I misread your original post with respect to the size of your database!

When you try to rename a column, it appears likely that Access has to make an extra copy of the table in order to make the changes and then bring stuff back together, so this is making a whopper of a disk size...plugging both the hard disk and the computer's memory. 20-40 minutes to run a query must try your patience!

As for breaking the table into parts, have you tried making 2 or 3 new tables, each having some of the fields in one of your tables, using Append queries to append the appropriate records to each of the new tables?

Again, before I did this, I would make a back-up of the database and work on that, in order not to lose anything from your original project.

Just for experimenting, you could also try the Database Splitter on a back-up copy.

Further than this, Frank, I'm at a loss to suggest anything. If none of this gets you anywhere, make a new post. There are some real experts around. They might have other ideas.

Good luck!

Tom

 

[Fherrera]

As for breaking the table into parts, have you tried making 2 or 3 new tables, each having some of the fields in one of your tables, using Append queries to append the appropriate records to each of the new tables?"

I'd like to do this... but i'm still a newbie with Access. I can make the new tables, i'm not sure about the append queries...

What ideally i'd like to do is split the current table i have into 3 smaller tables of equal (approx) size chunks.

Is there a way to append based on row#... (i actually don't have any primary keys :<.. I used VBA to import the raw data (about 50, 30-40mb CSV files and I wanted to automate the importing)

Or maybe a way of adding primary keys then splitting based on them.

-

I'm currently trying to split the database but... it's taking a while

Thanks again for all the help Tom

 

[THWatson]

Frank
An Append query, takes data row by row and appends it to another table. Would it do the trick in your case. Not sure. I'm flying a little blind here, because I can't visualize the exact make-up of your database.

However, two thoughts...

1. I'm wondering if a Make Table query would work. You can take some of the fields from one table and make a new table from those. I'm assuming that a Make Table query is available in Access 97.

2. You could also have a peek at the Table Analyzer, and follow its suggestions. It will make recommendations on how a table might be split up to reduce repeated data. If that would work, it could reduce the size of your database.

Again, good luck.

Tom