Refresh Data from Excel Sheet

[popohoma]

Hi All,

My excel worksheet loads data from access when the sheet opens. My data can be refreshed if I set the refresh period. However, if I add a new record from Access during runtime, the new record can not be refreshed from the data sheet. Do you guys have clue ? I wrote the following code:

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\DOCUMENTS AND SETTINGS\ALLENW\DESKTOP\andy.mdb;DefaultDir=C:\DOCUMENTS AND SETTINGS\ALLENW\DE" _
), Array("SKTOP;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), _
Destination:=Sheets("ClientData").Range("A1"))
.CommandText = Array("SELECT distinct ClientMaster.clientname FROM ClientMaster order by clientname")
.Name = "MS Access Database (not sharable)"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.SourceConnectionFile = _
"C:\Program Files\Common Files\ODBC\Data Sources\MS Access Database (not sharable).dsn"
.Refresh BackgroundQuery:=False
End With



Thank you for your help



Cheers,


Allen