MS Access remote server linked tables

Sorry about the info overload!

No, I am not sure about much - I only wish I could simply remote access the web server host and reconnect my links!!

I opted out of developing with SQL back end of any kind since I had already mostly completed the structure in Access, and since this is a new application I can barely even get any test data to use, which just makes troubleshooting a bit more of a challenge. Also there was no budget for them to afford SQL as I could tell or assumed, as I was thinking it would be costly, and once I get them a working draft I figured on going back to plug in to a SQL back end, they are expecting heavy traffic, although that is only relative to them, but I was trying to max capacity to buy time should they fill the tables too quickly. Currently they have about 300 users and 2500 clients, I expect 500 users upon deployment.

I linked the tables of the various DB's to a Main DB where I could make Relationships, Queries and Reports, when I ran into the problem of absolute file paths for the table links - I am familiar, but certainly no VBA programmer and want to avoid as much of that as possible, because any changes I need to make to the Main DB after it is published I would also need to run the code to reconnect the linked tables and it seems like that could give me problems, plus system downtime - So I am weighing the overall investment and learning curve involved in migrating to some version of SQL for the backend vs. size limitations and performance issues of having all the tables reside in 1 Main DB for now just to get this working.

This is such a small thing to have a huge impact on my progress that I thought I would try this forum as well, since it's hosted here...

Any insight would be greatly appreciated!

 

I'm trying to create a somewhat larger max capacity than 2 GB with MS Access 2007 backend, why is it so difficult?!

I have 6 backend ACCDB's with 1 table each linked to a Main ACCDB for queries and reports.

Although I developed within the same database folder on my local machine as ftp host and uploaded, the links retain the full local machine's file path.

Without writing code is there an way to assign the ftp database folder path to linked tables without use of UNC - I just need an option for relative link or something NOT absolute!

With all the great things Access does everywhere else I can't host linked tables on the web?

Since I had previously built the Access DB structure, I opted out of converting to SQL Server at this point to get something.

It is simply internet forms with Access databases ALL hosted on a DASP remote server.

6 - front end web pages in .aspx on web host server

6 - back end MS Access 2007 .accdb's on same web host server

1 - .accdb has the other 6 db tables linked to it and relationships mapped within the same root directory folder of the web host server, however the link manager retains the file paths of my local machine where the accdb's were built, then uploaded to ftp host folder of same name, yet instead of only looking within that folder for the linked db tables, it wants ones on my c: drive. I don't have virtual shared drives set up or any type of fancy network neighborhood places, which I can always add if needed...

The hosted db's work just fine behind the web pages directly interacting with them, but I get errors on the page when I call a query in the 1 Main db with all the linked tables.

How can I program to look blindly or have some kind of relative mapping for the root db folder of the host web server only?

Should I be using the 'Publish - Document Management Server' option in MS Access 2007 file menu?

no files will be downloaded from the FTP host, they are just the back end db engine for the SQL select & insert statements, however my main DB holds all my relationships to the linked tables and if the path points to a local drive then execution fails!

Would I need to create a web page that will execute this code before I publish the site?

I can provide a valid ftp path, where all the db files are stored on the host, but I get an invalid path error when I try this and I am not on a LAN to use UNC, why can't I just map the ftp host folder? Am I missing something, it works to upload the files from the same named root folder in DreamWeaver.

 

Hi,
I've got a lot of experience working with Access and admit it is handy.
Overall I'd rather use SQL Server whenever possible.


To be honest, in your case I wouldn't even consider Access.


There have been some changes over the years to allow better use of Access DBs on-line.
Access 2007 however lacks all of the previous security levels. (Access User Level security is gone in v2007)
Forms in Access are something that SQL Server does not have but ASP.NET has added what classic ASP lacked.
You can now use SQL Server with ASP.NET and have a 2-way connection with your forms, which Access can't do.


The limit on Access is 255 users, but on-line Access is not connected, you have to hit it.
So you get around that limit, but I'd still be very cautious.
Salute,
Mark

 

Hi,
First of all...Wow!
(Lot of info there.)

Are you sure you need to link the databases?

As an example of how others might do this with Access...
Some of the large CMS applications for the web would use multiple Access DBs but not link them on-line.
They can all reside in the same folder and any relationships would be within individual DB tables, not External.

I'm not sure but it appears to me the External Relationship links for DBs in Access 2007 are for packaging.
Very handy on a DVD for example.
Salute,
Mark

 

I have painstakingly installed SQL Server Express 2008, is that supported by this host?

Would i just need to addon?

 

With SQL Express 2008 you can connect to and publish to a 2008 addon here.

This was not possible with the previous express versions.
I absolutely admire the Express team for making that possible.