TransferSpreadsheet :: Field "F1" doesn't exist in the destination table

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

MRdNk

Thread Starter
Joined
Apr 7, 2007
Messages
439
Hi All,

I'm attempting to import data from an Excel spreadsheet into Access. As I'm just trying to get it to work first and then properly attempt to use it, I've simple exported my table "tblTenants", and renamed it Import.xls and changed one column of data.

Code:
Public Sub ImportExcelData()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTenants", "C:\Documents and Settings\Greg Webster\My Documents\Property DB\ImportFiles\Import.xls", True, "A3:I5"​
End Sub
However, I get the following error:
Field "F1" doesn't exist in the destination table "tblTenants."
And I don't know why, please help.
 

MRdNk

Thread Starter
Joined
Apr 7, 2007
Messages
439
Hmmm, if I create blank lines for rows 1 and 2 it seems to import properly.
 

jimr381

Jim
Joined
Jul 20, 2007
Messages
4,193
Import copies and pastes it onto the end of the data. It does not overwrite it. Also the reason why it works within row 3 down is that you have specifying the data to start from A3 down. ;) It is probably assuming that row 3 contains the headers and the other rows contain the data.
 

MRdNk

Thread Starter
Joined
Apr 7, 2007
Messages
439
Oh yeah :rolleyes: - "A3" - now I feel like a real tit!

I want it to update the data rather than add new stuff to the bottom.
TRIED to use an SQL UPDATE statement based on something I found on Google, but it doesn't like it:

UPDATE importTable
INNER JOIN tblTenants
ON importTable.TenantID = tblTenants.TenantID
SET tblTenants.HomeTelNo = (
SELECT importTable.HomeTelNo
FROM importTable
WHERE importTable.TenantID = tblTenants.TenantID)​

WHERE Exists
(SELECT importTable.HomeTelNo
FROM importTable
WHERE importTable.TenantID = tblTenants.TenantID);​
 

OBP

Joined
Mar 8, 2005
Messages
19,895
I always use a Transfer Spreadsheet or Import Data to create a Temporary Table and then use an "Append" Query to Add new Data and an "Udate" Query to update existing records.
You do not need to understand SQL to create those, but once created you then have the SQL for future reference.
I also use VBA Recordsets for more advanced data "Conversion" on the Temporary table when required.
 

MRdNk

Thread Starter
Joined
Apr 7, 2007
Messages
439
I've now made an UPDATE query that works at a basic level, I guess if I programatically do the whole query and have my importTable spreadsheet only have updated information, and program in if value IsNull then do nothing else update field, I'll get the end result that I want.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top