Excel vba using querytables

[godzi]

Hi,

I have a question, I am using the Get External data option in Excel to pull an Access database onto my excel spreasheet, though, everytime I want to use it the database has a different location. How can I change the code in vba for me to prompt me a location, in the code I attached below how can I prompt for location replacing: DBQ??, DefaultDir ?? and command text FROM 'path'?? ??

the code I have so far is this:
----------------------------------------------------------------
Sub AccessData()

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=Path.mdb;DefaultDir=Path??;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5" _
), Array(";UID=admin;")), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT DISTINCT OWNERS.WELL, OWNERS.OPERATOR, OWNERS.SEARCHID" & Chr(13) & "" & Chr(10) & "FROM `PATH?????`.OWNERS OWNERS" & Chr(13) & "" & Chr(10) "HAVING (OWNERS.ID<>'1' And" _
, " OWNERS.ID Not Like '%U')" & Chr(13) & "" & Chr(10) & "ORDER BY OWNERS.ID")
.Name = "Query from Access Database_5"
.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
End Sub
--------------------------------------------------------------------

How can I get it work with path prompting?????

Thanks,

Godzi

 

[SkipVought]

Hi,

WOW! Are you serious? How do you know where the database has moved to? Who devised such a diabolic scheme? Must have been the designer of a shooting gallery at Cony Island!

Anyhow, if you have more questions, please post VBA-related in forum707...

Sub AccessData()
  Dim sConn As String, sSQL As String, sPath, sDB As String
  
    sPath = Application.GetOpenFilename("Access Files (*.mdb), *.mdb")
    
    If sPath <> False Then
        sPath = Left(sPath, InStrRev(sPath, "\") - 1)
        
        sDB = "YourDatabasename"    'without the file extension
    
        sConn = "ODBC;DSN=MS Access Database;"
        sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".mdb;"
        sConn = sConn & "DefaultDir=" & sPath & ";"
        sConn = sConn & "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5"
    
        sSQL = "SELECT DISTINCT "
        sSQL = sSQL & "  OWNERS.WELL"
        sSQL = sSQL & ", OWNERS.OPERATOR"
        sSQL = sSQL & ", OWNERS.SEARCHID"
        sSQL = sSQL & vbLf
        sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.OWNERS OWNERS"
        sSQL = sSQL & vbLf
        sSQL = sSQL & "WHERE OWNERS.ID<>'1'"
        sSQL = sSQL & "  And OWNERS.ID Not Like '%U')"
        sSQL = sSQL & vbLf
        sSQL = sSQL & "ORDER BY OWNERS.ID"
    
        With ActiveSheet.QueryTables
            .Connection = sConn
            .CommandText = sSQL
            .Refresh BackgroundQuery:=False
        End With
    End If
  
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[godzi]

Skipvought,

Thank you very much for that answer, I am testing the code... However...

I want sDB to catch the PATH and Database name, since it is a different name eveytime !!!! say: same drive E:\ same folder , different database names....

thanks,

 

[SkipVought]

Same SQL, different DB & Table?

Hows that gonna work?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[godzi]

The vba code was a macro recorded from the following SQL statement:

SELECT DISTINCT OWNERS.WELL, OWNERS.OPERATOR, OWNERS.SEARCHID
FROM `E:\X Folder\X database`.OWNERS OWNERS
GROUP BY OWNERS.WELL, OWNERS.OPERATOR, OWNERS.SEARCHID
HAVING (OWNERS.SEARCHID<>'1' And OWNERS.SEARCHID Not Like '%U')
ORDER BY OWNERS.SEARCHID

The way I am handling it is like this, I open the External data, then I select my saved query, and edit it in Microsoft Query, where I need to click on SQL, and change de portion: E:\ yadda\yadda... to database I want to open....

That said, I was hoping to automate that sql statement with VBA. !!!!

What do you think??? maybe tricking the code you send me a little bit more?

Thanks. !!!

 

[SkipVought]

See my answer in forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}