• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

Stopping TransferSpreadsheet when there is no data.

I am producing a time recording system and need to import variable amounts of data from excel spreadsheets.
If there is no data in column B, I need it to move on to the next command.
column B is the only column that cannot be empty in the table. At present my database is importing the blank rows and then I'm using a query to delete the extra data, but this is messy.

Any ideas are welcome, the code I'm using at the moment is below.

' This populates the Time Breakdown form
DoCmd.TransferSpreadsheet acImport, 8, "Time Breakdown", strPath & strFile, True, "Main!B9:F50"
' This will delete all the blank rows
DoCmd.OpenQuery "Q_DeleteBlanks", acViewNormal, acEdit
' This should update the ID field in the "Time Breakdown" table
DoCmd.OpenQuery "Q_Timesheet", acNormal, acEdit
' This will delete the Error Table
DoCmd.DeleteObject acTable, "F50_ImportErrors"
0
millern2
Asked:
millern2
  • 4
  • 3
  • 3
  • +1
3 Solutions
 
jadedataMS Access Systems CreatorCommented:
You will have to pre-test the conditions of an identical recordset before running the transfer command in order to do this.
TransferSpreadsheet can not be stopped once launched.
0
 
walterecookCommented:
Jack is correct
(morning Jack)
I suppose your other option would be to import into a "temporary" table that allows it to be blank, then move to your "final" table via an append query so checks for this sort of thing.

Walt
0
 
jadedataMS Access Systems CreatorCommented:
Linking to the table and running

set rs = currentdb.openrecordset("Select Count([AnyField]) as RecCount from TableName",dbopensnapshot)
if nz(rs("RecCount"),0)>0 then
  'it's good!!
endif
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
jadedataMS Access Systems CreatorCommented:
and a "top 'o the mornin'" to you walt!
0
 
IronLiverCommented:
I have something similar to this in my db and I import to a temporary table just as Walt suggests. In this table I assign things like ID numbers and formatting etc. to imported data. When I have all the data nicely formatted I append it to the target table.

There is a stage at the beginning of the process that checks if there is any data in the 'Import table' (the one that the TransferSpreadsheet dumps its excel data in) using a simple DCount statement as follows:

If DCount("FieldName","TableName") = 0 Then
      'there is no data so stop the procedure
     Exit Sub
Else
     'The rest of the process goes here and runs if there is data in the table
End If

I do this in case users have tried to upload one of the empty excel templates in error. It means that the don't have to sit through the formatting process, which can be quite lengthy.

It's pretty basic but it works quite well and is easy to slip in between your DoCmd.OpenQuery things

Cheers

0
 
millern2Author Commented:
My database is producing error tables when it imports the blank rows, even with a temp table - how do I get the table to accept NULL values?
0
 
walterecookCommented:
In the table design, you'll need to change the properties of the fields to show Required - No, and allow Zero length strings - Yes.

0
 
millern2Author Commented:
sorry, I meant in a temporary table - since it isn't there to set up and it will be deleted afterwards.
I guess I would have to write some Create Table code.
0
 
IronLiverCommented:
Why not just leave the temp table there (I supposed it would just be an import table then instead of a temp table) and empty it (DELETE query) before you need to import fresh data?

If this is a regular task then it will save the hassle of creating a table in code with specific field properties.
0
 
walterecookCommented:
Yes like Iron liver says ( I guess we weren't clear on that) leave it there.  Constantly creating and deleting tables is not healthy for your database.
OR
like Jack said, you could link to the excel file directly

Walt
0
 
jadedataMS Access Systems CreatorCommented:
I don't create anything I don't have to create,  ...
 the data is already there and readable as a table via a link, which creates nothing but a reference to another object.
 the sql to test for "dead" records can create a virtual recordset and still we have no persistant objects in play other than the wkrsht.

just seems right to me...
-j-
0
 
millern2Author Commented:
Sorry I took so long to get back to you - lost my internet connection.
I can't link to the excel file because the file keeps changing, and it's importing from multiple files anyway.
I tried the import table idea but it didn't solve the problem of the error table that the database keeps creating - which I've now discovered is down to the hidden formula in column B returning a NULL value.
So I think I'll keep it pretty much the way it is. Thanks for all your suggestions though, sometimes I look for too complicated solutions to problems.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now