Linking Data from Excel to Access Drive letter problems 3

[Stoffman]

My Application is used in different locations, by different members of staff. Part of the application uploads data from an excel spreadsheet. The problem arises when different members of staff have set up there Network drives as different drive letter alias's. For example the linked table in Access connects to my L: Drive which just happens to be another blokes J: Drive. When he clicks on the'Upload Data' button, it doesn't work because it cannot find the spreadsheet as he doesn't have his L: drive mapped to the same drive address.

Is there anyway to link data using the drive address and now the Drive letter (as you can when setting up shortcuts to your favourite files).

Of course the best answer would be to make sure that everyone sets up the same drives with the same drive Letters, but it just isn't possible as the moment

Any ideas ?

 

[SkipVought]

Hi,

Format the string with the SERVER NAME rather than the DRIVE.

...
DBQ=\\server007\BConstruct\Estimating\Shared
...

Skip,
[sub]
[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie![/sub]

 

[Stoffman]

Sorry, that sounds all very simple but where do I put this code.

All I did was click on file--> Get external Data-->Link Tables--> then located the excel spreadsheet in the file system.

Cheers Chris

 

[JerryKlmns]

This shall relink all tables using not the mapped drive letter but the full path to the server folder

Sub ReLinkThem(ByVal myServer As String)
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim varA

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
  If len(tbl.Properties("Jet OLEDB:Link Datasource"))> 0 Then
   varA = Split(tbl.Properties("Jet OLEDB:Link Datasource"), "\")
   varA(0) = myServer
   tbl.Properties("Jet OLEDB:Link Datasource") = Join(varA, "\")
  End If
Next tbl
Set cat = Nothing
End Sub

myServer should have the server name like \\CompanyServer

Be aware that this, works only on mapped drive letters..

On Tools --> References check your version of
Microsoft ADO Ext. 2.x for DDL and Security

 

[JerryKlmns]

... and goes in a module of your database.

 

[Stoffman]

I must be more stupid than I look. You will have to spoon feed me here guys as I can't get this working.

Where is says 'varA(0) = myServer' you want me to change it to read

'varA(0) = \\lmpstmf05\rtp' . It highlights this in red due to the backslashes.

Also how do I launch this code, I assume I only need to do it the once.?

Cheers Chaps

 

[SkipVought]

Turn on your macro recorder and record EDITING your query table.

Post back with your code if you need help cleaning it up.

Skip,
[sub]
[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie![/sub]

 

[JerryKlmns]

Stoffman,

Create a new module. The Visual Basic Editor shows up.
On menu, click Tools, select References, find the Microsoft ADO Ext. 2.x for DDL and Security , click the checkbox and Ok
Copy paste the code.

Press Ctrl+G, this will bring up the immediate window. There, write this

ReLinkThem("\\lmpstmf05\rtp")

and hit Enter.

This, will execute the code, passing the value \\lmpstmf05\rtp to the variable myServer. Et voila!

 

[Stoffman]

You lot are having a laugh with me. There is no such thing as macro recorder in Access97. Under Tools--> Macros, it only allows me to run a macro not record like it does in Word or Excel. As far as editing the table is concerned I am not sure I no what you mean. I can open a table, look at the design of a table and chnage things here, but I cannot see where it allows you to change the path of the linked table. I have used the Link table manager but that only refreshes the link?

What am I like??

 

[JerryKlmns]

I would say a begginer trying to find a way out??
Reminds me of myshelf...

Skip propably thought of excel but you are using Access97!

On the database window there is a module tab. There create new and follow the rest of my previous posts.

 

[SkipVought]

Jerry,

Yes, I incorrectly assumed that he was working from Excel. Good job!. ==> *

Skip,
[sub]
[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie![/sub]

 

[JerryKlmns]

Skip thanx for the *, but I 'm eagerly waiting for Stoffman 's response....

I hope he gets through with this.

 

[Stoffman]

Ok Guys I have been down the pub and had a couple of beers, and now life is looking a lot better. Yes you guys are right I am working on Access 97 and not excel. One of my Tables is a linked table to an Excel spreadsheet. The problem is when you link an Access table to an Excel spreadsheet, it creates the path using the Drive letter that I have set up for that Server. (Which happens to be different to other members of staff) When other people use the database they cannot connect to this linked table as they have that server set up as a different drive letter than the one which was originally set up.

Does anyone know how to change the Properties of a linked table to show the full path.

Cheers

 

[JerryKlmns]

Stoffman, follow my posts in this order

26 Jul 05 7:30
26 Jul 05 5:11
26 Jul 05 6:55

If you cant solve it, I cant beam me up ......even if you buy me a beer or a doozen

 

[Stoffman]

Right we seem to be getting somewhere. I followed all your steps. It fell over with a Compile Error, Variale not found. In the Visual Basic window its hightlighted. 'Sub ReLinkThem(ByVal myServer As String)' in yellow and CurrentProject.Connection in dark blue.

Not sure if this helps but when I selected References. I now have.

-Visual Basic for applications
-Microsoft Access 8.0 Object Library
-Microsoft DAO 3.51 Object library
-Microsoft ADO ext.2.7 for DLL and Security

I can feel this working soon!

Selected.

 

[JerryKlmns]

Ok! Add this too
Microsoft ActiveX Data Ojects 2.7 Library

 

[PHV]

I'm not sure that CurrentProject is known by Access97 ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Stoffman]

Have added Microsoft ActiveX Data Ojects 2.7 Library
and Still have the same problem.
Here is the whole code as is, with nothing changed. Are you saying that I should change the 'CurrentProject' to Say 'Access97'?

Sub ReLinkThem(ByVal myServer As String)
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim varA

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
If Len(tbl.Properties("Jet OLEDB:Link Datasource")) > 0 Then
varA = Split(tbl.Properties("Jet OLEDB:Link Datasource"), "\")
varA(0) = myServer
tbl.Properties("Jet OLEDB:Link Datasource") = Join(varA, "\")
End If
Next tbl
Set cat = Nothing
End Sub

Cheers

 

[PHV]

I think the best way in ac97 is to play with the CurrentDB.TableDefs collection and the Connect property & RefreshLink method of the TableDef object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jebry]

Hi Stoffman!

You might be able to get the real path through the link wizard. When you are searching for the excel sheet instead of choosing the drive it is on, choose My Network places or My Global or whatever it is called on your machine. You should be able to navigate to the server by name and then find the correct 'drive' folder etc.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst