Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import Data from Access | Excel Discussion (Misc queries) | |||
Can't import data to access | Excel Discussion (Misc queries) | |||
Dynamic Data Import From Access | Charts and Charting in Excel | |||
data import and export in to access | Excel Discussion (Misc queries) | |||
Import Data from Access via DAO | Excel Programming |