Solved

Stopping TransferSpreadsheet when there is no data.

Posted on 2004-03-24
12
363 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 50 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 50 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
 
LVL 32

Expert Comment

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

Assisted Solution

by:IronLiver
IronLiver earned 50 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now