Data type conversion error with linked table in Access to SQL Server 2005

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.

Any thoughts?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

THis is a brute-force work-around but, since it loads the data into an Access table okay, why not pull it into an Access table as a staging table and then, in the macro, run an append/insert query to copy it inot the SQL Server table.  (I's ugly and a kludge but it will probably work. ;-)
In the mean time, how are you getting the data that you are inserting into the table.  I know that you said you are "running a macro" but that is rather vague . . . what exactly are you executing to get the data?  It may be that the datatype that is being returned is somewhat variable depending on the size of the file.  If so, it may be that the large file size is being returned as a datatype that doesn't have an implict conversion to the datatype you are trying to store it in.
lew105Author Commented:
here is a link where I found the vba.

I'm not sure how to explain how I'm getting the data, but it's fairly simple in this code.  I don't have Access on my Mac, but can point out the specific part tomorrow morning if you're having trouble
Sql maxium Integer size = 2,147,483,647
Sql maximum BigInt size = 9,223,372,036,854,775,807
If the SQL data field's data type is set to Int change it to BigInt
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

lew105Author Commented:
I tried big Int, as well as converting it to string and making the field varchar(max).  Both returned the same error.
The ~ may be the problem.  Can you adjust the value in the macro?  I think you had the right idea in dividing by 100 or maybe 1000, but it probably needs to be done before it gets to the table level.
lew105Author Commented:
I apologize.  I just used the '~' b/c I don't know the exact size but it was somewhere around 30 billion
lew105Author Commented:
I get the error on the line:
rs.Fields("fSize") = filFile.Size

Runtime error '3421': Data Type Conversion Error

In debugging, filFile.size =  30009720876

Set fsoSysObj = New Scripting.FileSystemObject
    Set fdrFolder = fsoSysObj.GetFolder(strDir)
' Lets get the files from the base directory
    For Each filFile In fdrFolder.Files
                rs.Fields("fName") = filFile.Name
                rs.Fields("fDir") = strDir
                rs.Fields("fSize") = filFile.Size
                rs.Fields("fDate") = filFile.DateLastModified
                rs.Fields("fType") = filFile.Type
  Next filFile

Open in new window

Is rs.AddnEW using an Access table or SQL table?

What if you"fSize") = filFile.Size/1000 just to see if you could get it to work?

Or, what if you converted filFile.Size to a string...and do it on the right side of --- rs.Fields("fSize") = filFile.Size
lew105Author Commented:
I figured it out.  Basically, I had to set this up properly and re-link the table.  Just changing the data type in SQL Server is not enough.  You also have to refresh the link. Thanks

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I had forgotten about that.  You are right.  With SQL tables linked to Access, data type changes in columns do not show up until you relink.  Sorry we didn't help you think of that sooner.  Glad you figured it out.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.