Refreshing Linked Tables

[mikekay]

Hello,

I was wondering if someone could point me in the right direction on how to go about this. Im programming an access database and I have an excel spreadsheet that is automatically updated using ODBC. Is there anyway that I could keep the excel sheet linked in access but have like a macro or update button, that would go into the spreadsheet update the data. Because I know that you can get external data and link to ODBC directly, but my fear is overwriting sensative data.

Thanks in advance.

I was almost thinking a macro that would open the location hit refresh close and come back to Access. Or is there an easier way?

MikeKay

[orange]

The following code should work fine. You could run this
from a startup form



sub RefreshMyExcelTableLink()
Dim tdf As DAO.TableDef

Set tdf = Currentdb.TableDefs("TheNameOfYourExcelSheet")
tdf.RefreshLink
Set tdf = Nothing
end sub

[mikekay]

Ok this will work even if its Excel(ODBC) > Access?

I haven't specified any login info in access.

Thanks in advance

[orange]

Ok this will work even if its Excel(ODBC) > Access?

I haven't specified any login info in access.

Thanks in advance

If you have created the link in Access, the login info etc has been recorded.

I would try this revised code to make sure it works for you.
I just tried it and it seems to work fine for me.


Sub RefreshMyExcelTableLink()
Dim db As DAO.Database 'added specific reference
Dim tdf As DAO.TableDef
Set db = CurrentDb 'set specific reference
Set tdf = db.TableDefs("LinkedXLS") 'my linked xls table name
tdf.RefreshLink
Set tdf = Nothing
End Sub

[mikekay]

The code you are specifing works to update the linked Excel sheet. But the problem is deeper than that. Ok my firm has set up an ODBC pull to Excel file. I then make a form for Access and *link* the Excel file to my form. Now if new items are posted, I was Access to login to the excel update and then update the tables... I dont want to set up a link from Access to ODBC because Im afraid I will modify info. Please let me know if there is code that will do this.

Thanks!

Mike

[orange]

If you are linking to Excel, you get the latest data in the excel sheet.
If you are linking to Excel, and then trying to get the updates and save them into Access, you are really looking at some form of import.

What are you really wanting to do?
Link gives you access to the latest excel data
Import gives you a copy of the data at the time of the import- any updates after the import are not reflected in your imported data