Also posted in the Excel Programming group but not sure where the answer lies.

Hi guys,

I hope you can help me with this puzzler.

My workbooks create a dataset ready for export to Access, essentially about
150 columns of information where the first row contains the field headers
that match the field names in Access.

Everything works fine unless the first 11+ cells in a column are blank. If
this is true then the transfer assumes that the entire column is blank and
misses out the data that is there.

Using XP office and 2003; I've included the connection coding in case
there's a clue there.

   Dim strName As String
   Dim varDbFilename As Variant
   Dim strDatabase As String
   Dim strUniqueCode As String
   Dim cnnXL As New ADODB.Connection
   Dim cnnAC As New ADODB.Connection
   Dim rstXL As New ADODB.Recordset
   Dim rstACProducts As New ADODB.Recordset
   Dim rstACCountries As New ADODB.Recordset
   Dim i As Integer
   Dim lngProductID As Long
   Dim arrCountries() As String
   Dim intInputRows As Integer
   Dim intOutputNew As Integer
   Dim intOutputUpdated As Integer
   Dim blnNewDatabase As Boolean
   
   strDatabase = Range("database_path")
   arrCountries = Split("UK,FR,SP,IT,Online", ",")
   Worksheets("DataForExport").Range("A1").CurrentRegion.Name = "ExportData"
   ActiveWorkbook.Save
   strName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
   cnnXL.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & strName & ";" & _
              "Extended Properties=""Excel 8.0;HDR=Yes"";"
   rstXL.Open "SELECT * FROM [ExportData];", cnnXL, adOpenStatic,
adLockReadOnly
   cnnAC.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & strDatabase & ";"
   rstACProducts.Open "SELECT * FROM tblProducts", cnnAC, adOpenDynamic,
adLockOptimistic
   rstACCountries.Open "SELECT * FROM tblCountries", cnnAC, adOpenDynamic,
adLockOptimistic
   cnnAC.BeginTrans
   rstXL.MoveFirst

Thanks guys.