[RESOLVED] Edit Link table connection string in Access

Right i can open a dialog to select the file to link to, and i can read the connection string for the table, but i can't get the string to be altered!

Code looks like this:

VB Code:

Private Sub Command0_Click()
    
    'Opens the dialog
    RelinkFile
    ' Old Connect String
    Text1.Value = CurrentDb.TableDefs("stafflist").Connect
    ' Change connect string
    CurrentDb.TableDefs("staffList").Connect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & sFile
    ' Out put the 'new' connect string
    Text1.Value = CurrentDb.TableDefs("staffList").Connect
    ' Check the dialog actually collected the file name/path
    Text6.Value = sFile
    
End Sub

wow.. couldnt get it to work either... odd.. it says you can set or read the value..


but.. if you are changing the source.. then why not dump the table and re-link

VB Code:

DoCmd.DeleteObject acTable, "Test"
Application.RefreshDatabaseWindow
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "Test", "C:\path\to\file.xls"

Coolies... ill have a go at that..... was really scratching my head over it!!

[EDIT...]

That works, but i also need to use the first set of entries as column headings.....

Got it!

added TRUE as the 'Has Field Names' property to make:

VB Code:

DoCmd.TransferSpreadsheet acLink, 8, "staffList", sFile, True