Changes to Imported File

[hstreff]

We are in the process of converting from dBase to Access. We have successfully imported one of our data files into Access. When we go in and try to change the field sizes we keep getting an error that there is not enough disk space or memory. Is there a general rule when doing this? We have tried decreasing the amount of records and continue to get the error. When we originally imported the file we did not set a field size limitation so all the text fields in the structure came in at a size of 255. We are trying to change them to their proper size.

If a database is 20MB how much memory or space is required to make changes such as this? The original file contains over 300,000 records.

Any help or direction would be greatly appreciated.

Heather

 

[hstreff]

Field Size

I wanted to add that my import goes fine. I get the error when I try to change the field size from 255 to anything else.

Heather

 

[Moniker]

The text field is 1 to 255 in length. For larger than 255, you need to use a memo.

However, a fixed-length string is not a requirement in Access. The default value of 255 is fine regardless of the length of the data you are storing in it. For example, leaving the text length at its default of 255 results in this:

[u]STRING[/u]          [u]LENGTH[/u]   [u]ACCESS STORES[/u]
A123            4        4 bytes
ABCDEFGHIJ      10       10 bytes
XYZ             3        3 bytes
ZYXWVUTSRQPON   13       13 bytes

In other words, there's no need for fixed-length strings as Access handles that automatically.

I'm not sure why you would be having a problem changing it from 255 (unless you were trying to increase it), but it won't make a difference if your string is less than 255.

 

[BrettStah]

http://support.microsoft.com/kb/286153/

 

[hstreff]

I am trying to make the field size smaller. Won't leaving it at 255 cause a lot of unneccessary overhead in the file? It is already over 300,000 records and growing continuiously every month. I would think this would use a lot of extra space. I even tried changing the default fields size in the Access Options to 50 but it didn't matter.

I will also check out the link for Microsoft.

Thank you both. I will let you know the outcome.

Heather

 

[Moniker]

No, that's what I was trying to show you before. Unlike dBase, Access will automatically trim the length of a stored text field to the length of the text itself. Look at the little table I did a few posts back. Storing the text "ABC" in a text field with a length of 255 will actually only store 3 bytes total. It won't store 255 bytes, as in "ABC" followed by 252 spaces. Access does this dynamically, which kills the need for fixed length strings.

 

[hstreff]

So it is OK then?? I tried changing the MaxLocksPerFile suggested by Brett(??) and that did nothing for me either.

This is a comment from my boss - I appreciate your response. Then why does it give you the option to change it? The point isn't just that we are trying to change a character field, it is that there is something really wrong. It doesn't make any sense why this is happening.

Thanks for your help.

Heather

 

[Moniker]

My guess is that you are importing the data as fixed-width fields, at which point you cannot change the field's size.

In order to change it, you would create a new table, mirror the structure of the data you import, and then use an append query to to take the data from the imported table to the newly constructed table. That would give you the ability to change the field sizes.

 

[hstreff]

Well, the file is a dBase file. I have tried to open the dbas file in Access but as soon as I select the file and hit open, my Access immediately closes itself. No rhyme or reason. So the work around I came up with, was to open the file in Excel, save it out as an excel file then import that. I am importing the file into a new table. I tried to create a table and import but then the program seemed to be having a problem with character fields that contain numbers. We have had no luck no matter what we try. So you're saying that if we create a structure (or use the original one we set up when we started out with the character problem) but them import the excel file into that, it may work??? I found out that we had 90 days of complimentary support from Microsoft (I WAS SHOCKED!!) so a technician is working on the problem as well. It just doesn't make any sense. It almost seems as if there may be a problem with the installation.

I really appreciate all your help and direction. I am just so frusterated. I have been working on this for days with my boss and he has continued on his own at night. So basically 3-4 days straight now to no avail.

He is also posting in this forum the thread is 2 or 3 away from this one. Here is the link http://www.access-programmers.co.uk/forums/showthread.php?t=126945. Sometimes he can explain things better than me and vice versa.

Heather

 

[amerifax]

Setting?

My guess is that you are importing the data as fixed-width fields, at which point you cannot change the field's size.

Is there an option some where for this.

In order to change it, you would create a new table, mirror the structure of the data you import, and then use an append query to to take the data from the imported table to the newly constructed table. That would give you the ability to change the field sizes.

I will give this a try.

Bob