Data type conversion error with linked table in Access to SQL Server 2005
Posted on 2009-05-20
I am currently running a macro, where you select a directory, and it produces the entire directory listing including path, file name, file type, date created, and file size into a table in access. I linked this table to a table in SQL Server 2005. It was working fine, however, when it came across a .exe file with a file size of ~30,009,000,000, I received an error "data type conversion error", at the point in the code where it assigns the record size (rs("fileSize")=filName.size). In debugging it, I know the number that won't get inserted, so I tried converting it to a string and inserting it that way, but got the same error.
I tried doing this in just Access, and it inserted fine into an Access table, so I think it has to do somehow with SQL Server.
Also, I changed the data type to varchar(255) and varchar(max), and converted the file size to a string, and it still would give me the same error.
Finally, I divided it by 100, and that seemed to make it small enough to fit. However, I want the actual file size, and I have no idea why it is that I can't insert a string of this size into my linked table. As I said, it works fine in just Access, but won't insert properly into my SQL Server linked table.