variable for path name in ODBC connection

[Guest]

Hi,
I am trying to update a spreadsheet from an access database. The code below
works, but I need to be able to dynamically alter the filename of the
database, as it changes whenever there is a new version. Any ideas how to
put a variable in for the filename in this query? Thanks for any assistance.
Jim

ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=MS Access Database;DBQ=\\Database\test\Test_4_Jim.mdb
;DefaultDir= \\Database\test\ ;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT tblItemMaster.ItemNo, tblItemMaster.UnitPrice" & Chr(13) &
"" & Chr(10) & "FROM `\\\Database\test\Test_4_Jim`.tblItemMaster
tblItemMaster" _
)

 

[Guest]

Actually when I shortened the pathnames for posting I left too many slashes
in the select column path.

Jim

 

[Guest]

try something like this:

sDBPath = "\\Database\test\Test_4_Jim.mdb"
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=MS Access Database;DBQ=" & sDBPath & ";DefaultDir=
\\Database\test\ ;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT tblItemMaster.ItemNo, tblItemMaster.UnitPrice" & Chr(13) &
"" & Chr(10) & "FROM `\\\Database\test\Test_4_Jim`.tblItemMaster
tblItemMaster" _
)

HTH

Philip