variable for path name in ODBC connection

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" _
)

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

Jim

"MOI_Jim" wrote:

> 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" _
> )

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
"oops" wrote:

> Actually when I shortened the pathnames for posting I left too many slashes
> in the select column path.
>
> Jim
>
> "MOI_Jim" wrote:
>
> > 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" _
> > )

Philip,

Thank you, it worked perfectly. I had tried something similar, but I don't
understand the differences in all of the path and filename declarations in
this query. I think in my attempts I used variables for all of them at once.

Thanks again.

Jim

"Philip" wrote:

> 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
> "oops" wrote:
>
> > Actually when I shortened the pathnames for posting I left too many slashes
> > in the select column path.
> >
> > Jim
> >
> > "MOI_Jim" wrote:
> >
> > > 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" _
> > > )