Solved

VBA code using SavedImportExport procedure creates a table in the wrong database

Posted on 2010-09-02
4
647 Views
Last Modified: 2013-11-27
Background: Every day we must refresh/update or overwrite Access tables with new data incoming on Excel spreadsheets.  For the most part, I'm using the Saved import/Export procedures built into Access and executing them with VBA code.  I don't know if this is the best way to bring in data from Excel but it's all I know.

Question: When I run a saved import procedure from an Access front end, a table is created in the front end instead of the backend.  Why doesn't the table refresh in the back-end instead?   Would someone pleasel ook at my code (attached).
DTR-import-code.txt
0
Comment
Question by:thutchinson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 1

Expert Comment

by:momo4kids
ID: 33589133
When creating a SavedImport using the import wizard, the data is imported into the current database, even if that database is linked to a backend database.  

Create the import in the backend and use a macro to run the import code.  You can then add the macro to your desktop and run it from a shortcut.
0
 

Author Comment

by:thutchinson
ID: 33589789
That's what I figured. However, it is not practical to run a bunch of macros from the desktop.  I have several network users that need to execute from the frontend application.
How do I execute a macro in (from) the front end to run a SavedImport that will update tables in the backend?
0
 
LVL 1

Accepted Solution

by:
momo4kids earned 500 total points
ID: 33590611
You can append the data to the existing table.  Create a query to clear out the existing data, then call the saved import and add the data to that table.
0
 

Author Comment

by:thutchinson
ID: 33590811
Thanks for your help.  I really appreciate it.
 
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 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