Missed data export from XL to Access via ADO

[Guest]

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.

 

[Tom Wickernards]

you should be using SQL Server and a real ETL tool like DTS for example

 

[Guest]

Tom,

Scanning through your posts you spend your time posting one liners trashing
Access and telling us 'kids' to move to a "real" environment like SQL.

OK; maybe SQL is a long term solution; but you're doing nothing to help
solve folks immediate problems with the systems they actually have.
So maybe you'd like to impart some of your useful knowledge to help resolve
my data transfer issue.........

you should be using SQL Server and a real ETL tool like DTS for example

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.

 

[Guest]

You shouldn't be using a computer at all. You are not intelligent enough to
know what you are talking about.
--
Dave Hargis, Microsoft Access MVP

you should be using SQL Server and a real ETL tool like DTS for example

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.