delete linked table

[qwertyjjj]

Is there a way to remove a link or delete a linked table in Access (via VBA).

I am importing a spreadsheet but the linked tables are not overwritten/refreshed. Instead, Access automatically renames them by sticking a 1 on the end.

 

[KenHigg]

Would it be more efficient to leave the table in place and have your routine delete the old content and then append the new data?

 

[qwertyjjj]

Possibly, but being a linked table, I'd rather not delete any data as it will delete the data in the spreadsheet won't it?

 

[KenHigg]

Hum... I was thinking that if when you get a new version of the spreasheet, you give move / delete the old spreasheet and replace it with the new, giving the new spreadsheet the same name as the old. Then when access opens, it will automatically see the new data. Then your routine can append this new data to a master type table with all the old and new spreadsheet data...

Hope this makes sense...

 

[qwertyjjj]

The problem is that at present, the user has to press a button to import the data. This does a whole load of stuff including shifting other data about into other tables, etc.

Are you saying that if I do nothing with the links, they will just remain and any data from the linked tables will automatically refer to the new spreadsheet?

I suppose that would work.
Just for completeness sake though i wanted to unlink the tables from the Access database after they have finished being used so that the user never knows they were there.

 

[ghudson]

I suggest that you add an import file routine to your db. Import the file into a temp table, then append and/or update the data to the main table. That allows you to do error and data validation checks before you actually append and/or update the data to the main table. Lots of samples floating around this forum on how to do it.

 

[Adeptus]

Are you saying that if I do nothing with the links, they will just remain and any data from the linked tables will automatically refer to the new spreadsheet?

If the new spreadsheet has the same name as the old, yes.

I suppose that would work.
Just for completeness sake though i wanted to unlink the tables from the Access database after they have finished being used so that the user never knows they were there.

The users shouldn't be allowed to see the tables anyway!

 

[Accident Man]

Try this:

DoCmd.DeleteObject acTable, "Link Table Name Here"