Creating a linked table on remote Access database through ASP/VBScript

kaeldowdy · #1 (**permalink**) 04-07-03, 05:47

I'm wondering if any one had success in creating a linked/attached table on an MS Access database once it is on a webserver.

Say I have two Access databases, and I want to create a linked table in one of them to look at a table in the other. I cannot create the linked table using the UNC path for the folder on the webserver containing the databases b/c it doesn't exist on my local machine. I want to either issue some type of SQL command (which I don't think is possible) or use VBScript to modify the Access db and establish the linked table.

I tried this on my local machine using IIS and it worked, but once I uploaded the dbs to the webserver and opened the same ASP document in my browser, I get a "HTTP 500 Internal server error".

Here's the code I made to do this which worked locally using a UNC path:

Code:

<%
  Dim oAccessApp ' access object variable name
   
  ' instantiate the access object variable with an instance of the destination database 
  ' in the web server's memory so you can create a linked table...
    ' Arguments:  
      '   UNC path of destination database (where you want to link the table to),
      '   class name of object you want to instantiate
      Set oAccessApp = GetObject("\\WebServerName\Folder1\...\database\dbDestination.mdb", "Access.Application")

  ' call the TransferDatabase method to link the table
    ' Arguments:  
    '   TransferType - linked table transfer (acLink = A_ATTACH = 2),
    '   DatabaseType - Microsoft Access in this case,
    '   DatabaseName - UNC path of source database (where you want to link the table from),
    '   ObjectType - the type of object you want to reference..
    '      a table in this case (acTable = A_TABLE = 0),
    '   Source - name of the table you want to link from,
    '   Destination - name of the table you want to link to 
    '      (can be same or different than Source),
    '   StructureOnly - boolean argument whether you want either the structure 
    '      or structure and data (False in this case)
    oAccessApp.DoCmd.TransferDatabase 2, "Microsoft Access", _
"\\WebServerName\Folder1\...\database\dbSource.mdb", _
       0, "tblData", "tbldata", False

  ' release the instance of the access object
  Set oAccessApp = Nothing
%>

As you can see, there are few lines of code to do this, and is pretty cut and dry.

Again, this works locally, but when I run the same script on the webserver (with the appropriate UNC paths), I get that 500 error.

I don't know if there may be a different way to do this, or if there is some setting the webserver has that doesn't allow this (that's what I am thinking, but not sure.)

I remember responding to a post (I don't remember if it was here or on the newsgroups) about the same topic, so I don't know if that person had success or not...I know this isn't a unique situation, but I may have limitations with the web host.

Any ideas??

Kael

Internet Services

Web Development

Digital Marketing

Consumer Tech