Saved Import Export Executes but Creates new local table

Question

Hello All,

I am trying to use a saved import routine in an Access 2010 database (in a VBA Sub: DoCmd.RunSavedImportExport "Import-XXX"); the routine was created by a user who is no longer available. My research on this subject indicates that one can neither view details nor edit these saved imports - oh, well. I understand that the only path is to recreate, so OK.

Here's the issue: when designing the steps, I specify a table where to import the data - the table specified is a linked table to a backend Access database. I'm trying to do this in the front-end database - the one with the links to the back end tables.

The import executes fine - the only problem is that once complete, it has created a new local table of the same name in the front end (and obviously overwrites the reference to the linked back-end table). Since the front end is used by multiple users, this behavior is unacceptable - not to mention frustrating.

Am I missing something? Or is this a "feature" of Access 2010.

Any help would be most appreciated, Thank you.

Steven

Answer 1

Here's the issue: when designing the steps, I specify a table where to import the data - the table specified is a linked table to a backend Access database. I'm trying to do this in the front-end database - the one with the links to the back end tables.   

AFAIK, we can't import into an existing Table regardless whwther it is a local or linked Table.  Quoted from Access Help:

"What happens when importing data.

Importing data creates a copy of the information in a new table in your Access database or Access project. The source table or file is not altered in this process.

When importing data, you can't append data to existing tables (except when importing spreadsheet or text files). However, once you have imported a table, in an Access database you can perform an append query or in an Access project you can use a stored procedure or an append query to add the table's data to another table."

The common method to add records into a existing Table (regardless whether the Table is local or linked) is to use an APPEND Query (which can be executed by Macro or VBA).  

For some complex databsources, we may need to use VBA code to massage the data items and insert the massaged items as records into the destination Table, one record at a time.  For example, a Text file that has extraneous text components to be eliminated and only some components to be added as records into the destination Table.

---

Van Dinh

Answer 2

Thank you for the response.

Steven

---

Steven

Answer 3

Thank you for your earlier response; not to be a PITA, but can you suggest a methodology to track down and eliminate key violations which prevent the appending of data from one table to another? I have tried to delete fields from both sides of the INSERT statement one by one [INSERT (...) SELECT ...], but still getting key violations.

I appreciate any assistance - frustration level is expanding exponentially...

Steven

---

Steven

Answer 4

I tend to try to write the Append Query/INSERT SQL so that it will avoid trying to append those rows that cannot be appended.  This really depends on your Table and what you try to avoid.

For example, let's say I have tblSource that I need to append to tblDest and both Tables have the Field [UniqField] which cannot have duplicate in the destination. If I know that it is possible for tblSource to have values that are already in tblDest, I would eliminate records from tblSource in the INSERT INTO ... like:

INSERT INTO tblDest
( UniqField, Field1, Field2, etc...)
SELECT S.UniqField, S.FieldA, S.FieldB, ...
FROM tblSource AS S
LEFT JOIN tblDest AS D ON S.UniqField = D.UniqField
WHERE D.UniqField Is Null

The SELECT cluase will on select rows from tblSource whose [UniqField] values don't exist in tblDest and therefore the failure due to duplicated values will be avoided.

 

---

Van Dinh

Answer 5

Thank you so very much - I will try that.

S

---

Steven