Networking concepts (WinXP Access 2003)

Topic

New Reply

I need some conceptual help on networking, please. As I understand it, the simplest way of connecting multiple PC

Reply | Quote

Replies

The second option is actually the simplest. Peer-to-peer networking does not usually work very well in Access and it breaks down quickly with more than one or two users. Unless you’re very comfortable with SQL Server, an ADP can be very confusing. You would ordinarily split your front and back end anyhow, whether in Access or a SQL Server backend, either of which can be in a network folder. Then it is simply a case of distributing front end copies that link to the back end (Access or SQL) on the network drive. What in particular did you want input on?

Reply | Quote

Your opinions were just what I was looking for. I read some on the SQL with an ADP and it does look more difficult. For sure, you should dominate SQL.

Do I understand that I can place my split back-end on the SQL server as well as on a 2000 Server? What disadvantages do I have vs the ADP in these cases? Is it really easy? Do I need to modify the Access code?

Excuse my ignorance.

Reply | Quote

You can use a SQL Server back end and link to it from an MDB, there’s no placing it on the SQL Server involved. Alternatively, you can place an Access mdb backend on a server (box, not SQL Server per se) and link to it from an MDB. Since in either case you would be running queries and so forth from within Access, there is very little you would need to change. There can be issues between Access date fields and SQL Server date fields and the Access yes/no field is a bit value in SQL Server. Maybe someone else will chime in with anything I’ve forgotten to mention.

Reply | Quote

Charlotte has covered most of the basic issues in selecting a back-end design. So let me suggest some other more intangible considerations:

One of the factors that can drive one to SQL Server is the number of users, and their level of activity. If you have 5 to 10 users really pounding on an Access MDB back-end, you may encounter performance issues and need to consider SQL Server. On the other hand if you have 50 or even 100 users who occasionally open the database and look at one or two records, performance may be perfectly acceptable.

Another factor is the size of the tables. Because Access is not a server, if you have a large number of records in tables, any query run against such tables must pull the entire table into Access on the workstation in order to work on it. Moving a few million records across a LAN, especially if it’s a 10 Mbit LAN, can make for miserable performance. On the other hand, if you put the same table in a SQL Server back-end, SQL Server can extract the specific records you want and send only those records to the workstation, providing much better performance.

I would strongly suggest you not look at ADPs as an alternative, but look at using ODBC linked tables using an MDB front-end. There are many advantages, not the least of which is the learning curve for ADPs and the additional development time ADPs usually require. Also, ADPs are being de-emphasized in future products according to a number of Microsoft sources. FWIW, we develop almost exclusively with SQL Server back-ends connected to an Access MDB front-end deployed to the user workstation.

Finally, you can run SQL Server on a peer-to-peer network, and with either the MSDE version of SQL Server, or the new SQL Server 2005 Express, the product is free. However there are some significant security issues with peer networking and SQL Server that can be an issue if the network has Internet connectivity. If that is the case, I would strongly recommend you set up a domain so you can use the integrated security model with SQL Server.

If this bit of rambling raises more questions, feel free to raise them here. Good luck.

Reply | Quote

A real help!!

Your 4th paragraph, last sentence

Reply | Quote

To answer your questions:

The back-end is the SQL tables. Each workstation has an ODBC Data Source that establishes a connection to the SQL Server database, and then the tables are connected using the File / Get External Data / Link Tables and then choosing ODBC as the type of connection in the dialog box that opens.

You cannot link to an Access back-end which has ODBC linked tables that connect to a SQL Server – you will simply copy the ODBC links to the front-end database. An ADP on the other hand uses a direct connection to SQL Server so you are working with SQL Server objects much as you would with Enterprise Manager.

I used the term MSDE rather loosely – strictly speaking that’s the version that shiped with Office 2000, while the SQL Server Desktop Engine shipped with Office XP and Office 2003. SQL Server 2005 Express is a new version released late last year – you can read lots more about it at http://msdn.microsoft.com/sql/express/%5B/url%5D – but I wouldn’t recommend trying to use it at this point as the administration tools are still a work in progress unless you also have the full version of SQL Server 2005. Hope this clears things up a bit.

Reply | Quote

Let me re-cap this.

Alternative one

Reply | Quote

Just what is the nature of the database you’re proposing? Your users wouldn’t ordinarily be creating their own queries anyhow, so all copies of the front end would contain the same queries, which would run locally. It is also possible to use passthrough queries with a SQL Server backend, which are executed in SQL Server. The several scenarios aren’t quite as cut and dried as you appear to think.

Reply | Quote

---

As the queries are on the user machines, I presume I would have the problem of having to import the full tables to the user

Reply | Quote

Let me try and clarify the problem, and then I will go study networking.

Suppose I have five non-networked PC’s in an office with Windows Server 2003 networking another 25 PC

Reply | Quote

My mind is still boggling at non-networked PCs. I didn’t think there were any left in today’s offices.

Do I understand that you want to “network” these non-networked PCs by using peer-to-peer (wired, I assume, or are you talking wireless?). In most offices, someone is in charge of networking and you will come to grief if you roll your own. However, if you do succeed, then the only practical approach is to have the back end database on a single machine, with copies of the front end, linked to the single backend on each of the others. Especially on peer-to-peer, it is NOT a good idea to try to run the application over the network.

Reply | Quote

Not necessarily peer-to-peer. My question is what options do I have. P-to-P is one. Tie into the existing network and put my back-end on the Windows 2003 server? Can I run SQL server concurrently on the same server as Windows 2003?

I would really appreciate your opinion on this.

Reply | Quote

Windows 2003 is a server OS. On a network, it is common to run SQL Server on Window server.

Reply | Quote

Charlotte and Wendell – Thank you so much for this discussion. Most informative.

Reply | Quote