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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cyhill - 14 Sep 2007 16:02 GMT

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.

Reply to this Message

- - 14 Sep 2007 16:34 GMT

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

> Is there a way to import data to excel, from MS Access without haveing the
> link referance a drive letter?
[quoted text clipped - 3 lines]
> they
> have the shared drive containing the DB mapped to.

Reply to this Message

Cyhill - 14 Sep 2007 17:38 GMT

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,

> Have you tried the UNC name? It should work the same.
>
[quoted text clipped - 5 lines]
> > they
> > have the shared drive containing the DB mapped to.

Reply to this Message

Darren Bartrup - 14 Sep 2007 16:36 GMT

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).

> 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.

Reply to this Message

Cyhill - 14 Sep 2007 16:54 GMT

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.

> Hi Cyhill,
>
[quoted text clipped - 25 lines]
> > multiple users can update the excel file regardless of what drive letter they
> > have the shared drive containing the DB mapped to.

Reply to this Message

Darren Bartrup - 17 Sep 2007 12:22 GMT

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 :)

Reply to this Message

Cyhill - 17 Sep 2007 14:52 GMT

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

> 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
[quoted text clipped - 6 lines]
> 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 :)

Reply to this Message

Darren Bartrup - 17 Sep 2007 16:16 GMT

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
OLEDB:Database Password="";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

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.

Reply to this Message

Cyhill - 17 Sep 2007 18:42 GMT

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.

> OK, in Excel you select
> Data > Import External Data > Import Data...
[quoted text clipped - 41 lines]
>
> If you're using that method, then I'm afraid I can't help.

Reply to this Message