Unanswered: Creating a linked table on remote Access database through ASP/VBScript
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:
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...
' 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
' 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", _
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.