Lost Records

[Hydra]

I have code that creates an Access database from and excel file and reads in
all the records. My Exlcel file has 1500 records but when I open the database
I only find around 200. Otherwise my code seems to be working. I'm guessing
that it is not writing duplicate records or not writing records with nothing
in some of the fields. What should I look for?

 

[Clifford Bass]

Hi,

Have you traced through your code do see exactly what is happening? If
you do not know how.... While in the VBA Editor choose a line at or near the
beginning of the code and click in the colored margin to the left of the
code. You will get a large dot, which is a break point. Then run your
process. It will stop at the break point and display the code. Press
<Shift>-<F8> to execute each line of code. You can hover the cursor over
variables to see their current values. For other options on stepping through
your code please seach your online help for "trace" or "tracing".

Hope this helps,

Clifford Bass

 

[David H]

Perhaps their on the island? (I'm a fan of 'Lost' and couldn't resist)

To the point though,

I'm a huge believer in designing code that imports data in such a way that
the newly created records can be identified by batch and that the original
data source documents the batch number of the record. This allows you to
easily identify which records were and were not imported if an error occurs.
Typically, I grab the current Date/Time from Now and convert it to a number
to create the Batch number as in BatchNumber = CDbl(Now()). Then as the
records are imported the batch number is placed into a field in the
destination table as well as a field in the source file.

To troubleshoot your problem, you may want to take the code that loops
through the records in the Excel file and simply write them to the screen
using Debug.Print. By placing a counter i = i + 1 and printing the value of
i, you'll be able to determine if that part of the code functions properly.
On that note, you may want to look at the condition that you're using to
determine if you've reached the end of the records in the Excel file. For
example, if you're looping through the rows checking the value in column A to
see if its empty, there might be a record that meets that condition in the
middle of the others.

 

[Clifford Bass]

Hi David,

I like your import batch number idea! I may implement it for some of
my load that occasionally have issues for one reason or another.

Clifford Bass

 

[Hydra]

I like the debug print number idea.
I was already printing the detail of each field in each item, and as faras I
can see it works OK. Howver, there are a lot of records in this test file,
and it isn't easy to determine which ones are missing.

However, there are records with a lot of empty fields. If I fill some of
them with dummy data I get more records in the final file.

Related question: When I create the fields in the database I loop through an
add statement on the recordset. All of these turn up as text fields. If I
wanted to create a certain one as a date field, for example, how do I do
that? I didn;t see any parameters or syntax in the add fieild to allow me to
do that>

 

[Hydra]

So I just added an ID field to the Excel file and populated it from 1 to
1500. When I execute the code I get the first 187 records and then it goes
intermittent: 470 to 484,595 to 597, 886 to 921, 1185 to 1192, 1241 to 1248.

Each record is obviously unique, because of the ID number, and it throws no
errors.

My next move is to repeat and see if the same records are always missing.
then put in a counter and break just before the missing ones. I don't really
expect to see anything except the cursor going around the loop.

 

[Hydra]

Sure enough, it skips the same records every time.

 

[Clifford Bass]

Hi,

I take it that you are doing the import entirely in code, including the
creation of the destination table. Perhaps this small code example will help:

Dim td As DAO.TableDef
Dim fld As DAO.Field

Set td = CurrentDb.CreateTableDef("tblNew_Table")
Set fld = td.CreateField("Double_Field", dbDouble)
fld.Required = True
fld.DefaultValue = 123
td.Fields.Append fld
Set fld = td.CreateField("Text_Field", dbText, 50)
fld.Required = False
fld.AllowZeroLength = True
td.Fields.Append fld
CurrentDb.TableDefs.Append td
RefreshDatabaseWindow

Clifford Bass

 

[Hydra]

Yes, thank you, that is what I was looking for. I was looping through a list
of names using fields Add (fieldame, dbtext), but using your method I can set
each property on the fly.

