UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using Runsavedimportexport, Access 2013    
 
   
JV63
post Feb 14 2018, 08:07 AM
Post#1



Posts: 194
Joined: 16-August 12



Hi. I'm trying to create a macro to import a spreadsheet with just certain fields I need. I imported it manually first and then saved the import specifications. If I use a macro and have the RunSavedImportExport step first and then have it import the spreadsheet I get errors saying field X is not in the destination table. If I put the RunSavedImportExport step after trying to import the spreadsheet I still get the same message.

Must be missing an option or step or is not possible in a macro?

Thanks
Go to the top of the page
 
GroverParkGeorge
post Feb 14 2018, 08:38 AM
Post#2


UA Admin
Posts: 34,132
Joined: 20-June 02
From: Newcastle, WA


"... field X is not in the destination table."

That means there is a mismatch between the fields in the imported Excel worksheet and the table in Access where you intend to insert the records.

Review your source and destination to identify where the problem is. Apparently your table is missing at least one field that is identified in the ImportExport Spec.

You have a saved ImportExport Spec. It identifies fields as well. Make sure it identifies all of the fields correctly as well.
Go to the top of the page
 
JV63
post Feb 14 2018, 08:52 AM
Post#3



Posts: 194
Joined: 16-August 12



I just checked again to make sure I didn't miss not importing any particular field I don't want and I am skipping the fields I don't want in the import specifications. So still getting Field X not in destination table even though I checked that I am skipping that field in the import specs.
Go to the top of the page
 
GroverParkGeorge
post Feb 14 2018, 09:15 AM
Post#4


UA Admin
Posts: 34,132
Joined: 20-June 02
From: Newcastle, WA


Well Access thinks it's missing, so there's something about either the ImportExport spec that is wrong, or the table is wrongly defined....

Check a couple of things.

First, if you let Access create a NEW table from the import and save the ImportExport spec for it, can you compare the results of that table against the one you are trying to import into? How about the two specs? All identical in both the tables and in the specs?

Second, what type of file are you importing, i.e. is it comma delimited or fixed width? In other words, are fields identified by a delimiter like a comma? Or are they supposed to be a specific number of characters? I assume because you're pulling the data in from Access they would be delimited, but did you tell the ImportExport spec to do the same? Or did you make it fixed width?

And another thing to consider. Let Access import the entire source into a matching temporary table. Then create an append query to take only the fields you need from that temp table to append on into the final destination table. That is a frequently used method, in fact. It can help in another way, too. If you let Access import everything into Text fields in the temporary table, except for dates, then you can apply conversions, as needed, to put the results into the appropriate data types for appending to the final destination table.
Go to the top of the page
 
JV63
post Feb 14 2018, 09:32 AM
Post#5



Posts: 194
Joined: 16-August 12



I'm trying to import an Excel spreadsheet so it's not text or csv or anything like that. I do like your idea of importing the whole spreadsheet and using an append query. Doesn't hurt to have all the fields in a temp table in case I ever need them down the road. I'll go that route.

Thanks for the suggestion.
Go to the top of the page
 
GroverParkGeorge
post Feb 14 2018, 10:32 AM
Post#6


UA Admin
Posts: 34,132
Joined: 20-June 02
From: Newcastle, WA


Keep us posted and good luck with your project.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th December 2018 - 11:56 PM