Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 645
  • Last Modified:

Use relative pathname in Excel Query to Access database

Is there a way to make the reference to the database name a relative path rather than a fullpathname starting at the C: directory,. In this sample I want to have Source=V3.0 PMW 160 Master Plan.mdb instead of starting with the C: directory.  If I put that in, it gets converted to a full pathname

Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\PMW 160 Plans\V3.0 PMW 160 Master Plan.mdb;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False
0
StevenPMoffat
Asked:
StevenPMoffat
  • 3
  • 2
1 Solution
 
mikeopoloCommented:
To my knowledge, no. I'm saying this based on experience with various other connection strings for different database files, where the name of the database always requires the full path and filename.

May I ask why you would want this?

Regards
Mike
0
 
rockiroadsCommented:
might be possible, you could try this

'Change directory to where path of xls file is
chdir("C:\PMW 160 Plans")

'Open xlsfile using relative path

0
 
StevenPMoffatAuthor Commented:
For Mike:   I have a spreadsheet that is linked to an Access database file.  I want to pass them around to a number of individuals together and not have them have to change the pathname in the query.  

For Rock.  I setup the link in Excel to Access by using the Connection box of the Edit OLE DB Query box so I don't know if your recommended commands would work?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rockiroadsCommented:
What about using ODBC then. Each user will have to create an ODBC entry on their PC. This will point to the database.
Then see if you can connect via ODBC instead
0
 
rockiroadsCommented:
Forgot to add, the ODBC approach uses a common name, Excel doesnt worry about the path. Not sure from using Edit OLE DB Query box, you can use ODBC or if there is some other way to acheive what you want
0
 
StevenPMoffatAuthor Commented:
This sounds like it should work in my case.  Thanks for sticking with it.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now