How to import multiple columns from a csv file into multiple table in MS Access 2016

[tmd63]

I have a CSV file which imports into Excel as more than 255 columns (actually 666 columns).

As Access has a 255 column limit I need to parse the CSV file and import these into 10 different tables (254 columns and one link column).

How can this be achieved in VBA? I need vba as the CSV changes over the year and has a different name depending on when it is generated.

Most columns do/have nothing in them but it is the only output created.

[arnelgp]

see this demo, sorry unable to upload here.
https://www.dropbox.com/s/rnftc6bo5uiuo1d/import666.accdb?dl=0

[OBP]

I posted how to split the incoming data in the 1st post at the head of the this forum.

Why 10 tables, why not 3?

[arnelgp]

it is not enough that you split the CSV column header to a set of 254 + 1 (or less).
you also need to determine the Fieldtype and Fieldlength, so you can construct the field properly
on creating new table rather than assign them all as Short text.
this is handled on my demo (see the link).

[OBP]

OK.