I probably onely need dbtext, dbdate, dbdouble, but now I can't figure out
how to et the type out of access. The help screen says that IsText is
available to VB but I can't get it to work. I would test the cells in the
worksheet and then create the datafield to match.

 

[Hydra]

Still don;t see why it is skipping over records.

Hi,

I take it that you are doing the import entirely in code, including the
creation of the destination table. Perhaps this small code example will help:

Dim td As DAO.TableDef
Dim fld As DAO.Field

Set td = CurrentDb.CreateTableDef("tblNew_Table")
Set fld = td.CreateField("Double_Field", dbDouble)
fld.Required = True
fld.DefaultValue = 123
td.Fields.Append fld
Set fld = td.CreateField("Text_Field", dbText, 50)
fld.Required = False
fld.AllowZeroLength = True
td.Fields.Append fld
CurrentDb.TableDefs.Append td
RefreshDatabaseWindow

Clifford Bass

 

[Clifford Bass]

Hi,

How are you getting to the data in the spreadsheet?

Clifford Bass

 

[Clifford Bass]

Hi,

How about posting your import code, an example row that works and an
example row that does not?

Clifford Bass

 

[BTU_needs_assistance_43]

Could it be excluding files that have blank cells maybe? You could try
including an IF/THEN/ELSE statement that would state IF ActiveCell.Value =
("") THEN... giving the blank or null cells some value such as "0" or "NA",
something to write into your table. Never know, might work!

- It is better to have died a thousand times without glory than to have
lived without honor -

 

[Hydra]

Set rsresources = dbs.OpenRecordset("Resources", dbOpenTable)
Counter = rsresources.Fields.Count

For c = 1 To Counter
Debug.Print rsresources.Fields(c).Name & ", " &
rsresources.Fields(c).Value
Next c

Activeworksheet.Cells(1, 1).Activate
Activeworksheet.Cells(1, 1).Select

r = 2 ' the start row in the worksheet

With rsresources

'------------Pick up the Data from the Spreadsheet
For r = 2 To nrows
Debug.Print "Add new Record"
.AddNew ' create a new record
For c = 1 To rsresources.Fields.Count
'FieldName = rsresources.Fields(c).Name
FieldName = ActiveSheet.Cells(1, c).Value
Fieldvalue = ActiveSheet.Cells(r, c).Value
Debug.Print FieldName & ", "; Fieldvalue


' -----------add values to each field in the record
.Fields(FieldName).Value = Fieldvalue

'-------------Add more fields and more values for each column in the
spreadsheet
Next c

Debug.Print "list fields and values"
For Counter = 0 To rsresources.Fields.Count
On Error Resume Next
Debug.Print rsresources.Fields(Counter).Name & ", " &
rsresources.Fields(Counter).Value
Next Counter

'-------------Udate the table with the new record
.Update

Next r
End With


Data: 21,88 1nd 189 work, 190 doesn't 484 works, 485 doesn't.


21 2.5.2.2.1.1.1 BR04 04/2011 engrtype21 EMP21 OSC21 Direct Source21 2.5.2.2.1.1.1 PM 8.00 21.00
88 2.5.2.2.1.1.1 TRAVEL 02/2010 Travel 3 2.5.2.2.1.1.1 PM 0.00 88.00
189 2.5.2.2.1.1.2 TRAVEL
AIRFARE 08/2010 Travel 6 2.5.2.2.1.1.2 Contracts 0.00 609.00
190 2.5.2.2.1.1.3 BR08 09/2009 Direct 2.5.2.2.1.1.3 Subcontracts
Administration 63.00 0.00
484 2.5.2.2.1.2.2 TRAVEL AIRFARE 05/2010 Travel 8 2.5.2.2.1.2.2 Mission
Assurance 0.00 480.00
485 2.5.2.2.1.2.3 BR06 08/2009 Direct 2.5.2.2.1.2.3 Security 86.00 0.00

 

[Hydra]

Sure enough, filling wverything with "NA" brought in all the records. That
doesn;t explain why some records with blank fields made it in ans some didn't.

