locked
VBA to SQL Server - not all records load RRS feed

  • Question

  • I have an Excel VBA macro that passes data into a SQL Server database table.  It will not pass all of the data.  It stops at 504 records out of 548.

    The entire macro works with fewer records.  Is this a connection string property?  I read something about an extended property that controlled memory, but it applied to earlier SQL Servers.

    I am using Excel 2007 and SQL Server 2012.


    EPG48

    Thursday, May 26, 2016 4:55 PM

All replies

  • It sounds like the data types don't match up.  Excel allows any kind of data in a column, so you can have text in a row and numbers directly below, or above that, in the same column.  SQL Server requires you to set the data type, and the data MUST conform to that type.  Look closely at these things and it should be obvious where the disconnect is.


    MY BOOK

    Thursday, May 26, 2016 5:31 PM
  • I've used SQL Server 2012/2014 and Excel 2010.  I never had a problem.  I suspect you may have an invalid character in the data or similar problem.  Can you load the data directly to the server using SSMS?
    Thursday, May 26, 2016 5:39 PM
  • Thank you.  Yes it was a database issue.  There were two entries with the same name.

    I would never have found that without your guidance.


    EPG48

    Thursday, May 26, 2016 7:23 PM