Access as a backend (2003)

Topic

New Reply

We are new to using Access as a backend datastore on our website. We use Access as the primary database program for sales, and were hoping to use the web as better alternative to weekly in office updates. The shared hosting server uses Windows 2003 and the vendor claims to support Access .mdbs. We cannot link the frontend (Access) to the back end using either ODBC and can’t open the online database, it just tries to download not open in Access. Is there a way to accomplish this?
Thanks,
SteveW

Reply | Quote

Replies

This kind of thing usually involves using either Data Access Pages (an Access feature), or some form of ASP or ASP.net – it sounds as if you are trying to open an Access database in your web browser, which won’t work. Can you give us more details about how you are trying to go about this? Also who is the vendor – your ISP? Finally, what does ODBC have to do with the problem?

Reply | Quote

Thanks for replying so quickly. Yes, we are trying to link the tables on the webserver to a front end on a client machine. When trying to link we get a download trying to start. When we use Linked table manager we get not a valid folder error message. The provider is iPowerWeb.com and it has taken them over 60 days to set up the ability to connect to the webserver. I have serious doubts as to their ability to manage a Windows 2003 Server.

All we want to do is to be able to link the tables on the webserver to the forms, querries and reports on the clients.
SteveW

Reply | Quote

Thanks for replying so quickly. Yes, we are trying to link the tables on the webserver to a front end on a client machine. When trying to link we get a download trying to start. When we use Linked table manager we get not a valid folder error message. The provider is iPowerWeb.com and it has taken them over 60 days to set up the ability to connect to the webserver. I have serious doubts as to their ability to manage a Windows 2003 Server.

All we want to do is to be able to link the tables on the webserver to the forms, querries and reports on the clients.
SteveW

Reply | Quote

This kind of thing usually involves using either Data Access Pages (an Access feature), or some form of ASP or ASP.net – it sounds as if you are trying to open an Access database in your web browser, which won’t work. Can you give us more details about how you are trying to go about this? Also who is the vendor – your ISP? Finally, what does ODBC have to do with the problem?

Reply | Quote

(Edited by HansV to make URL clickable – see Help 19)

Wendell’s right about having to do something special to access Access thru a webpage. What I would recommend is implementing a DSN-less connection in ASP and going from there. If that means nothing to you at all, do a search for “DSN-less connection” on the internet to find loads of discussions on this technology.

Basically the issue is to configure an ASP page to declare, create and implement a connection to the Access db. You will then need to implement logic to find the records from the tables needed (using SQL) and then write the code needed to display the data on a webpage.

If you are completely new to any of this, by far the most painless way to learn about data-driven webpages from a database is to download and play with WebMatrix, a free MS product for creating relatively lightweight ASP.NET web applications. You can very quickly build code to connect and display data from Access and the code generated is remarkably compact and well formatted. A great learning tool and a good solution for smaller web projects. It’s availible at http://www.asp.net/webmatrix%5B/url%5D

If you need to use ODBC for some reason (I don’t recommend it) then you’ll have to create an ODBC System DSN thru the ODBC control panel and then use that pipe to connect to and display Access data on a webpage. I don’t recommend System DSN as you’ll have to have clear access to the webserver machine for any adjustments and there are some performance issues using ODBC that don’t come up in a straight DSN-less connection. If you try WebMatrix you’ll be learning ASP.NET which is the ‘next generation’ ASP from MS. I can’t say one way or another if the latest is the greatest, so it’s up to you…

Reply | Quote

Thanks for your help…We are trying to avoid using either ASP or ASP.Net. All we wish to do is to link the tables in the datastore to the queries, forms and reports that reside on the client machines.
Thanks,
SteveW

Reply | Quote

sorry – didn’t understand the question. if you are trying to share an access db over a network, you should make a copy of the present one (for a backup) and split the database (Tools | Database Utilities…Split Database). What this does is, separates your forms and queries from the source tables. The intention is to have the ‘backend’ db (the container of all the information) separate from the ‘front end’ (the User Interface and supporting queries) — so you can make multiple copies of the Front End and distribute them throughout the network.

I’m not expert on this but other people here are — hopefully Wendell can chime in with more on splitting and distributing Access dbs – or you can re-post a question on DB splitting (or search for the topic) to get more info.

Hope this is more pertinent to your question.

Reply | Quote

Steve,
I have already split the functions from the tables and they reside in two different access databases. The issue is to link the tables in the front end to the back end data store (server side). I have changed vendors to get one who actually supports Access and provides free MS SQL databases as part of their hosting packages. the previous hosting company claimed to support the Access databases but didn’t have a clue. I keep you guys updated.
Thanks,
SteveW

Reply | Quote

