Import Excel file in Database using VBA (learning purpose) (1 Viewer)

[tomasm]

No, if you change the way you link, you get an error when the file is MIA.

I decided to test this, out of curiosity. I can only duplicate the "bug" in two scenarios: 1) attempting to open the linked table in design view when the linked file is missing, and 2) opening any form that has a subform, making design changes to the form, then saving.

First scenario is unsurprising and Access generates an error message as well. It's still odd that Access creates a blank Excel file, even in this scenario.

I tried many other ways to duplicate the "bug" and the only time it happens is when saving design changes to a form. But it has to be a form that contains a subform. And the form needs to be opened first, then make a design change, close and save when prompted. Then Access will recreate the linked Excel file in its original location with the same filename but no data in the workbook. This happens with forms that have no connection to the linked table and with forms where both form and subform have no code.

The lesson here is don't keep a table saved that links to an Excel file if you know that the Excel file will be deleted or moved later.

Edit: attempting to open the linked table will cause the bug too

 

[Mark_]

2nd to Pat's response.
Queries allow you to also do some clean up on data in the query def to better facilitate import / export of data. Really handy to be able to proper case names and such when you keep getting users who DON'T KNOW WHAT THE CAPS LOCK KEY IS OR WHAT IT DOES.

 

[tomasm]

I have one saved query object that uses the deleted linked table. So far I'm not having any problems with C&R breaking the query or breaking anything else.

I'm using the code I posted in post #21 above. The linked Excel file is only used in an append query and then the linked table object is deleted. I don't bind the workbook to any forms or do anything else with it.

My process is working fine now and I've used it several times since changing the code last week. The test I did yesterday morning was with a backup file from two weeks ago with old code that changed the link of a saved table.

 

[tomasm]

I have a form with a button on it that creates a linked table, runs a query to append the linked table data to a local temp table, then deletes the linked table. I'm using Access to modify an Excel file and export right back to Excel. The process isn't related to the rest of my database.

I append from a linked table to a local table so I can specify the data types that I want to use, rename fields, and exclude any blank rows that might be at the bottom of the Excel worksheet. I got the idea from your post Pat in this thread and it's working well.

This is a unique scenario and in the rest of my database I don't use TransferSpreadsheet acLink. In the two other instances where I import from Excel, I use TransferSpreadsheet acImport and I make sure the data is good in Excel before importing to my Access tables.