Got it to work, if anyone was interested here is the code


Code:
--------------------
NewFN1 = lblFile2.Caption

Sheets("DB_Import").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=MS Access Database;DBQ=" & NewFN1 & ";DefaultDir=" & Path_New & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT HouseholdData.Address, HouseholdData.HouseholdDataID, HouseholdData.SurveyDataID, HouseholdData.DeveloperDataID, HouseholdData.CityDataID" & Chr(13) & "" & Chr(10) & "FROM HouseholdData HouseholdData" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
--------------------


--
greenfalcon
------------------------------------------------------------------------
greenfalcon's Profile: http://www.excelforum.com/member.php...o&userid=13622
View this thread: http://www.excelforum.com/showthread...hreadid=560025