Import Excel file in Database using VBA (learning purpose)

[tomasm]

Thanks for the reply and I hope you are doing well today.

I am trying to think of why I decided to save a linked table object and use .Connect and .RefreshLink to change the source instead of using TransferSpreadsheet. But I can't remember the reason.

It could be that I was seeing different ways of checking if a table exists before attempting to delete it (like post #7 above) and decided to use .Connect and .RefreshLink to avoid that. But I don't need to check if the table exists, and I want Access to raise an error and stop running code if somehow the temporary linked table doesn't exist when the delete line executes. And if somehow the delete fails, and the next time the sub runs and TransferSpreadsheet creates "tempLinkedTable1", that's fine too because I'll get an error on the append query.

This is working fine so far with my tests today:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, "tempLinkedTable", varFile, True
'run append query and other things here
Db.TableDefs.Delete "tempLinkedTable"

 

[Pat Hartman]

And if somehow the delete fails, and the next time the sub runs and TransferSpreadsheet creates "tempLinkedTable1", that's fine too because I'll get an error on the append query.

Again, all you need to do is to always use TransferSpreadsheet to link to the same named table. Then your queries won't break. You can first check the linked table using code that has an error trap so you can suppress the error message and just delete the link if it already exists. The reason for leaving the link table between uses is so that Access won't break your queries by changing all the column names to Expr1, Expr2, etc if you C&R while the link is missing.

 

[tomasm]

Again, all you need to do is to always use TransferSpreadsheet to link to the same named table. Then your queries won't break. You can first check the linked table using code that has an error trap so you can suppress the error message and just delete the link if it already exists. The reason for leaving the link table between uses is so that Access won't break your queries by changing all the column names to Expr1, Expr2, etc if you C&R while the link is missing.

If I leave the linked table saved in Access then I'll be back to my problem of Access creating blank Excel workbooks where the linked file used to be (that seems like a bug to me). The Excel files that I import come from a network folder and someone eventually moves those files. I could copy the file to a folder on my PC prior to running the import code, but I have no use for those files after I import them. And my revised code is working well so far and I'm not seeing any mysterious, blank Excel files appearing.

I did a compact and repair this morning, then ran my code and everything is fine. I'm looking at my append query now and it's still good. I think if I opened it in Design view that would break it. I avoid Design view for that reason and I'm glad Access finally allows me to go straight to SQL view from the navigation pane.

 

[The_Doc_Man]

Best wishes for recovery, Pat. And I admit to a bout of jealousy. But right now, my dear wife is recovering from her dislocated shoulder so we can't travel across the pond anytime soon anyway.

 

[Ken Sheridan]

If I leave the linked table saved in Access then I'll be back to my problem of Access creating blank Excel workbooks where the linked file used to be (that seems like a bug to me). The Excel files that I import come from a network folder and someone eventually moves those files. I could copy the file to a folder on my PC prior to running the import code, but I have no use for those files after I import them. And my revised code is working well so far and I'm not seeing any mysterious, blank Excel files appearing.

If you take a look at the DecomposerDemo file that I attached to an earlier reply in this thread, you'll see that, rather than linking to the Excel file, it imports the data from it into a predefined MasterTable, which is then decomposed into a set of predefined normalised tables. If, when selecting the Excel file in the dialogue I change its name to that of a non-existent file, this simply raises a File Not Found error, which can be handled in the code. No new empty Excel file is created. As the MasterTable table and the tables into which it is decomposed will then remain as empty tables in the Access file, the 'append' queries which carry out the decomposition are safe from any adverse consequences.

 

[tomasm]

If you take a look at the DecomposerDemo file that I attached to an earlier reply in this thread, you'll see that, rather than linking to the Excel file, it imports the data from it into a predefined MasterTable, which is then decomposed into a set of predefined normalised tables. If, when selecting the Excel file in the dialogue I change its name to that of a non-existent file, this simply raises a File Not Found error, which can be handled in the code. No new empty Excel file is created. As the MasterTable table and the tables into which it is decomposed will then remain as empty tables in the Access file, the 'append' queries which carry out the decomposition are safe from any adverse consequences.

The demo looks like it would be useful for that particular scenario where normalization is needed. In my case, my module to import from Excel is separate from the rest of my database and it's purpose is only to modify an Excel file and then export a new version of it back to Excel. I don't save any data from it or do any normalization. The module and related lookup tables and queries are really not related to the rest of my database. It's just convenient to have this Excel file modification tool included as a button on one of my forms.

I don't know when Access was creating an empty Excel file after the file was moved or deleted. Maybe on startup or compact and repair. Deleting the linked table each time solved that problem.

 

[Pat Hartman]

If I leave the linked table saved in Access then I'll be back to my problem of Access creating blank Excel workbooks where the linked file used to be

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

 

[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

 

[Pat Hartman]

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.

I think that is the wrong lesson to learn. I NEVER delete the table links if I intend to reuse them with updated workbooks and I've never, ever had this issue. Deleting the link will cause all of your querydefs that use the linked workbook to break if you C&R while the table is deleted. THAT is something you really want to avoid since fixing the queries is a PITA.

Did you change your method of linking to the workbook as I suggested? Personally, I would never bind a form that is linked to a workbook. I don't bind forms to tables in general but that's because the majority of my BE's are not ACE but some other "real" RDBMS.

If changing the linking method doesn't resolve the problem, then it has to do with how bound forms work and that is the lesson you should learn - never bind a form to a linked workbook. Binding the form to a query of the linked workbook might also avoid the issue but forms do stuff we simply don't know about and MS is never going to tell us.

 

[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.

 

[Pat Hartman]

I don't bind the workbook to any forms or do anything else with it.

But you said you do.

 

[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.