Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Stopping TransferSpreadsheet when there is no data.

Posted on 2004-03-24
12
Medium Priority
?
373 Views
Last Modified: 2012-08-14
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
Comment
Question by:millern2
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 10668195
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
 
LVL 17

Accepted Solution

by:
walterecook earned 150 total points
ID: 10668226
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
 
LVL 32

Assisted Solution

by:jadedata
jadedata earned 150 total points
ID: 10668337
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Expert Comment

by:jadedata
ID: 10668342
and a "top 'o the mornin'" to you walt!
0
 

Assisted Solution

by:IronLiver
IronLiver earned 150 total points
ID: 10668819
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
 

Author Comment

by:millern2
ID: 10669428
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
 
LVL 17

Expert Comment

by:walterecook
ID: 10669505
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
 

Author Comment

by:millern2
ID: 10669724
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
 

Expert Comment

by:IronLiver
ID: 10676036
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
 
LVL 17

Expert Comment

by:walterecook
ID: 10676710
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
 
LVL 32

Expert Comment

by:jadedata
ID: 10677894
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
 

Author Comment

by:millern2
ID: 10694647
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question