Steve,
I have already split the functions from the tables and they reside in two different access databases. The issue is to link the tables in the front end to the back end data store (server side). I have changed vendors to get one who actually supports Access and provides free MS SQL databases as part of their hosting packages. the previous hosting company claimed to support the Access databases but didn’t have a clue. I keep you guys updated.
Thanks,
SteveW

Reply | Quote

sorry – didn’t understand the question. if you are trying to share an access db over a network, you should make a copy of the present one (for a backup) and split the database (Tools | Database Utilities…Split Database). What this does is, separates your forms and queries from the source tables. The intention is to have the ‘backend’ db (the container of all the information) separate from the ‘front end’ (the User Interface and supporting queries) — so you can make multiple copies of the Front End and distribute them throughout the network.

I’m not expert on this but other people here are — hopefully Wendell can chime in with more on splitting and distributing Access dbs – or you can re-post a question on DB splitting (or search for the topic) to get more info.

Hope this is more pertinent to your question.

Reply | Quote

Before we can give you a definitive answer, we need to understand your network topology. Are you in a LAN where everyone is connected to a hub or switch, or are you connected to a WAN where people come in via moderate speed communication links such as DSL? If you are connected via a high-speed LAN, then you don’t need ODBC – you simply link to tables. If on the other hand you are some distance from the server, then you may want to look at alternatives such as Terminal Services or Replication. We have experience with all three, as do many other loungers who frequent the Access forum. I’ll reserve judgement on the effectiveness of your vendor until I know more about your network and what they are trying to accomplish. However, having taken a quick look at what i presume is their website (http://www.ipowerweb.com[/url%5D), it appears they only support some FrontPage extensions (not clear about Access) and mySQL. Since they are a webhosting company, like many others, you will not be able to connect using ODBC or linking tables if the database is to be located on their server. If you want to be able to update data and so forth, you would need to have either DAPs or ASP pages.

Reply | Quote

Both. We are putting the back end on a web server with the hosting company. I have changed hosting companies. the previous (iPowerweb.com) does not have a clue or knowledge tech support for windows Server either 2000 or 2003.

Reply | Quote

Just an FYI, when a Hosting company says that they ‘support Microsoft Access’, what that means, is that they have the necessary drivers to allow ASP (or another server side scripting language) to use ADO to communicate with JET (Access’ DB Engine), in order to read/write data.

It does NOT mean that you can link tables to a database on the web.

Web servers do not work that way. A web server is similar to a file server, but there are VERY key differences. Imagine your harddrive as a filing cabinet. You have folders and files (for this example, consider the ‘files’ to be the folders that contain the data (sheets of paper)). Now, as a normal ‘file server’ either locally, or across a LAN, your OS simply uses the local file/folder system (on Windows OSes, that is going to be FAT16, FAT32, or NTFS), to locate the data, and hand it to you. With our filing cabinet example, the OSes file system is a combination of a little ‘card catalog’ or filing labels to help find what you need, along with the ‘design’ of the physical layout of the cabinet and it’s folders. So having such a cabinet in your office, would be like working off of your harddrive. You can take the ‘files’ that you need, and do whatever you want to them. When you have a file ‘out’ on your desk, it is ‘marked’ by the OS as being in use, so that if you need to use the file for another project, you have to either put it back into the cabinet (close the file), or simply ‘share’ it on your desk.

Now, a network file server, on a LAN, is like a filing cabinet outside of your office, with a secretary. You ask the secretary for the files that you need, and the secretary brings them to you. A little slower then getting them yourself. The speed of your LAN would be equivalent to how close the secretary is to your office, and the speed of the file server would be equivalent to how well organized your secretary is. Now, if two people are using the same secretary, and you need a file, if you are just going to read the file, the secretary would copy the file, and give it to you, however, if you are going to modify a file, the secretary would give you the parts you need. Anyone else needing the file, would have to wait until you are done.

Now, a Webserver is more like a corporate office. The secretary and file cabinet are not in your local neighborhood, but instead available by fax. In this case, when you ask for a file, you are faxed the entire thing. You can’t modify the file directly, because the corporate office just sent you a fax, if 5 people send modified faxes back, which one becomes the ‘new’ one. However, there are advantages to the corporate office / webserver approach. For example, let’s say you have a Executive Sales report. On a local or network file system (cabinet in your office, or in your building), you may have 50 versions of the report. So you have to ask for a specific one, because you can’t ask the secretary to guess what report you in particular might need, because Jim Bob may mean a different ‘version’. Way too much for the local secretary to remember, for every file…etc. However, when you fax the corporate office for the Executive Report. To simplify matters, the corporate office can hand out what it wants too, so it can ‘standardize’ what is being sent out. Also, the corporate office may want to edit certain parts of files before sending them out, so you may get faxes with blacked out information. All very similar to a webserver.

Now, when dealing with an Access .mdb, to read/write data, your computer is getting various parts of a file (the indexes are usually the first thing grabbed, then figured out, then the data portions are requested from the HD or network server, and displayed to the user.) Either way, various ‘locks’ are used, read locks, write locks, etc. This works fine for local and network file systems, but from a web perspective, it is only handing you a copy of the data. Once it sends it to you, it’s out of sight and out of mind.

To get LAN file server access across a WAN, you need to setup a fileserver that has VPN capabilities. This allows your computer to access another computer on the Internet, just like a file server. However, since most Internet connections are a small fraction of the speed of most LANs, access through a VPN is like having the corporate office use UPS, instead of a fax.

I hope this was somewhat enlightening, it was kind of fun to write!

Reply | Quote

Yes, most enlightening…The hosting company does not support what it has claimed and we have moved to one that does acutally deliver what it promised. We will use MS SQL on their servers to make this work.
Stevew

Reply | Quote

Ah yes, SQL Server. That can be setup to be used through the Internet through ODBC. Problem with Access, is that it’s a client side db, and doesn’t have a ‘server’ component to receive ‘SQL’ requests….the client machine needs direct file read/write access.

Good luck with SQL Server!

Reply | Quote

Thanks for all your help! The new hosting company has been fantastic with tech support and fixing any problems that come up. Not only to they offer MS SQL and My SQL plus Access support but they have WSS as a no cost extra. I called the former company to cancel our account and it took 90 min. to reach the billing dept. DON’T USE iPowerWeb.com to host windows.
SteveW

Reply | Quote

Thanks for all your help! The new hosting company has been fantastic with tech support and fixing any problems that come up. Not only to they offer MS SQL and My SQL plus Access support but they have WSS as a no cost extra. I called the former company to cancel our account and it took 90 min. to reach the billing dept. DON’T USE iPowerWeb.com to host windows.
SteveW

Reply | Quote

Ah yes, SQL Server. That can be setup to be used through the Internet through ODBC. Problem with Access, is that it’s a client side db, and doesn’t have a ‘server’ component to receive ‘SQL’ requests….the client machine needs direct file read/write access.

Good luck with SQL Server!

Reply | Quote

Yes, most enlightening…The hosting company does not support what it has claimed and we have moved to one that does acutally deliver what it promised. We will use MS SQL on their servers to make this work.
Stevew

Reply | Quote

Just an FYI, when a Hosting company says that they ‘support Microsoft Access’, what that means, is that they have the necessary drivers to allow ASP (or another server side scripting language) to use ADO to communicate with JET (Access’ DB Engine), in order to read/write data.

It does NOT mean that you can link tables to a database on the web.

Web servers do not work that way. A web server is similar to a file server, but there are VERY key differences. Imagine your harddrive as a filing cabinet. You have folders and files (for this example, consider the ‘files’ to be the folders that contain the data (sheets of paper)). Now, as a normal ‘file server’ either locally, or across a LAN, your OS simply uses the local file/folder system (on Windows OSes, that is going to be FAT16, FAT32, or NTFS), to locate the data, and hand it to you. With our filing cabinet example, the OSes file system is a combination of a little ‘card catalog’ or filing labels to help find what you need, along with the ‘design’ of the physical layout of the cabinet and it’s folders. So having such a cabinet in your office, would be like working off of your harddrive. You can take the ‘files’ that you need, and do whatever you want to them. When you have a file ‘out’ on your desk, it is ‘marked’ by the OS as being in use, so that if you need to use the file for another project, you have to either put it back into the cabinet (close the file), or simply ‘share’ it on your desk.

Now, a network file server, on a LAN, is like a filing cabinet outside of your office, with a secretary. You ask the secretary for the files that you need, and the secretary brings them to you. A little slower then getting them yourself. The speed of your LAN would be equivalent to how close the secretary is to your office, and the speed of the file server would be equivalent to how well organized your secretary is. Now, if two people are using the same secretary, and you need a file, if you are just going to read the file, the secretary would copy the file, and give it to you, however, if you are going to modify a file, the secretary would give you the parts you need. Anyone else needing the file, would have to wait until you are done.

Now, a Webserver is more like a corporate office. The secretary and file cabinet are not in your local neighborhood, but instead available by fax. In this case, when you ask for a file, you are faxed the entire thing. You can’t modify the file directly, because the corporate office just sent you a fax, if 5 people send modified faxes back, which one becomes the ‘new’ one. However, there are advantages to the corporate office / webserver approach. For example, let’s say you have a Executive Sales report. On a local or network file system (cabinet in your office, or in your building), you may have 50 versions of the report. So you have to ask for a specific one, because you can’t ask the secretary to guess what report you in particular might need, because Jim Bob may mean a different ‘version’. Way too much for the local secretary to remember, for every file…etc. However, when you fax the corporate office for the Executive Report. To simplify matters, the corporate office can hand out what it wants too, so it can ‘standardize’ what is being sent out. Also, the corporate office may want to edit certain parts of files before sending them out, so you may get faxes with blacked out information. All very similar to a webserver.

Now, when dealing with an Access .mdb, to read/write data, your computer is getting various parts of a file (the indexes are usually the first thing grabbed, then figured out, then the data portions are requested from the HD or network server, and displayed to the user.) Either way, various ‘locks’ are used, read locks, write locks, etc. This works fine for local and network file systems, but from a web perspective, it is only handing you a copy of the data. Once it sends it to you, it’s out of sight and out of mind.

To get LAN file server access across a WAN, you need to setup a fileserver that has VPN capabilities. This allows your computer to access another computer on the Internet, just like a file server. However, since most Internet connections are a small fraction of the speed of most LANs, access through a VPN is like having the corporate office use UPS, instead of a fax.

I hope this was somewhat enlightening, it was kind of fun to write!

Reply | Quote

Both. We are putting the back end on a web server with the hosting company. I have changed hosting companies. the previous (iPowerweb.com) does not have a clue or knowledge tech support for windows Server either 2000 or 2003.

Reply | Quote

Before we can give you a definitive answer, we need to understand your network topology. Are you in a LAN where everyone is connected to a hub or switch, or are you connected to a WAN where people come in via moderate speed communication links such as DSL? If you are connected via a high-speed LAN, then you don’t need ODBC – you simply link to tables. If on the other hand you are some distance from the server, then you may want to look at alternatives such as Terminal Services or Replication. We have experience with all three, as do many other loungers who frequent the Access forum. I’ll reserve judgement on the effectiveness of your vendor until I know more about your network and what they are trying to accomplish. However, having taken a quick look at what i presume is their website (http://www.ipowerweb.com[/url%5D), it appears they only support some FrontPage extensions (not clear about Access) and mySQL. Since they are a webhosting company, like many others, you will not be able to connect using ODBC or linking tables if the database is to be located on their server. If you want to be able to update data and so forth, you would need to have either DAPs or ASP pages.

Reply | Quote

Thanks for your help…We are trying to avoid using either ASP or ASP.Net. All we wish to do is to link the tables in the datastore to the queries, forms and reports that reside on the client machines.
Thanks,
SteveW

Reply | Quote

(Edited by HansV to make URL clickable – see Help 19)

Wendell’s right about having to do something special to access Access thru a webpage. What I would recommend is implementing a DSN-less connection in ASP and going from there. If that means nothing to you at all, do a search for “DSN-less connection” on the internet to find loads of discussions on this technology.

Basically the issue is to configure an ASP page to declare, create and implement a connection to the Access db. You will then need to implement logic to find the records from the tables needed (using SQL) and then write the code needed to display the data on a webpage.

If you are completely new to any of this, by far the most painless way to learn about data-driven webpages from a database is to download and play with WebMatrix, a free MS product for creating relatively lightweight ASP.NET web applications. You can very quickly build code to connect and display data from Access and the code generated is remarkably compact and well formatted. A great learning tool and a good solution for smaller web projects. It’s availible at http://www.asp.net/webmatrix%5B/url%5D

If you need to use ODBC for some reason (I don’t recommend it) then you’ll have to create an ODBC System DSN thru the ODBC control panel and then use that pipe to connect to and display Access data on a webpage. I don’t recommend System DSN as you’ll have to have clear access to the webserver machine for any adjustments and there are some performance issues using ODBC that don’t come up in a straight DSN-less connection. If you try WebMatrix you’ll be learning ASP.NET which is the ‘next generation’ ASP from MS. I can’t say one way or another if the latest is the greatest, so it’s up to you…

Reply | Quote

G’day Steve,
I have been trying to achieve the same MSAccess back-end result and finding your question has helped me understand the situation.
fyi:- I host my own webpage and ftp site. This is achieved by using http://www.dyndns.com/. This is free for non-commercial users and it works fine.
My first problem was caused by trying to use my FTP site to store the MSAccess backend. FTP can only transfer complete files and would not permit linking to an MSAccess database!
I have SQL-Server but could not decide from the documentation if MSAccess could connect over the internet. I suspected that I would have to translate the front end into web pages. Have you successfully connected using ODBC? I ask because this seemed to be your intention.
If I use SQL-Server, it will mean that I have to leave my pc running 24-7. Is there any way of Replicating the SQL-Server database in an MSAccess frontend?
Finally, is it all working as you expected?

Reply | Quote