G
greenfalcon
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(Array( _
"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?
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(Array( _
"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?