RE: variable for path name in ODBC connection

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

try something like this:

sDBPath = "\\Database\test\Test_4_Jim.mdb"
With Active***.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 spread*** 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 Active***.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" _
> > )
.

Quantcast