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

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.

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

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

"Cyhill" wrote in message
...
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.

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

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" wrote:

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.

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

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.

"Darren Bartrup" wrote:

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" wrote:

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.

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

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,

"-" wrote:

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

"Cyhill" wrote in message
...
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.

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

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.

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

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.

"Darren Bartrup" wrote:

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.