Import Data From Access! Please Help!!
 

Its been a while since i have posted however I have a quick question
that hopefully requires a simple answer... I am trying to build a query
from excel that gets data from access... The thing is i want 2 variables
in the query so people can choose what database they extract data
from... Here is my code


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


Lets say "NewFN1 = C:/db.mdb"
and FilePath = C:/

I cant get this to work... Any ideas?


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

Import Data From Access! Please Help!!
 

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