Linked Excel Table on Web Server / UNC 1

[credo]

Hello,
I've been reading some of the posts to try and work out how to use UNC path mapping for the problem below.

The problem is I have a linked Excel table in my db and the path of the linked table is absolute eg. pointing to my C:\directory1\directory\book1.xls. Because the db will sit on a remote application/web server I need somehow to make the path relative eg. book1.xls.

One solution mentioned UNC and another setting up a separate DSN. I'm not sure which option is the best solution to this problem but would like to first try UNC.
Can someone point me to a resource or help ?

thanks in advance

 

[lameid]

Talk to your network administrator. Ultimately he'll know what can and can't be done. UNC is probably easiest to set up. It just requires people to log onto the network. Although using Access via dial-up is too slow unless you are doing terminal emulation. On the other hand you may use high speed internet to connect (I'm not familiar with the security issues their but it could be risky if it isn't set up right).

 

[jfischer]

The UNC (Universal Naming Convention) method of specifying a file location uses the following format:

\\server\share\path\filename

You can use this UNC format very simply to relink your Excel worksheets to an Access database. The "\\server\share" portion of the format takes the place of your MS-DOS/Windows "C:" notation. For example, if you were to share your local C: drive on a network, your sample Excel workbook path in UNC format might be:

\\computername\c\directory1\directory\book1.xls

where "computername" is your computer's name and "c" is the network "share" name assigned to your C: drive.

If you move your Excel worksheet around, or need to link in worksheets from different locations, try the following routine. It accepts UNC format in the path parameter.

Function RelinkWorksheet(sBookPath As String, sSheetName As String) As Boolean
'Relink an Excel Worksheet
  Const cSglQuote = "'"              'Chr(39)
  
  Dim dbs As Database
  Dim tdf As TableDef
  Dim sConnect As String
  Dim saveErr  As Long
  
  On Error GoTo RelinkWorksheet_Err
  RelinkWorksheet = True
  Set dbs = CurrentDb                 'or, CodeDb as needed
  sConnect = "Excel 8.0;HDR=NO;IMEX=2;DATABASE=" & sBookPath
  On Error Resume Next
  Set tdf = dbs.TableDefs(sSheetName)
  saveErr = Err
  Err.Clear
  On Error GoTo RelinkWorksheet_Err
  If saveErr = 3265 Then      'Not found
    Set tdf = dbs.CreateTableDef(sSheetName)
    With tdf
      .Connect = sConnect
      .SourceTableName = cSglQuote & sSheetName & cSglQuote & "$"
    End With
    dbs.TableDefs.Append tdf
  Else                        'Existing link
    If tdf.Connect <> sConnect Then   'Link has changed
      dbs.TableDefs.Delete sSheetName
      Set tdf = dbs.CreateTableDef(sSheetName)
      With tdf
        .Connect = sConnect
        .SourceTableName = cSglQuote & sSheetName & cSglQuote & &quot;$&quot;
      End With
      dbs.TableDefs.Append tdf
    End If
  End If
  
RelinkWorksheet_Exit:
  On Error Resume Next
  Set dbs = Nothing
  Exit Function
  
RelinkWorksheet_Err:
  MsgBox &quot;Error #&quot; & Err.Number & vbCrLf & vbCrLf & Err.Description, _
         vbOKOnly, &quot;RelinkWorksheet&quot;
  RelinkWorksheet = False
  Resume RelinkWorksheet_Exit
End Function

You might call this function as follows:

Result = RelinkWorksheet("\\BigServer\Apps\directory1\directory\book1.xls","sheet1")

Note that the SourceTableName property of the TableDef object points to the specific sheet of the workbook, and is surrounded by single quotes (apostrophe) and has a trailing dollar sign ($).

Hope this helps you.

 

[credo]

thank you for the reply jfischer and lameid - sorry I should have explained a little more, my Excel file and database will be sitting on a remote application/webserver to be accessed over the internet rather than a local network so I'm not sure if the above helps.

I have tried setting up a Web Folder in Explorer to say

