Changing the length of Text fields in an Access linked table

Question

I am exporting a file from a system as .csv. My aim is to link to this file as a table (which matches the output field for field) and then run the queries and export.

The problem I am having is that, upon import, all the fields are 255 bytes wide rather than what they need to be.

Here's what I've tried so far:

• I've looked at ALTER TABLE but I cannot run multiple ALTER TABLE statements in one macro.
• I've also tried appending the table into another table with the correct structure but it seems to overwrite the structure.
• I've also tried using the Left function with the appropriate field length, but when I try to export, I pretty much just see 5 bytes per column.

What I would like is a suggestion as to what is the best path to take given my situation. I am not able to amend the initial .csv export, and I would like to avoid VBA if possible, as I am not at all familiar with it.

Answer 1

In the end, as the data set is not that large, I have set this up to append from my source data into a table with the correct structure. I can now run my processes against this table as per normal.

Answer 2

You don't really need to worry about the size of Text fields in an Access linked table that is connected to a CSV file. Access simply assigns each Text field the largest possible maximum size: 255. It does not mean that every value is actually 255 characters long, it just means that any values in those fields can be at most 255 characters long.

Even if you could change the structure of the linked table (which you can't), it wouldn't make any real difference except to possibly truncate longer Text values, and you could easily do that with a String function. For example, if a particular field had to be restricted to 15 characters then you could simply use Left([fieldName], 15) as a query column or as the control source in a report.