Export from XL to Access via ADO

[Gizmo63]

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.

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.

 

[arno]

Hi Gizmo63,

Everything works fine unless the first 11+ cells in a column are
blank.

I know this sort of problem if Excel Files are used as ODBC Databases.
In ODBC properties you can set how many rows (records) should be
scanned to identify the datatype of the fields etc. This fails
offcourse if the fields are empty. The standard is 8 record. This might
be your problem.

So, maybe there's an option for your connection string.

Or you might adjust your sql-string to filter empty records. A
workaround like

select * from (select * from mydb where myfield is not null)

arno

 

[Gizmo63]

Thanks for the pointer Arno,

It makes sense that there may be some limiter and I'll need to do some
research into the coding but at least I've got somewhere else to look.

Giz