Here is my recorded macro and changes I made

recorded macro

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access
Database;DBQ=C:\temp\submission.mdb;DefaultDir=C:\ temp;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5" _
), Array(";")), Destination:=Range("A1"))
CommandText = Array( _
"SELECT Submissions.Task_ID, Submissions.`Client Name`,
Submissions.`Effective Date`, Submissions.`Imp Mgr`, Submissions.`Due
Date`, Submissions.`Actual Date`, Submissions.`Date Difference`" &
Chr(13) & "" & Chr(10) & "FROM `C:\t" _
, _
"emp\submission`.Submissions Submissions" & Chr(13) & "" &
Chr(10) & "WHERE (Submissions.`Client Name`='Test')" _
)
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


Change to make easier to read. I also replace chr(13) and chr(10) with
vbcrlf
I also added a commar at the end of the FROM line because the string
was too long

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y("ODBC;" &
_
"DSN=MS Access Database;" & _
"DBQ=C:\temp\submission.mdb;" & _
"DefaultDir=C:\temp;" & _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5"),
_
Array(";")), _
Destination:=Range("A1"))


CommandText = Array( _
"SELECT Submissions.Task_ID," & _
"Submissions.`Client Name`, " & _
"Submissions.`Effective Date`, " & _
"Submissions.`Imp Mgr`, " & _
"Submissions.`Due Date`, " & _
"Submissions.`Actual Date`, " & _
"Submissions.`Date Difference`", vbCrLf & _
"FROM `C:\temp\submission`.Submissions Submissions", vbCrLf &
_
"WHERE (Submissions.`Client Name`='Test')")

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

Now I replace 'Test' with a reference to the worksheet

Set Pulldownbox = Sheets("sheet2").range("A1")
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y("ODBC;" &
_
"DSN=MS Access Database;" & _
"DBQ=C:\temp\submission.mdb;" & _
"DefaultDir=C:\temp;" & _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5"),
_
Array(";")), _
Destination:=Range("A1"))


CommandText = Array( _
"SELECT Submissions.Task_ID," & _
"Submissions.`Client Name`, " & _
"Submissions.`Effective Date`, " & _
"Submissions.`Imp Mgr`, " & _
"Submissions.`Due Date`, " & _
"Submissions.`Actual Date`, " & _
"Submissions.`Date Difference`", vbCrLf & _
"FROM `C:\temp\submission`.Submissions Submissions", vbCrLf &
_
"WHERE (Submissions.`Client Name`='" & Pulldownbox & "')")

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147026