 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / SQL Server / ADP / March 2010Connect to multiple SQL Server databases
I currently have multiple access databases that are linked to different access databases also. Because of performance issues (i.e. servers in different locations), I'm going to convert these access databases to access projects. So I wanted to know if I will be able to link to different SQL Server databases? And if so is it possible to do this through code, because this will be distributed to over 50 users. Thanks in advance for the help. An ADP project can cannect to only one server at a time. Of course, you can also make your own queries to any databases using ADO and a proper connection string or you can etablish a linked server. Of course, there is also this subtle distinction between multiple databases on the same server or on different server. I never worked with ADP against multiple databases located on the same server; so I cannot tell if you will or not encounter problems but you can try it, if you want. The fact that you want to work against multiple databases looks also suspicious in your case. If you are doing this in order to circumvent the database size limitation of SQL-Server Express instead of buying a regular edition of SQL-Server, then you don't really save any money. In your case, you should also consider the possibility of using Terminal Server or Citrix instead of migrating to SQL-Server.  SignatureSylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) >I currently have multiple access databases that are linked to different > access databases also. [quoted text clipped - 9 lines] > > Thanks in advance for the help. Thanks for the quick response. The reason I was thinking of having multiple SQL Server databases was because right now I have three different access databases. My logic for this was because there are two different servers on each coast. Right now everyone is using a server on the East Coast. So the West Coast users are having significant speed issues. So I was going to use replication of the SQL Server databases. This was going to solve the speed issues. How would Citrix or Terminal Server solve this problem? >An ADP project can cannect to only one server at a time. Of course, you can >also make your own queries to any databases using ADO and a proper [quoted text clipped - 18 lines] >> >> Thanks in advance for the help. Hum, my last message doesn't seem to show up, so here's a second try: > Thanks for the quick response. The reason I was thinking of having > multiple > SQL Server databases was because right now I have three different access > databases. Working with three databases located on the same server or with three databases located on three different servers are two very different things and each situation will have its own share of problems. However, from your description of the problem, it's hard to say which one of these two possibilities is your case here. > My logic for this was because there are two different servers on each > coast. > Right now everyone is using a server on the East Coast. So the West Coast > users are having significant speed issues. So I was going to use > replication > of the SQL Server databases. This was going to solve the speed issues. You're right, using replication will solve the speed issues for people working on the remote LAN. As far as ADP is concerned, an ADP database project won't care if the database that it's connecting to is replicated or not. However, as you will be using replication, you might - or might not - be hit by the many problems that replication can bring on some occasions. > How would Citrix or Terminal Server solve this problem? TS/Citrix are advanced remote desktop functionality. They are able to solve the problem of using Access over the WAN (Wide Area Network) quite easily. The speed will not be as perfect as with replication but you save yourself from the many problems that replication can bring on many situations. Also, with TS/Citrix, you don't have to switch to SQL-Server and you can keep your old Access/JET backend. (However, as this is for a company, using Access/JET as the backend is not necessarily the best choice that you can make in life.). You can search Google for Access/Terminal Server. Another possibility would be to use synchronisation instead of replication. Much less trouble than replication but still can be troublesome. Finally, you could also use an ADP project to work over the WAN; however, this requires some deep understanding on how to optimize an ADP project; otherwise, you might very well end up suffering the same speed problems as with a regular Access frontend. SignatureSylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) Sylvain, Thanks for all the info. The three databases would be on the same server. I'm also wondering if I might be better off using SQL Server as the backend for the databases, and keep the current Access front ends and just link to the SQL Server dbs. Then this would get around the issue of using adp and linking to multiple SQL Server dbs. What type of issues do you run into with replication? I've never had to do this before. Do you think I would be better off using Citrix if I can instead of replication? >Hum, my last message doesn't seem to show up, so here's a second try: > [quoted text clipped - 40 lines] >otherwise, you might very well end up suffering the same speed problems as >with a regular Access frontend. >Because of performance issues (i.e. servers in different locations), I'm >going to convert these access databases to access projects. I'm not the expert that Sylvain is but I wonder if converting to ADPs would make any significant performance difference. Tony SignatureTony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ For a convenient utility to keep your users FEs and other files updated see http://www.autofeupdater.com/ Granite Fleet Manager http://www.granitefleet.com/ No problem here running one of my program over the WAN using either directly the ADP project or TS on a very ordinary high speed internet connection. The ADP without TS is a very little bit slower opening a form, moving between record or writing a new record but once the form is open, it doesn't exhibit the usual jerks of TS. Both modes are used and one person is obligated to use the direct ADP version because the hosting company has not been able to set up correctly on the TS the driver for the special printer that he need to use. (I'm not saying here that the failure of installing the driver has been the responsability of the hosting company because it's quite possibly that's the driver itself that is faulty. I'm only saying that this printer doesn't work correctly/reliably over TS at the present time.). As far as I can remember (it has been four years now), this is a very ordinary, plain vanilla ADP project with no unbound form or any other trick to accelerate the communication over the Internet. SignatureSylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) >>Because of performance issues (i.e. servers in different locations), I'm >>going to convert these access databases to access projects. [quoted text clipped - 3 lines] > > Tony >>Because of performance issues (i.e. servers in different >>locations), I'm going to convert these access databases to access >>projects. > > I'm not the expert that Sylvain is but I wonder if converting to > ADPs would make any significant performance difference. Based on Microsoft's recommendations, I don't think they's say that it would. It will be interesting to see what happens to ADPs in the next version of Access (i.e., the one after 2010). SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ >>>Because of performance issues (i.e. servers in different >>>locations), I'm going to convert these access databases to access [quoted text clipped - 8 lines] > It will be interesting to see what happens to ADPs in the next > version of Access (i.e., the one after 2010). Any approach to working with SQL Server as the backend should give good performance over a WAN as long as you adjust forms to minimize the number of round-trips to the database and the amount of data being retrieved. I found it necessary to change from the typical Access approach of using Select * From MyTable as a form record source to letting the user specify a single row (or a small set of rows) to be retrieved and then updating the form record source. So the base form record source could be Select * From MyTable Where 1=0, returning no rows. An unbound textbox or combo box lets the user select a record to see, and the after-update event code updates the form record source to retrieve the specified row, or hopefully small set of rows. If the underlying table has more than a few thousand rows, forms open faster if the unbound combo box is only filled after the user types a few characters, again limiting the data retrieved. Report performance depends on how much data is being retrieved, and it can be harder to improve, but for most apps I've done the reporting is used much less frequently than data management. Hi Paul, Thanks for the info. I usually try to do what you suggested with the recordsource for the form. But I don't think I'm going to be able to do that with these forms. At this point there are only a 1,000 records so it's not an issue at this point. Do you think it's a good idea to stick with the normal version of Access and just link to the three database in SQL Server or going with an adp setup? >>>>Because of performance issues (i.e. servers in different >>>>locations), I'm going to convert these access databases to access [quoted text clipped - 18 lines] >most apps I've done the reporting is used much less frequently than data >management. I've used ADP's, but I don't have any experience with direct linking to make a comparison. Plenty of people have suggested that either approach works fine, so I think it's ok to try whichever you prefer. You mentioned 3 databases in SQL Server. Is it really 3 db, or 3 tables? If it's 3 separate databases required in a single Access application, you probably have an easier time with linked tables in an Access mdb. The adp presumes you are working with a single database. You could probably work around that, but I'll guess it would be easier with linked tables. You're right that 1000 rows should not be a serious performance issue. > Hi Paul, > [quoted text clipped - 38 lines] >>most apps I've done the reporting is used much less frequently than data >>management. It's actually three separate databases not three tables. Each one is a separate program but two of the access front ends use tables in the other databases. I'm waiting to go back to this consulting job to finish this project. But I think I'm going to try converting the access databases to SQL Server databases and linking the tables. >I've used ADP's, but I don't have any experience with direct linking to make >a comparison. Plenty of people have suggested that either approach works [quoted text clipped - 12 lines] >>>most apps I've done the reporting is used much less frequently than data >>>management. If the SQL Server databases are all on the same server, you can link to one database, and within that database, create views or stored procedures that access data in the other two databases. You use the three-part name instead of just schemaname.objectname: SELECT colname FROM MyDb.MySchema.MyTable --Mary >It's actually three separate databases not three tables. Each one is a >separate program but two of the access front ends use tables in the other [quoted text clipped - 20 lines] >>>>most apps I've done the reporting is used much less frequently than data >>>>management. I may wind doing what you suggested in the end. The problem was that when this project started it was only going to be used by five people in one location using the same server. Now it may be used by as many as 100 users and in different locations. I'm trying to avoid having to rewrite all the original code. That's why I was thinking of just linking the SQL tables through code and then I wouldn't have to rewrite the existing code. >If the SQL Server databases are all on the same server, you can link >to one database, and within that database, create views or stored [quoted text clipped - 10 lines] >>>>>most apps I've done the reporting is used much less frequently than data >>>>>management. Access isn't designed to handle that many users in different locations, but SQL Server is. As you said, the original app was used by five people in one location, meaning no thought whatsoever was given to the challenge you now face. It would be unrealistic to expect that code to be robust when the parameters of the project have changed so drastically. --Mary >I may wind doing what you suggested in the end. The problem was that when >this project started it was only going to be used by five people in one [quoted text clipped - 19 lines] >>>>>>most apps I've done the reporting is used much less frequently than data >>>>>>management. So at this point I'm getting confused. What I'm trying to find out at this point is if I'm better off converting the databases to Access adp program or just leave the front ends and convert the backends to SQL server databases. I need to connect all the front ends to different tables in the backends. >Access isn't designed to handle that many users in different >locations, but SQL Server is. As you said, the original app was used [quoted text clipped - 10 lines] >>>>>>>most apps I've done the reporting is used much less frequently than data >>>>>>>management. My recommendation would be to NOT use ADPs because it limits you in terms of flexibility. Using ODBC linked tables does not restrict you to a single database connection, and you have the ability to cache data locally in Jet tables. In short, it gives you more control. Have you read the Optimizing Access Applications Linked to SQLS whitepaper yet? If not, it's at http://msdn.microsoft.com/en-us/library/bb188204.aspx. It will give you the background information on how the Jet engine works with the SQLS engine, and how to get the most out of your converted application. The FMS site also has some free migration papers -- http://www.fmsinc.com/Consulting/sqlupsizedocs.aspx. They have a lot of first-hand experience, as does Jstreet -- http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp. --Mary >So at this point I'm getting confused. What I'm trying to find out at this >point is if I'm better off converting the databases to Access adp program or [quoted text clipped - 15 lines] >>>>>>>>most apps I've done the reporting is used much less frequently than data >>>>>>>>management. Marh, Thanks for the info. I'll look at those links to the white papers. My last question would be about distributing the applications. I was thinking about coding the links to the SQL Server tables in the different databases. This way I the ODBC connections wouldn't have to be setup on each computer. Does this seem like a good alternative? >My recommendation would be to NOT use ADPs because it limits you in >terms of flexibility. Using ODBC linked tables does not restrict you [quoted text clipped - 17 lines] >>>>>>>>>most apps I've done the reporting is used much less frequently than data >>>>>>>>>management. Yes, dynamically connecting in code makes the most sense, especially if you need to use SQL logins (firewall issues) by submitting the user name and password at run time. Also, you'll want to avoid linking to entire tables as you'll need to send more metadata over the wire. If you think of creating a lightweight Access client as though you are creating a Web application, you'll be on the right track, so avoid linking to tables, use views that restrict the number of rows returned, or stored procedures for RO data. The big differentiator from what you have now is that you will be losing the LAN for a WAN, so you have networking and throughput issues you never needed to consider before. Your motto should be, "fetch only needed data" to cut down over the amount of information that needs to go over the wire for every call. You should also get comfortable using SQL Profiler as this is the only way you'll be able to troubleshoot problems. HTH, --Mary >Marh, > [quoted text clipped - 25 lines] >>>>>>>>>>most apps I've done the reporting is used much less frequently than data >>>>>>>>>>management. your motto should be 'use a platform that supports wan connections without corruption and performance nightmares' On Feb 12, 8:38 am, "Mary Chipman [MSFT]" <mc...@online.microsoft.com> wrote: > Yes, dynamically connecting in code makes the most sense, especially > if you need to use SQL logins (firewall issues) by submitting the user [quoted text clipped - 45 lines] > >>>>>>>>>>most apps I've done the reporting is used much less frequently than data > >>>>>>>>>>management. > My recommendation would be to NOT use ADPs because it limits you in > terms of flexibility. Using ODBC linked tables does not restrict you > to a single database connection, and you have the ability to cache > data locally in Jet tables. In short, it gives you more control. > > --Mary Maybe flexible but inherently slow; so slow in fact that over the Internet (WAN), you need to use Terminal Server; in which case using a local Jet database is totally pointless. The flexibility of ODBC Linked Tables to work against multiple databases is often pushed forward; however, it might also be the most useless feature of Access when working against a SQL-Server database. There is no magical recipe. Only by trying both solutions will the OP be able to determine what he need and don't need and after that, be able to make a correct decision. SignatureSylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) Mary, Can you please elaborate on the limits you speak of here. I would also be very interested to hear what flexibility is lost as well. Thanks in advance, ..bob > My recommendation would be to NOT use ADPs because it limits you in > terms of flexibility. Using ODBC linked tables does not restrict you [quoted text clipped - 36 lines] >>>>>>>>>data >>>>>>>>>management. You should read the Optimizing Access Applications whitepaper listed below, which has a more detailed explanation than would easily fit in a newsgroup post. At the time Andy and I wrote our book (parts of the paper are based on it) we did extensive testing and original research to reach our conclusions, which ran contrary to msft marketing at the time. It's still in print if you want a longer read http://www.amazon.com/dp/0672319446 :-) --Mary >Mary, >Can you please elaborate on the limits you speak of here. [quoted text clipped - 43 lines] >>>>>>>>>>data >>>>>>>>>>management. Hello Mary, Thanks for the reply. I don't plan on purchasing the book. I only asked because... I have been using .ADP's for years over a WAN with no issues. There are many different applications from collections to dispatch to Executive reporting and views. One application that integrates closely with dispatch is Service. We have 9 branches across the east coast with multiple users from each service department using .adp front ends pointing at SQL2005 & SQL2000 backends located at our corporate office. Workflow (with good speed ) between dispatch and service and credit and trucking.. is all handled without issue. thanks again for the reply.. ..bob > You should read the Optimizing Access Applications whitepaper listed > below, which has a more detailed explanation than would easily fit in [quoted text clipped - 57 lines] >>>>>>>>>>>data >>>>>>>>>>>management. It sounds like ADPs are a good fit for your business needs. One reason I didn't mention in my previous posts about why msft doesn't recommend them for new applications isnt' because they don't work at all, or aren't appropriate for some applications, but because for later versions of SQLS (2005 on) they haven't kept up with the back-end designer functionality people expect from Access with a Jet/ACE back-end. Access product releases are out of sync with the SQLS SKUs as Access is part of Office, not SQL Server, so there is no way to catch up. Plus, SQLS 2005 introduced features, such as hosting the CLR, which would be impossible to support. Even if you leave out all of that, a developer still needs to use SSMS (or SEM for SQLS 2000) to adequately create and secure back-end SQLS objects. Given that ADP/SQLS developers represent only a tiny sliver of the total Access user base, the team elected not to devote resources to SQLS, but instead to Sharepoint integration for Access 2007 and beyond, which has been wildly successful. ADPs will of course continue to be supported, and perhaps improved on in future versions of Access, it's hard to say at this point. So it really isn't so much a religious issue as a practical one in terms of which Access client or development environment makes the most sense in a given situation. Sadly, there isn't much in the way of prescriptive guidance that is targeted for specific business and deployment scenarios, so someone coming fresh to the stack has to amass the information they need from disparate sources in order to reach an informed decision about the best way to proceed given their business requirements. I hope this clarifies my previous comments, --Mary >Hello Mary, >Thanks for the reply. I don't plan on purchasing the book. I only asked [quoted text clipped - 76 lines] >>>>>>>>>>>>data >>>>>>>>>>>>management. Thanks for the detailed explanation Mary. I understand where you are coming from. I guess it works for me because I spend most of my time in SQL. I build my solutions using SSMS and find the flexibility to do whatever the project scope demands. The environment that Access provides is unmatched from a RAD front end interface perspective. I think that combining these two allows me to capitalize on the best of both worlds. > Sadly, there isn't much in the way of prescriptive > guidance that is targeted for specific business and deployment > scenarios, so someone coming fresh to the stack has to amass the > information they need from disparate sources in order to reach an > informed decision about the best way to proceed given their business > requirements. I hope this clarifies my previous comments, -- I agree with you on this Mary. It's a whole different mindset shifting from Jet and recordsets to SQL and set based programming. Once you get past that though.... It works very well. Thanks again for the detailed reply Mary. ..bob > It sounds like ADPs are a good fit for your business needs. One reason > I didn't mention in my previous posts about why msft doesn't recommend [quoted text clipped - 111 lines] >>>>>>>>>>>>>data >>>>>>>>>>>>>management. Same here. Honestly, the usual recommendation of MS about using ODBC Linked Tables instead of ADP because they allow you the flexibility of using local temporary tables is like a crutch: it's useful to have a crutch if you have a broken leg, so we suggest you to break one of your legs in order to be able to use our crutches. Even when you're not going over the WAN, simply taking a look with the SQL-Profiler to see what's going on behind the scene when ODBC Linked Tables are used in comparaison to ADP make the decision of using the former more or less ridiculous. They might be usable for small database projects, with only maximum of 20 users and a light load but for anything bigger, they are a dead end. As to the fact that the design tools of ADP have not keeping it up with the capacity of the latest SKU of SQL-Server, this is true but we can not even make a comparison with ODBC Linked Tables on this ground: there not even to be seen on the field! MS has decided to phase-out ADP in favor of ODBC Linked Tables but not because the later are superior to the former, only because they want to push users of Access toward Sharepoint in one side or toward the .NET platform on the other side. It's essentially a marketing decision, with no technical merit behind it. This decision has nothing to do with SQL-Server but all with Sharepoint and the .NET platform. SignatureSylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) > Hello Mary, > Thanks for the reply. I don't plan on purchasing the book. I only asked [quoted text clipped - 77 lines] >>>>>>>>>>>>data >>>>>>>>>>>>management. >MS has decided to phase-out ADP in favor of ODBC Linked Tables but not >because the later are superior to the former, only because they want to push >users of Access toward Sharepoint in one side or toward the .NET platform on >the other side. It's essentially a marketing decision, with no technical >merit behind it. This decision has nothing to do with SQL-Server but all >with Sharepoint and the .NET platform. The reasoning behind msft's recommendations regarding ADPs is spelled out in the following paper: http://technet.microsoft.com/en-us/library/cc178973.aspx "The preferred way to connect to SQL Server is MDB file format or ACCDB file format. This enables you to use the full flexibility of local tables and local queries, while leveraging the full power of SQL Server. In addition, MDB and ACCDB files link to multiple SQL Server computers and a wide variety of other data sources. Office Access 2007 contains many new features that are available in both MDB and ACCDB file formats, but only a subset of those features are available in ADPs." ADPs are not being phased out, they are still supported and will continue to be supported. The next version of Access is in the planning phase, so it's impossible to predict where the Access team will invest in SQLS support. For Access 2010, download this whitepaper: Improving the Reach and Manageability of Microsoft Access 2010 Database Applications with Microsoft Access Services http://www.microsoft.com/downloads/details.aspx?FamilyID=54e6aa02-c500-46ba-a930 -bfd5c9f43edd&displaylang=en. I hope this clarifies things. --Mary I'm working in Access 2003 is there a way to link the tables through code the way you can with DAO? Right now this is coded using DAO to connect to three different backends. If I could do the same through with SQL Server I think this might be the best way to go with this project. >>MS has decided to phase-out ADP in favor of ODBC Linked Tables but not >>because the later are superior to the former, only because they want to push [quoted text clipped - 26 lines] > >--Mary Yes, it works the same way with SQL Server as it does with Jet. You use DAO to create links to the back-end SQLS tables the same way you do now, except you supply different connection information. Access creates TableDef objects in the front end, pulling in metadata from SQLS. When you query the linked tables, it uses the metadata, which includes connection information, to retrieve the actual data from the server. The information you need is contained in the Optimizing Access apps linked to SQLS paper I pointed to earlier in this thread (http://msdn.microsoft.com/en-us/library/bb188204.aspx). Also, take a look at Armen Stein's site -- he's got great resources there for working with Access 2003 and SQL Server: http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp. --Mary >I'm working in Access 2003 is there a way to link the tables through code the >way you can with DAO? Right now this is coded using DAO to connect to three [quoted text clipped - 31 lines] >> >>--Mary Mary, Thanks again for all the information you've given me. I'm not going to go back to this project for about a month or so. So I'll have time to decide which way to go. At this point I'm pretty sure that I'll convert the Access back ends into SQL Server databases. From there I will most likely use DAO to link to the SQL Server tables. >Yes, it works the same way with SQL Server as it does with Jet. You >use DAO to create links to the back-end SQLS tables the same way you [quoted text clipped - 16 lines] >>> >>>--Mary >>MS has decided to phase-out ADP in favor of ODBC Linked Tables but not >>because the later are superior to the former, only because they want to [quoted text clipped - 17 lines] > file formats, but only a subset of those features are available in > ADPs." About the affirmation "the preferred way", I wonder on what fact, proof or research this statement has been based. It's true that you can use local tables and local queries but at the same time, you *must* use local queries; which also means that you must use the JET querying engine: it doesn't take long for this engine to spit out in your face some error message "query is to complex" anytime you come up with anything that's not straight simple. What people are trading in exchange for this *flexibility* is the obligation that they will have to spend - or lose - many days, weeks and months - if not years - of supplemental hours of work in order to continuously work around the limitations of the JET engine and burying themselves under an incredible amount of small queries while adding lots and lots of VBA code everywhere in an effort to glue all this together. Many years ago, I stopped using this JET engine and I switched to ADP and SQL-Server because I was sick of losing my time with this minimalist sql engine that has not been upgraded since around circa 1995. I'm not someone who, after having to chose betwen MDB and ADP, made the decision to go with ADP and remained there. Instead, I'm someone who, based on some research at that time, have made the decision to go first with MDB, ODBC linked tables and passthrough queries but who after some time, made the contastation that I was losing more time than doing useful work, concluded that keeping going this way was a big mistake for connecting to a SQL-Server, decided to switch to ADP and since then, I never looked back again. I've never heard of only one single person who, after switching to SQL-Server, has ever looked back at the JET engine. When was the last time that you have seen a person - in these newsgroups or anywhere else - asking for recommendation about switching from SQL-Server to JET? Peoples are asking if and how they can switch to SQL-Server but they never ask the opposite and this include people who are already using ADP; maybe there is reason behind that? As for the statement "leveraging the full power of SQL-Server", replacing it with "minimizing to a very, very low level the power of SQL-Server" would be more appropriate and when I'm saying that, I'm not saying while I'm totally serious; without joking at all. > ADPs are not being phased out, they are still supported and will > continue to be supported. The next version of Access is in the [quoted text clipped - 6 lines] > > --Mary This document is a marketing speech about switching to Sharepoint. I don't see any difference between this and what I've just said in my previous post and furthermore, I don't see anything in it that could help someone willing to make a connection to a SQL-Server database instead of Sharepoint. Probably one useful way for people willing to go into disconnected mode over the WAN to do it - they are also other ways - but for a live and direct connection; I don't see the point. SignatureSylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) I vote that ADP is superior to everything else. Namely for performance / reliability / extensibility reasons. I've been using ADP every day for almost 11 years now. I'd sure never use anything else, except maybe Enterprise Manager, Query Analyzer, SQL Server Management Studio, Business Intelligence Development Studio.. and 'Dreamweaver' as an IDE sometimes. Thanks -Aaron On Feb 18, 6:51 pm, "Sylvain Lafontaine" <sylvainlafontaine2...@yahoo.ca> wrote: > >>MS has decided to phase-out ADP in favor of ODBC Linked Tables but not > >>because the later are superior to the former, only because they want to [quoted text clipped - 82 lines] > Independent consultant and remote programming for Access and SQL-Server > (French) Hi Sylvain, You raise many valid points. However, there simply is not now, and never has been, a "one size fits all" solution to database application development. Many people, such as yourself, have been successful with ADPs, and many millions more have found that Jet/ACE meets their business needs without having to migrate to a server RDBMS at all. And enterprise customers have found Sharepoint to be the ideal solution for sharing data, and Access 2007 and 2010 can serve as an optimal front-end for that business scenario. A lot of research is done internally that is never made public--bugs, customer support cases, internal focus groups, and so on. Microsoft makes recommendations based on this research, decisions made "for marketing reasons only" would defeat the purpose of creating the software to begin with, which is to empower people to develop applications that give them the best chance of success. If the customers succeed, Microsoft succeeds. When was the last time you went back for more when a product didn't work or failed to meet your needs? --Mary >>>MS has decided to phase-out ADP in favor of ODBC Linked Tables but not >>>because the later are superior to the former, only because they want to [quoted text clipped - 75 lines] >the WAN to do it - they are also other ways - but for a live and direct >connection; I don't see the point. Uh, Microsoft SharePoint doesn't scale- it's not prevalent, it's not drop dead simple. It's not extensible. SQL Server is all of those things. So until they have something better than ADP.. I'll stick with the best flavor of Access ever, Access 2007 / 2010. ADP has gotten new features every version for the past decade. Same thing for SQL Server. Thanks -Aaron On Feb 22, 1:13 pm, "Mary Chipman [MSFT]" <mc...@online.microsoft.com> wrote: > Hi Sylvain, > [quoted text clipped - 100 lines] > >the WAN to do it - they are also other ways - but for a live and direct > >connection; I don't see the point. The data store for SharePoint is SQL Server, so it gives you the best of both worlds for many apps, especially in the enterprise, because all of the security, backup and UAC issues are taken care of by system administrators, not DBAs or developers. If you like Access 2007/2010, you should give Sharepoint 2010 a try -- using it with an Access front-end does make it as close to drop-dead simple as it's going to get. --Mary >Uh, Microsoft SharePoint doesn't scale- it's not prevalent, it's not >drop dead simple. [quoted text clipped - 118 lines] >> >the WAN to do it - they are also other ways - but for a live and direct >> >connection; I don't see the point. SharePoint requires ActiveX controls. SharePoint requires ActiveX controls. SharePoint requires ActiveX controls. SharePoint doesn't _WORK_ with Firefox and Chrome and Safari. Are you on crack? Access Database sucks balls. It's never been reliable enough for a single record and a single user. SharePoint is slower than Access Data Projects. And you have the audacity to spread mis-information, just because you're too stupid to learn how to use a real database. Precious. Access frontend with SharePoint?? OH, that's just hilarious.. Do you know how slow it is? About 3 years ago, it took me an HOUR to append 10,000 rows into a sharepoint list. So _WHY_ do you think that it's a reliable platform for any sized dataset? On Feb 23, 7:45 am, "Mary Chipman [MSFT]" <mc...@online.microsoft.com> wrote: > The data store for SharePoint is SQL Server, so it gives you the best > of both worlds for many apps, especially in the enterprise, because [quoted text clipped - 128 lines] > >> >the WAN to do it - they are also other ways - but for a live and direct > >> >connection; I don't see the point. Access Data Projects give you the best of both words for many apps, especially in the enterprise, because all of the security, backup and UAC issues are taken care of by system administrators, not DBAs or developers. If you like Access 97, you should give Access Data Projects a try -- using it with an Access front-end does make it as close to drop-dead simple as it's going to get. On Feb 23, 7:45 am, "Mary Chipman [MSFT]" <mc...@online.microsoft.com> wrote: > The data store for SharePoint is SQL Server, so it gives you the best > of both worlds for many apps, especially in the enterprise, because [quoted text clipped - 128 lines] > >> >the WAN to do it - they are also other ways - but for a live and direct > >> >connection; I don't see the point. I've worked at five companies in the past 4 years.. that put a million dollars into SharePoint, and then they threw it away. It's just too quirky to be practical. SharePoint is just a dead end street. It's slow as molasses. It makes everything harder. And SharePoint -USED- to be able to link to Excel.. now it's no longer a feature. You can't even use Access linked tables unless you have Windows Authentication, I mean.. SharePoint just plain sucks balls. On Feb 23, 7:45 am, "Mary Chipman [MSFT]" <mc...@online.microsoft.com> wrote: > The data store for SharePoint is SQL Server, so it gives you the best > of both worlds for many apps, especially in the enterprise, because [quoted text clipped - 128 lines] > >> >the WAN to do it - they are also other ways - but for a live and direct > >> >connection; I don't see the point. The data store for SharePoint is SQL-Server but this doesn't mean in any way that you can use SharePoint to access another database located on the same SQL-Server (unless undirectly with lot and lot of programmation) or that we can use SharePoint itself like an ordinary database. With Sharepoint, all we have is a single table, with no relationship at all to any other table; either in SharePoint or on SQL-Server. Maybe great if you want to rewrite 100% of the design of your current database so that everything will be stored in a single table but for connecting to an existing database, your "drop-dead simple" is more like to be "simply drop dead" than anything else. A lot of people thinking that they can now easily solve their problem of connecting to a remote SQL-Server by simply switching to SharePoint will have tough awakening sooner than later. Like I've already said, SharePoint might be useful if you want to go connection-less or for setting up some dedicated collecting of information but for a direct, live connection to an existing SQL-Server database; I simply don't see the point. Of course, you can alway set up some complicated way like using SSIS to continually monitor your stuff and synchronise everything between a SharePoint table and a SQL-Server database but if you were thinking about using Access and SharePoint together for saving the developing costs for coding a remote access; well, see you later in another, better world. SharePoint will be the tool of choice for big companies willing to pay big $$$ in exchange for a remote access to their databases but for smaller companies, with shallower pockets; they are in to get a big disapointment. SignatureSylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) > The data store for SharePoint is SQL Server, so it gives you the best > of both worlds for many apps, especially in the enterprise, because [quoted text clipped - 162 lines] >>> >the WAN to do it - they are also other ways - but for a live and direct >>> >connection; I don't see the point. Sylvain; I thank you for continuing to stand up for ADP! This is precious.. thanks! Re: With Sharepoint, all we have is a single table, with no relationship at all to any other table; either in SharePoint or on SQL-Server. Maybe great if you want to rewrite 100% of the design of your current database so that everything will be stored in a single table but for connecting to an existing database, your "drop-dead simple" is more like to be "simply drop dead" than anything else. -Aaron On Feb 23, 12:16 pm, "Sylvain Lafontaine" <sylvainlafontaine2...@yahoo.ca> wrote: > The data store for SharePoint is SQL-Server but this doesn't mean in any way > that you can use SharePoint to access another database located on the same [quoted text clipped - 197 lines] > >>> >the WAN to do it - they are also other ways - but for a live and direct > >>> >connection; I don't see the point. I didn't mean to imply that Sharepoint should be a replacement for SQL Server, merely that Sharepoint uses SQL Server under the hood as a data store. Obviously, any technology can be misused to create inefficient applications -- Access itself has a long history of being misused to create inefficient applications for which it was never intended. Understanding the underlying architectures of the various components involved in creating data-aware solutions is essential. That was my point in posting the links to additional learning resources earlier in this thread. --Mary >The data store for SharePoint is SQL-Server but this doesn't mean in any way >that you can use SharePoint to access another database located on the same [quoted text clipped - 23 lines] >$$$ in exchange for a remote access to their databases but for smaller >companies, with shallower pockets; they are in to get a big disapointment. SharePoint is a total and utter waste of time. You can't install SP on your workstation, for starters! SharePoint requires you to use Internet Explorer to get all the functionality.. This browser has lost 30 points of market share in the past 5 years? Why would anyone base anything on SharePoint, it's a dead weight, it's a sinking weight.. and you can't even link to SharePoint from within excel in the 2007 release. I mean.. It's got to be the most mis-managed product of all time. Easily. -Aaron On Feb 24, 10:15 am, "Mary Chipman [MSFT]" <mc...@online.microsoft.com> wrote: > I didn't mean to imply that Sharepoint should be a replacement for SQL > Server, merely that Sharepoint uses SQL Server under the hood as a [quoted text clipped - 38 lines] > >$$$ in exchange for a remote access to their databases but for smaller > >companies, with shallower pockets; they are in to get a big disapointment. re: Many people, such as yourself, have been successful with ADPs, and many millions more have found that Jet/ACE meets their business needs without having to migrate to a server RDBMS at all so HOW is it that SQL Server is ten times are popular as Jet? so HOW is it that SQL Server is ten times are popular as Jet? so HOW is it that SQL Server is ten times are popular as Jet? Just because you're a halfwit, doesn't mean that it's in the best interests of anyone else to cling to tech that has been obsolete for a decade. Maybe if you weren't of the inferior sex-- then maybe you'd have the mental capacity to learn a real database. On Feb 22, 1:13 pm, "Mary Chipman [MSFT]" <mc...@online.microsoft.com> wrote: > Hi Sylvain, > [quoted text clipped - 100 lines] > >the WAN to do it - they are also other ways - but for a live and direct > >connection; I don't see the point. "a a r o n . k e m p f @ g m a i l . c o m" <aaron.kempf@gmail.com> wrote: <snip> Your posting is highly inappropriate and exceedingly unprofessional. Please apologize to Mary. Tony SignatureTony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ For a convenient utility to keep your users FEs and other files updated see http://www.autofeupdater.com/ Granite Fleet Manager http://www.granitefleet.com/ Who do you think that you're talking to? I'm a certified MCITP: DBA. I've been using ADP every day for a decade. I've been using SQL Server every day for a decade. SQL / ADP is a superior platform.. Microsoft has never come out and said anything about the future of ADP (if anything they're wildly optimistic about it's future). Anyone using Jet for any reason should be homeless and jobless. It's not reliable. It's not dependable. It doesn't have a future. No amount of kicking and screaming and misinformation is going to change that Tony. ADPs have gotten new features with every release of Access in the past decade. SQL Server has gotten new features four times in the past decade. You and your continued misinformation is nothing but a waste of time. The funniest part is that Tony Toews started finally using ADP (and added that to his AutoFeUpdater. So now.. after a decade of breaking the law to slander my name-- Tony has finally seen the light and lost the training wheels and started using ADP. -Aaron On Feb 23, 11:45 am, "Tony Toews [MVP]" <tto...@telusplanet.net> wrote: > "a a r o n . k e m p f @ g m a i l . c o m" <aaron.ke...@gmail.com> > wrote: [quoted text clipped - 12 lines] > updated seehttp://www.autofeupdater.com/ > Granite Fleet Managerhttp://www.granitefleet.com/ >Who do you think that you're talking to? That doesn't matter. Your reply to Mary Chipman was highly inappropriate. You should apologize. >The funniest part is that Tony Toews started finally using ADP (and >added that to his AutoFeUpdater. Wrong. >So now.. after a decade of breaking the law to slander my name-- Please sue me. >Tony >has finally seen the light and lost the training wheels and started >using ADP. Wrong. Tony SignatureTony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ For a convenient utility to keep your users FEs and other files updated see http://www.autofeupdater.com/ Granite Fleet Manager http://www.granitefleet.com/ Tony; Eat sh.t bully.. You make websites, spreading lies about me.. You write letters to my employers and clients-- spreading lies about me. Seriously, you can f.ck yourself if you think that I'm -ever- going to do anything you say. Just because you and your wussy pants friends don't have the mental capacity to learn a 'real database' doesn't mean that you can run around giving misinformation. I'll stand up for ADP until the day I die. -Aaron On Feb 23, 12:55 pm, "Tony Toews [MVP]" <tto...@telusplanet.net> wrote: > >Who do you think that you're talking to? > [quoted text clipped - 24 lines] > updated seehttp://www.autofeupdater.com/ > Granite Fleet Managerhttp://www.granitefleet.com/ And for the record Tony, I think that it is highly inappropriate for you to threaten me in this regard. Seriously. Drop it fag, you are in an ADP newsgroup, if you want to spread lies elsewhere-- go f.ck yourself in another newsgroup if you want -Aaron On Feb 23, 11:45 am, "Tony Toews [MVP]" <tto...@telusplanet.net> wrote: > "a a r o n . k e m p f @ g m a i l . c o m" <aaron.ke...@gmail.com> > wrote: [quoted text clipped - 12 lines] > updated seehttp://www.autofeupdater.com/ > Granite Fleet Managerhttp://www.granitefleet.com/ >And for the record Tony, I think that it is highly inappropriate for >you to threaten me in this regard. If you feel I'm threatening you please report me to your local police force. Please, please report me. Tony SignatureTony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ For a convenient utility to keep your users FEs and other files updated see http://www.autofeupdater.com/ Granite Fleet Manager http://www.granitefleet.com/ Tony; Eat sh.t bully.. You make websites, spreading lies about me.. You write letters to my employers and clients-- spreading lies about me. Seriously, you can f.ck yourself if you think that I'm -ever- going to do anything you say. Just because you and your wussy pants friends don't have the mental capacity to learn a 'real database' doesn't mean that you can run around giving misinformation. I'll stand up for ADP until the day I die. -Aaron > >And for the record Tony, I think that it is highly inappropriate for > >you to threaten me in this regard. [quoted text clipped - 10 lines] > updated seehttp://www.autofeupdater.com/ > Granite Fleet Managerhttp://www.granitefleet.com/ high quality Soccer jerseys NBA Jersey tracksuit and jackets, GHD hairstraightener supplier from www.willpa.com Are you a Retail businessman who bother by the purchase price? China Cheapest TOP wholesale website can help you we are specialize in replica sport goods manufacturing in china, we can offer you all kinds of soccer jersey, NBA jersey,shoes and so on. they are the best brand replica goods whih are look the same as the original goods. excellent quality and steady supply for them. we have been marketed in Europe and American for 3 year. all the goods we offer are AAA quality. our soccer jersey are Thailand style. If any goods you buy from my company have problem, we will refund or resend them again. Most of ourProducts have no minimum order requirements,soyou can shop retail goods at wholesale prices. if you can buy more than 300usd. We offer free shipping. The more you buy the more discount for you. National soccer jerseys: http://www.willpa.com Club soccer jerseys: http://www.willpa.com NBA Jerseys: http://www.willpa.com T-shirt and shirt: http://www.willpa.com Tracksuit: http://www.willpa.com Hoody & Jackets: http://www.willpa.com UGG boots: http://www.willpa.com Hair style: http://www.willpa.com shopping Index: http://www.willpa.com EMS shipping. 7days arrive, paypal accept want more information pls contact us or check our website: www.willpa.com There is a lot of religion here. I would suggest that you go for yourself and try both solutions to see what happens. Only by trying both solutions that you will be able to grasp the differences between them and see which one is more appropriate to your situation. SignatureSylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) > So at this point I'm getting confused. What I'm trying to find out at > this [quoted text clipped - 20 lines] >>>>>>>>data >>>>>>>>management. I was working on this project as an outside consultant. The deal was that I would come back at the beginning of the year and finish the project. As of last week I was told that the job would now be done internally. So I guess I won't find out which solution is better for this project. Thanks again to everyone for their help and input. >There is a lot of religion here. I would suggest that you go for yourself >and try both solutions to see what happens. [quoted text clipped - 7 lines] >>>>>>>>>data >>>>>>>>>management. > If the SQL Server databases are all on the same server, you can > link to one database, and within that database, create views or > stored procedures that access data in the other two databases. You > use the three-part name instead of just schemaname.objectname: > > SELECT colname FROM MyDb.MySchema.MyTable There's also linked servers, no? SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > Do you think it's a good idea to stick with the normal version of > Access and just link to the three database in SQL Server or going > with an adp setup? Microsoft recommends MDB/ACCDB/ODBC over ADP/ADO/OLEDB, so I think I'd take their advice. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > I found > it necessary to change from the typical Access approach of using > Select * From MyTable as a form record source Uh, even with a Jet back end, it's wise to not do that! The same things that make the process efficient with a server back end also make Access/Jet/ACE more efficient. And it also means that upsizing is much less painful. Personally, I've been designing all my Jet apps with upsizing in mind since 1998 or so. The result has been more efficiency with Jet, but also ease of upsizing in the few cases where that's happened. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ ecovindaloo, explore using stored procedures to return the rows you want. The stored procedures can reside in a single db and,.. provided the users security permits,... you can query any db on any server. you connect the .adp to a sql db and use ssms to write your procedures, triggers, functions and whatever else you need. hth, bob. >I currently have multiple access databases that are linked to different > access databases also. [quoted text clipped - 9 lines] > > Thanks in advance for the help. >I currently have multiple access databases that are linked to different > access databases also. [quoted text clipped - 9 lines] > > Thanks in advance for the help. >I currently have multiple access databases that are linked to different > access databases also. [quoted text clipped - 9 lines] > > Thanks in advance for the help. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.