Table greater then 365 columns

[Starbucks_33]

Hi,

I want to import a text file into Access, but there are more then 365 columns and it will not allow me to do this. I am able to link to the table.

Why does access not allow tables > 365 columns?
Is there any way around this other then linking?

I have Access 2003, windows XP

 

[alansidman]

Look at this web site for the limitations that Access has.

http://www.databasedev.co.uk/access_specifications.html

Alan

 

[Starbucks_33]

Thanks for the link. What do you do when the table is over the limit have you ever come across this problem before?

 

[CT Witter]

Can you give an example of your data? I suspect you may be using dates or other info that could be normalized into a database format.

HTH,
CT

 

[Starbucks_33]

The data is a comma seperated text file. The data itself is just integers.

The problem is that access has a limit on the number of columns, I just learned this is 255.

So any text file, no matter what the data is, can not be imported into access given the limitations from access --> from the link above.

I was a little surprised that tables are limited to 255 columns (I dont use access a lot) it seems like large tables like this would be pretty common so people must have a solution for dealing with it.

 

[Starbucks_33]

Oh... I see what you are saying, you are right I can link the table, and then filter only those columns that are necessary for my work.

I guess this is the solution I will take. As far as normalization, the fields are all unique things so I dont think I can normalize them much.

 

[boblarson]

As far as normalization, the fields are all unique things so I dont think I can normalize them much.

Your comment makes me wonder if you truly do understand normalization. So, one record (line) of your text file will NEVER be exactly equal to another line, is that it?

If the answer is no, then it is not necessarily normalized.

 

[Starbucks_33]

I dont really understand normalization. Unfortunately every line (row) is unique because these are policies and each policy has a different policy number. There are just a lot of indicators that are associated with each polciy number.

 

[boblarson]

I dont really understand normalization. Unfortunately every line (row) is unique because these are policies and each policy has a different policy number. There are just a lot of indicators that are associated with each polciy number.

Just because each policy has a different number, doesn't mean that the other fields are unique to it. So, likely is that some, if not most, of that information should be in other tables anyway.

But, I think the first thing is that you are going to need to put at least two tables together to do the initial import and then you will need to use text processing code to do the work. By that I mean you will need to do something like this:

Dim strFile As String
Dim strValue As String
Dim varSplit As Variant
Dim lngRec As Long
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
 
strFile = "YourFilePathAndTextFileNameHere"
 
Open strFile For Input As #1
 
Input #1, strValue
 
varSplit = Split(strValue,",", , vbTextCompare)
 
Set rst = CurrentDb.OpenRecordset("Table1Name")
Set rst1 = CurrentDb.OpenRecordset("Table2Name")
 
For lngRec = 0 to UBound(VarSplit)
   If lngRec < 255 Then
      With rst
             .AddNew
             .Fields(0) = varSplit(0)
             .Fields(1) = varSplit(1)
             .Fields(2) = varSplit(2)
         ' ...etc.
             .Update
      End With
  Else
      With rst1
             .AddNew
             .Fields(0) = varSplit(256)
             .Fields(1) = varSplit(257)
             .Fields(2) = varSplit(258)
          ' ...etc.
End If
Next
 
Close #1
rst.Close
rst1.Close
Set rst = Nothing
Set rst1 = Nothing

And that would basically be the starting point.

 

[boblarson]

I don't have time to fix the code at the moment but just know that it is not totally right as I forgot to include a way to get to the next line of text file.