http://www.myurl.co.uk/directory1/directory/book1.xls

so I can use it in the linkage path to the spreadsheet, but
I cannot get the full path. It may be something to do with
permissions on my host web server.

The reason I'm trying to do this, is because I have a web based application with an MS Access database, 90% of the tables will remain static, but there is one table that will be updated everyday and this is the table I want to link to an Excel ss, so the Client can just ftp a spreadsheet up to
the web/application server each day, rather than the whole db.Is there a better way of doing this ?

 

[jfischer]

If the Access database and the Excel workbook are going to be stored in the same directory on the Web server, you can pick the path out of the database Name property to use for the Excel link. For example,

Dim sDbPath As String

sDbPath = FilePath(CurrentDb.Name)

where FilePath is a custom function you would write to split out the path from the fully qualified database name. (Access has some built-in properties to use for the path depending on which version and database type, .MDB or .ADP, you're using. You might not have to write a custom function.) Then use the sDbPath string plus the workbook name with the RelinkWorksheet function I posted before to link in the Excel workbook.

The path that is returned by Access is the path of where the database currently resides when it is opened. So, when it is opened on the Web server, it will return the path where it is located on the Web server.

The trick is to open the database. I think that what you may be puzzled about is how to trigger this relinking remotely. I don't have time to answer this at the moment, but I'll post a response soon. lameid is correct that you're going to need to work with the Web server administrator on this one.

 

[jfischer]

You will definitely run into security issues trying to set up hyperlinks directly to the copy of your Excel workbook that was put on the Application/Web server using ftp. The server administrators would need to grant you special permissions to allow that kind of link in a Web hosting environment. If you need to look at the copy of the Excel workbook that is at the server site and you have ftp rights, just ftp "get" another copy of the workbook down to your local computer to open it and look at it.

I'm going to make the assumption that you are using Active Server Pages (ASP) on a Microsoft Internet Information Server (IIS) for your Application/Web server. I'm also going to assume that the same server machine doubles as the data server that hosts your Access database and Excel workbook.

If you are concerned about linking the Excel workbook to the Access database once it is at the data server site, you could handle it simply in one of two ways: 1) let the data server administrator manually link the two files together using the Linked Table Manager of Access, or 2) you provide the data server administrator with alternate "administrative tools". These "tools" could be in the form of a) forms and modules inside your "data" Access database, or b) a separate Access database with forms and modules or a VB program that performs the table linking more automatically, using a function such as I posted earlier. Either way, you need to get your data server administrator involved in the process. Otherwise, you'll need to figure out remote "administration tools" and the special permissions needed to run those. For a one- or two-time link setup between your Access database and the Excel workbook at the data server site, it's probably not worth the remote administration effort.

Once the link is established between your Access database and the Excel workbook, your clients can ftp "put" updates to the data server. (If the workbook is uploaded to a "staging area" on the data server and copied to "production" directories by scripts, the update will be immediately accessible as soon as it is in "production." You do not need to do any re-linking of the Excel workbook to the Access database as long as the directory locations stay the same.) Your Application/Web server ASP's will automatically retrieve "refreshed" copies of the data when they open their ADO recordsets.

Hope this clarifies things for you.

 

[credo]

thanks again JFischer, your assumptions are correct, and your comments have been extremely useful.
Because of the a) security issues and b) extra work of creating a new db to perform the linking I may have a rethink.
I was initially thinking of just building a simple form to allow the Client to update this one table themselves before this post. They would then just ftp the whole db to the server each day (perhaps setting up an automated schedule). I was trying to avoid this because of webpage downtime during the ftp upload and it seems inefficent to transfer the whole db when only one table was changing. Another alternative was to build some ASP pages in a secure area of the site to allow Client-only access to update this one table directly through their web browser. Again, the development time for building this seemed excessive to just update one table and I was'nt sure about handling 'locking record' situations where ppl were trying to view a record that was being updated etc...
In any case it looks I've got some more work/thinking to do about this one.... !