StevenPMoffat
asked on
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.OLE DB.4.0;Use r 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
Provider=Microsoft.Jet.OLE
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
'Change directory to where path of xls file is
chdir("C:\PMW 160 Plans")
'Open xlsfile using relative path
ASKER
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?
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?
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
Then see if you can connect via ODBC instead
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This sounds like it should work in my case. Thanks for sticking with it.
May I ask why you would want this?
Regards
Mike