Excel ODC/ODBC Relative Path

I connect Excel to an Access database via ODBC with connection information stored in DSN file. I would like to modify the connection string to use the relative path. Thoughts

<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
<meta name=ProgId content=ODC.Table>
<meta name=SourceType content=OLEDB>
<meta name=Catalog content="U:\QIC DEMO REPORTING\QIC Demo Reporting.accdb">
<meta name=Table content=tblConsolidatedHistory>
<title>QIC Demo Reporting.accdb tblConsolidatedHistory</title>
<xml id=docprops><o:DocumentProperties
  xmlns:o="urn:schemas-microsoft-com:office:office"
  xmlns="http://www.w3.org/TR/REC-html40">
  <o:Name>QIC Demo Reporting.accdb tblConsolidatedHistory</o:Name>
 </o:DocumentProperties>
</xml><xml id=msodc><odc:OfficeDataConnection
  xmlns:odc="urn:schemas-microsoft-com:office:odc"
  xmlns="http://www.w3.org/TR/REC-html40">
  <odc:Connection odc:Type="OLEDB">
   <odc:ConnectionString>Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=&quot;DSN=QIC Demo Reporting;DBQ=U:\QIC DEMO REPORTING\QIC Demo Reporting.accdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;&quot;;Initial Catalog=U:\QIC DEMO REPORTING\QIC Demo Reporting.accdb</odc:ConnectionString>
   <odc:CommandType>Table</odc:CommandType>
   <odc:CommandText>`U:\QIC DEMO REPORTING\QIC Demo Reporting.accdb`.`tblConsolidatedHistory`</odc:CommandText>
  </odc:Connection>
  <odc:Connection odc:Type="ODBC">
   <odc:ConnectionString>DSN=QIC Demo Reporting;DBQ=U:\QIC DEMO REPORTING\QIC Demo Reporting.accdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;</odc:ConnectionString>
   <odc:CommandText>SELECT * FROM `U:\QIC DEMO REPORTING\QIC Demo Reporting.accdb`.`tblConsolidatedHistory`</odc:CommandText>
  </odc:Connection>
 </odc:OfficeDataConnection>
</xml>
<style>
<!--
    .ODCDataSource
    {
    behavior: url(dataconn.htc);
    }
-->
</style>
 
</head>

<body onload='init()' scroll=no leftmargin=0 topmargin=0 rightmargin=0 style='border: 0px'>
<table style='border: solid 1px threedface; height: 100%; width: 100%' cellpadding=0 cellspacing=0 width='100%'> 
  <tr> 
    <td id=tdName style='font-family:arial; font-size:medium; padding: 3px; background-color: threedface'> 
      &nbsp; 
    </td> 
     <td id=tdTableDropdown style='padding: 3px; background-color: threedface; vertical-align: top; padding-bottom: 3px'>

      &nbsp; 
    </td> 
  </tr> 
  <tr> 
    <td id=tdDesc colspan='2' style='border-bottom: 1px threedshadow solid; font-family: Arial; font-size: 1pt; padding: 2px; background-color: threedface'>

      &nbsp; 
    </td> 
  </tr> 
  <tr> 
    <td colspan='2' style='height: 100%; padding-bottom: 4px; border-top: 1px threedhighlight solid;'> 
      <div id='pt' style='height: 100%' class='ODCDataSource'></div> 
    </td> 
  </tr> 
</table> 

  
<script language='javascript'> 

function init() { 
  var sName, sDescription; 
  var i, j; 
  
  try { 
    sName = unescape(location.href) 
  
    i = sName.lastIndexOf(".") 
    if (i>=0) { sName = sName.substring(1, i); } 
  
    i = sName.lastIndexOf("/") 
    if (i>=0) { sName = sName.substring(i+1, sName.length); } 

    document.title = sName; 
    document.getElementById("tdName").innerText = sName; 

    sDescription = document.getElementById("docprops").innerHTML; 
  
    i = sDescription.indexOf("escription>") 
    if (i>=0) { j = sDescription.indexOf("escription>", i + 11); } 

    if (i>=0 && j >= 0) { 
      j = sDescription.lastIndexOf("</", j); 

      if (j>=0) { 
          sDescription = sDescription.substring(i+11, j); 
        if (sDescription != "") { 
            document.getElementById("tdDesc").style.fontSize="x-small"; 
          document.getElementById("tdDesc").innerHTML = sDescription; 
          } 
        } 
      } 
    } 
  catch(e) { 

    } 
  } 
</script> 

</body> 
 
</html>

Open in new window

shieldscoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
If you are having a problem because of different drive mapping, use::
\\serverpath\localpath\yourdatabase.accdb

Othewise, you would have to change the connection strings on the fly since they can't be relative.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
adding on to Pat's comment ...

the connection string must be absolute, but you can store a relative path and use a function to return the first part to build the connecting strings.

In those cases, I like to store the relative path starting with \ and have a function to return the first part of the path based on settings or user specifications.

Once a DSN file is used to connect, Access doesn't read it again -- but of course the tabledef connect property can be changed by iterating

have an awesome day,
crystal
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shieldscoAuthor Commented:
Thanks
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
HTML

From novice to tech pro — start learning today.