Solved DoCmd.RunSavedImportExport for linked Table (1 Viewer)

[Weekleyba]

I have split database that has a Import button the executes the DoCmd.RunSavedImportExport command which first runs a query to delete data in the table to be imported and then executes the saved import.
The saved import take a MS Excel cvs file and imports the data to single table in Access.

My problem is, when it imports, it does so directly into the FE, loosing the link to the BE file.
The BE file data is deleted, as the code works for that.

Is there a way to fix this?

 

[Gasman]

Not sure how you did that, as you need to specify the table that will receive the data?

 

[NauticalGent]

As Gasman is suggesting, it sounds like the savedimport was made before the DB was split, therefore it will ALWAYS import in the FE.

 

[Pat Hartman]

The old import method using TransferText is more flexibile.

 

[Weekleyba]

As Gasman is suggesting, it sounds like the savedimport was made before the DB was split, therefore it will ALWAYS import in the FE.

Yes, that is correct. So now I see why it doesn't work.
Now....I'm stumped on how to fix it.

Here's what I'm trying to do.
Our admin downloads a large Excel csv file from another program.
My database imports the new data from the csv file via the Import command button.
Prior to splitting the db everything was working fine since we were accessing same database.
Now I've split the db and it's saved import is doing what it should, ie importing a table into the FE, but it's of course that's not what I want.
I want the Import button to import the new data to the BE.
How can I do that?

 

[Gasman]

Just recreate the import steps using the correct table and overwrite the import spec.?
@isladogs does have some code here somewhere that allows you to modify those specs, but I think it would be just as easy, if not eaiser and quicker to just recreate the import spec.?

 

[NauticalGent]

Just recreate the import steps using the correct table and overwrite the import spec.?

 

[Weekleyba]

For some reason I'm not tracking here.
If I go through the steps to Import, it does not import to my BE but rather to my FE.
I'm trying to get the FE to import the table in the BE.
Below is what I have.

VBA for the "Import" button circled in red.

I think the problem is this is not able to import the data from the csv file to the Back End database but, rather only to the Front End.

Maybe I'm blind now from staring at this so long....

 

[Gasman]

Start the import from scratch.

 

[NauticalGent]

Put another way, delete the saved ImportSpec(Saved Import) that you have and build it from scratch being sure to save it as the same name.

 

[theDBguy]

Hi. You could also leave it as is and just append the data from the imported FE table into the empty BE table.

 

[Weekleyba]

I’m on the road right now but I’ll be able to look at it again later tonight and tomorrow. But when I try to redo the import it gives you three choices. Import, append, link. I am trying to import data from a text file CSV. The only option that allows you to save the import is the first option, import. But every time I re-created it it imported table directly into the front end and now it’s no longer linked and also it did not update the backend. The other two options do not allow you to save the import. At least that’s how the wizard was handling it for me.

 

[Gasman]

When you import it asks you what table to append to?
Or just use theDBguy's idea and transfer from that table to your BE table

 

[Pat Hartman]

Importing/deleting/importing to the FE will just bloat it so I wouldn't leave it as is.

Link to the external file rather than importing it.
Then run an append query that copies the data from the external file to the table in the BE.

This method also bloats the BE so again, I wouldn't do it. When I have external data that needs to be constantly refreshed, I either leave it as a linked table or I use a tempBE database that I create from a template. Create a new db with just the tables that you are going to replace. Remove any data if you built the table with an import. Then compact the db and name it TEMPLATE_tempBE.

To start your import process copy TEMPLATE_tempBE to the directory where you want it to be and rename it to tempBE.
Link the FE to tempBE. Now you're good to go. The links remain even though you constantly replace the tempBE.
Link to the external file
Append to the table linked to the tempBE.

 

[Weekleyba]

To start your import process copy TEMPLATE_tempBE to the directory where you want it to be and rename it to tempBE.
Link the FE to tempBE. Now you're good to go. The links remain even though you constantly replace the tempBE.
Link to the external file
Append to the table linked to the tempBE.

So Pat I don't quite follow. I get that my FE table is linked to the tempBE table, but how is the tempBE table updated?
How then does the tempBE table get updated from the new csv file?
How does the "Append to the table linked to the tempBE" work?

 

[Weekleyba]

When I link the external csv file to the tempBE table, I get a different looking table. See below.
The link then to the FE, no longer works.

 

[Weekleyba]

I think I'll give up on trying to link it.
I'll just have each FE import the file when they open the database.
Even though the file is 16MB (125K rows), it will still work.
It's not live, but the csv file it's linked to, is only generated once a day at the most.

One of the complications involves a column in the csv file that has numbers only for the first 10,000 rows then has number/letter combinations.
Append a copy of the records to the table, via the wizard, does not allow you to change the format from numerical to short text. So it tries to import as a number but throws an error when hits the number/letter data. Then it kicks these out via an error table and renders the data useless since the queries need this field to work.

Thanks for all who helped me.
If you have any other thoughts, let me know.

 

[Gasman]

When you link to the CSV file, it is up to YOU to specify what each field type is?
Use the Advanced button

 

[Weekleyba]

It worked!
Maybe I spent two much time on it last night...
When you Import a text file and chose Append, you cannot change field type. It just does not show up. See below.
But for Import and Link, you can change the field type.
The way I have it set up now, is table is linked to the csv file.
Now the admin can replace the file (same location; same name) and it maintains the link.
No more clicking a Import button on the form. I like it.
Thank you guys for hanging with me!!!
Sure do appreciate it.

 

[Pat Hartman]

Glad you got it working Thanks forthe assist @Gasman