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
--------------------------------------------------------------------

However...

The Database name is a different name eveytime !!!! say: same drive E:\, same folder , different database names....


Thanks,

Godzi

 

[godzi]

I think I solved the Login problem changing this line:

sDB = Right(sPath, Len(sPath) - InStrRev("\", sPath) - 1)

to:
sDB = Right(sPath, Len(sPath) - InStrRev("\", sPath) - 3)

Now the problem is with the Refresh portion.

Run-time error '1004', SQL Syntax Error.

It points at: .Refresh Backgroundquery:=False


I think we are there.... just one more thought !!!

please... thank you

 

[SkipVought]

Change thusly...

    If sPath <> False Then
        sDB = Right(sPath, Len(sPath) - InStrRev(sPath, "\"))
        sDB = Split(sDB, ".")(0)    '
        sPath = Left(sPath, InStrRev(sPath, "\") - 1)
...

Skip,

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

 

[godzi]

I finally got it !!... problem solved.. thanks

 

[SkipVought]

Sorry. That was my error originally.

Skip,

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