Import from MS Access - Lose the drive letter..link only to drive

[Cyhill]

Is there a way to import data to excel, from MS Access without haveing the
link referance a drive letter?

I would like the spreadsheet to referance only the drive name. This way
multiple users can update the excel file regardless of what drive letter they
have the shared drive containing the DB mapped to.

 

[Guest]

Have you tried the UNC name? It should work the same.

 

[Darren Bartrup]

Hi Cyhill,

I'm presuming you're using Data~Import External Data~Import Data

right-click on your imported table and select 'Edit Query'.
In the connection text box, find where it says Source=C:\folder name\... (or
whichever drive) and change this to the server name.

You can find your server name by looking in Explorer - it will appear
looking something like:
common on 'server-name' (J

in this case you would change the
Source=C:\folder name\...
to
//server-name\common\folder name\...

Remember the first two slashes are forward slashes rather than back slashed.

Hope this works - worked for me

(can you rate the post if it was helpful please - thanks).

 

[Cyhill]

Darren - Thanks for the quick reply!!

However, I must be missing something. When I select 'Edit Query', I do not
see the option you mention beow.

The query wizard takes me to a 'Choose Columns' prompt.

I'm running Office 2003...in case that makes a difference.

 

[Cyhill]

I'm using the following method:

Data > Import External Data > New DB Query...

and then following the prompts to pinpoint the DB I wish to link to.

At which point , (option), would I enter the UNC Name?

Or is there a whole other process for importing data from access to excel
using the UNC Name?

Thanks,

 

[Darren Bartrup]

That's because you're using Microsoft Query, and I'm not sure if that
supports UNC - I tried going into the SQL behind the query and changing the
drive letter, but the computer didn't like it.

Sorry, the only way I know how is to use

Data > Import External Data > Import Data

or to write some VBA code to make a link to the database.
You could always delete your imported data and import it the other way

 

[Cyhill]

Darren - I am using the same method that you are (Data > Import External Data

Import Data). However, I do not see the place you were trying to point me

to in the earlier post that you said would allow me to remove the drive
letter.


In the previous post you stated the following:

"right-click on your imported table and select 'Edit Query'.
In the connection text box, find where it says Source=C:\folder name\... (or
whichever drive) and change this to the server name."

However, when I follow the steps you mentioned, I do not see the connention
text box. Am I missing something?

Thanks for your time!

CH

 

[Darren Bartrup]

OK, in Excel you select
Data > Import External Data > Import Data...

This will bring up an open file dialogue box called 'Select Data Source'
The 'Look In' combo box will be set to 'My Data Sources' and it will include
things like '+Connect to New Data Source.odc'

After you've navigated to your database you'll get a 'Select Table'
dialogue box listing all the tables and queries in your database.

After you've selected a table and pressed OK you'll get an 'Import Data'
dialog box which asks where you want to import the data to (default cell $A$1
on the existing worksheet). One of the buttons at the bottom of this form
says 'Edit Query...'.
Pressing this brings up the 'Edit OLE DB Query' dialogue box which shows
your connection parameters - looking something like:

Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data
Source=H:\DATA\ACCESS\Database name.mdb;Mode=Share Deny Write;Extended
Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet
OLEDBatabase Password="";Jet OLEDB:Engine Type=5;Jet OLEDBatabase 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 OLEDBon't Copy Locale
on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False

On the second line where it says H:\ change that to the server name as I
described in the first post.

If you right-click on an imported table it will give an option to 'Edit
Query...' which brings up the 'Edit OLE DB Query'.

If you're using
Data > Import External Data > New Database Query
(this is the way you've put your using in your post to - )

you'll get a dialogue box called 'Choose Data Source' and it will include
things like '<New Data Source>', '<Excel Files*>'
On your taskbar at the bottom of the screen there'll be a new icon called
'Microsoft Query'
Once you've chosen a data source you'll get a 'Connecting to data source...'
message come up and a 'Select Database' dialogue box.

If you're using that method, then I'm afraid I can't help.

 

[Cyhill]

Okay - Thanks for your tiome on this.

FYI: I tried VB (with no luck).

However, MS Script Editor allowed me to update the path with the server name
(removeing the drive letter). This seems to have worked on my test file. I
will test further. Hopefully this will be what I'm looking for.

Thanks for your time on this.