In Table design, Allow zero length is set to NO, although I can now change
that using the code Clifford kindly suggested. Could that cause the problem?
Also the table is not indexed, though I'm not sure how to do that on the fly.

 

[John Spencer MVP]

Partial explanation of why some records with blank fields made it in and some
didn't.

If the field had a value and the value was deleted and the Allow Zero Length
was true, then the record ended up with a zero length string in that field.

If no entry was ever made to the field then it would have null in it.

If an update query was run to set the field in some records to blank it would
depend on whether the update to the field was set as Null or set as "" (a zero
length string).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Hydra]

Dim td As DAO.TableDef
Dim fld As DAO.Field

Set td = CurrentDb.CreateTableDef("tblNew_Table")
Set fld = td.CreateField("Double_Field", dbDouble)
fld.Required = True
fld.DefaultValue = 123
td.Fields.Append fld


OK so I'm using

DataType = VarType(ActiveSheet.Cells(r, 2))

To pick up the data Type used in Excel

Then I tried

Set fld = td.CreateField(Fieldname, DataType)

but that threw an error

Then I tried


Set fld = td.CreateField("Double_Field", dbDouble)
fld.Required = True
fld.DefaultValue = 123
fld.Type = DataType
td.Fields.Append fld

And That threw an Error


Then I set up a Case Else to convert the dataTypw to a string such that
DataType is converted to a string, and That threw an error.

(Why is vartype(ActiveCell) = 8 When Active Cell is text, but in DAO dbText
= 10?)

 

[Clifford Bass]

Hi,

I see you discovered the reason for some rows not being added. In
answer to your data type determination, you might try something like this:

Dim varCellValue As Variant

' ...

varCellValue = VarType(ActiveSheet.Cells(1, c).Value)
Select Case varCellValue
Case vbEmpty, vbNull
' No value - make sure to allow nulls and zero-length strings

Case vbInteger

Case vbDouble

Case vbDate

Case vbString
' Remember to check for zero-length strings
If varCellValue = "" Then
' Make sure to allow zero-length strings
End If

End Select

It may make sense to go through the entire Excel table checking types
in each column so you can make sure that you have the right type for the
column in Access. So if a column contains an integer value and later on a
double value, you probably will want to choose double for the column type in
Access. Unless further on in the column there is a string. And if there are
blanks, then you can make sure to specify to allow nulls and/or zero-length
strings. For strings you can determine the maximum length encountered and
use that when creating the text field in Access.

Good luck,

Clifford Bass

 

[Clifford Bass]

Hi,

Why they are different, I do not know. Some decision made by someone
at Microsoft for some reason? You will need to convert from the variant data
types to the DAO data types. This is why I suggested using the Select Case
statement. However, I got that somewhat wrong. The top part should be:

Dim varCellValue As Variant
Dim vtType As VbVarType
' ...
varCellValue = ActiveSheet.Cells(1, c).Value
vtType = VarType(varCellValue)
Select Case vtType

You will need to fill out the internals of the Select Case statement to
do the actual conversions. Do a search in the online help for "VarType
Constants" and "DataTypeEnum" to get the listings of possible values of the
types.

Clifford Bass

 

[Clifford Bass]

Hi,

Here is a modified example that includes creating a primary index:

Dim td As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index

Set td = CurrentDb.CreateTableDef("tblNewer_Table")
Set fld = td.CreateField("Double_Field", dbDouble)
fld.Required = True
fld.DefaultValue = 123
td.Fields.Append fld
Set fld = td.CreateField("Text_Field", dbText, 50)
fld.Required = False
fld.AllowZeroLength = True
td.Fields.Append fld

Set idx = td.CreateIndex("Primary")
idx.Primary = True
idx.Unique = True
Set fld = idx.CreateField("Double_Field")
idx.Fields.Append fld
td.Indexes.Append idx

CurrentDb.TableDefs.Append td
RefreshDatabaseWindow


Clifford Bass