+ Reply to Thread
Results 1 to 2 of 2

Import Data From Access! Please Help!!

Hybrid View

  1. #1
    Registered User
    Join Date
    08-25-2004
    Posts
    82

    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

    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?

  2. #2
    Registered User
    Join Date
    08-25-2004
    Posts
    82
    Got it to work, if anyone was interested here is the 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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1