Unknown Field name

[Design by Sue]

I am giving the user the ability to import a table from excel and trying to cover all of the possibilities of import error with custom message boxes. I have most covered but have a tricky one. If the user imports a table that has records that are null I need to detect this (ie. if the user goes into excel and deletes all of the records in the file (say there were 100 records) and saves it, excel seems to keep the records, but removes the info in them - leaving all the records with nothing in them (100 blank records), and when imported into Access, there are the 100 records - with nothing in them) I would like to stop the import if the fields in the records are blank. I can use the first column to test this on as there must be something in all records for the first column. Because the import may have been completed without headers, I can't use a coded header name. What would the code be to refer to the first column in a table without using the header?? (what would replace column1)

My code so far is as follows:

Dim ColCount As Integer
ColCount = (DCount("*", "MyTable", "IsNull(column1)"))
If ColCount < 2 Then
MsgBox "The Excel file you are trying to import does not have any records." & Chr(13) & "You must obtain/select a correct Excel file before rerunning this import.", vbExclamation, "Excel File Incorrect"

Exit Sub
End If

 

[spikepl]

Why don't you try it and see? That's faster than typing quesitons here. In fact I doubt you'll ever get as far as your code, if you are using TransferSpreadsheet.

 

[CJ_London]

rather than importing directly into a table using transferspreadsheet acimport, have you considered linking to the spreadsheet using transferspreadsheet aclink to a temporary table, then you can write an append query which can filter out blank lines.

 

[GinaWhipp]

I'm confused by...

ColCount = (DCount("*", "MyTable", "IsNull(column1)"))

Are you checking the Table in Access or the actual Excel file?

 

[Design by Sue]

Gina - yes I am checking the table in Access
spike - What? I don't understand your comment?
CJ - No - what I have already is correct for my usage, thanks. I just want to check for an empty table (which is not actually empty because the fields must have something in them to show the record count)

If I try to import a totally blank excel file that gets caught by error trapping of the 3673 code.

 

[GinaWhipp]

My thought was why not check the spreadsheet to see if it has data and if there are no records then stop the whole process. But I guess you could do it this way. Your safest bet is to import into a Temp Table then you could the DCount to check if the field has any records thereby cancelling the import.

 

[Design by Sue]

Gina - Either way would work for me - but what I am trying to do with the code in my original post is your second choice - but the code does not work. I get the error code 2417, "The expression you entered as a query parameter produced this error 'column1'" So I am asking what is the proper way to refer to a column (field) in the table if the name is unknown.

 

[GinaWhipp]

If you are using a Temp Table how could the field be unknown? The Field is static in the Temp Table.

 

[Design by Sue]

OK so maybe I am asking the incorrectly - I just need to check to see if the records in the first column are null, or blank - no matter what the name of the field is. I would expect that there is a way to do this, but maybe not.

 

[GinaWhipp]

Try...

DCount("NameOfAFieldInTable", "TableName") = 0

 

[Design by Sue]

Thanks - but I do not know the name of the field - it may change with whatever the user imports.

 

[GinaWhipp]

Well, then you don't have a Temp Table because in the table the Field would be static. Well, this could be tricky... do you know the name of the Table?

 

[MarkK]

Well, a DAO.TableDef has a Fields collection, so you get a field name by ordinal using something like . . .

Function GetFieldNameByOrdinal(TableName as string, Ordinal As Integer) As String
   GetFieldNameByOrdinal = CurrentDb.TableDefs(TableName).Fields(Ordinal).Name
End Function

Or if you just want the first field as an object . . .

Function GetFirstField(TableName as string) As DAO.Field
[COLOR="Green"]   'this, actually, might go out of scope, not sure[/COLOR]
   GetFirstField = CurrentDb.TableDefs(TableName).Fields(0)
End Function

. . . and then you can do a DCount() like . . .

DCount(GetFirstField("TableName").Name, "TableName") = 0