February 6, 2006 at 10:13 am
It all depends upon what you want level of reliability you want from your systems. If your client is happy for you to restore the databases if you make a mess of the data, no problem.
Do you give everyone SA access? Some people do as it makes life easier - the users can do what they like to the data and so they are happy. They are not constrained by the application. but when it goes wrong, where do the fingers point. Who has to work all night to get the system sorted out - if that's even possible.
I just think that an article showing "this is a good way to modify data and do reporting" is irresponsible. Just because it's easy or quick doesn't make it right.
You should avoid getting into bad habits and try to abide by best practice where possible. If you get agreement with the client/business unit that you are working for that what you are doing is acceptable, fine. I would get them to agree that if it all goes wrong, they won't fire you too.
regards
Neil.
February 22, 2006 at 8:58 pm
We very much enjoyed the interaction above it seemed in large part around the relative merits of using Access and SQL Server. Obviously, you have stirred comment from some folks with many deep years of experience who in short know what they are talking about. Being complete newbies to SQL Server (with some Access experience), and having searched the Microsoft site and elsewhere quite hard, we found your article excellent and fills what appears to be some real hole. Well done!
We did however have two rather basic (embarassing?) technical detail questions which I am sure are crystal clear to your expert audience to date, but were not clear here to novices. A little light for the incognenti would be very greatly appreciated!
1. Does the method you employ assume that you have SQL Server installed, ie, that one is not just linking to a say single table created by SQL Server? What if you dont?
2. What is the significance of the name "Northwhich" in the middle of your article? (big/none?).
We much appreciate your clarification here.
regards,
Simon.
February 23, 2006 at 2:04 am
Hi Simon,
"Northwhich" you probably mean NorthWind. Its a template database the Microsoft ships with its SQL server products. Everybody has a copy of it when they install sql so it is very useful when showing examples of code and how-tos.
I may get corrected on the second point but I dont see how you can have a single SQL sever table without installing an instance of SQL Server somewhere.
Sam
February 23, 2006 at 11:15 am
Hi Sam:
Thank you, you are indeed right that it was, "NorthWind", the SQ Server trial database.
We are probably coming at things far too much from a desktop, non dba, end-user, MS-Access, sort of point of view!! However, if say perhaps one has temporally lost one's SQL Server expertize, or, perhaps say is just out on site with MS-Access installed on a laptop, without SQL Server server sofwtare, but with a SQL server table to be viewed, to the ignoramus, it at least feels like it is a reasonable thing for there to be able to be some way to translate or open the SQL Server table ("SQL Server Database Primary Data File") without necessarily having to install (or, learn!) SQL Server. What critical services does the SQL Server provide? Obviously, it must be some -- but it seems from Mr. Warren's script that MS-Access is in fact "driving" much of the process? We are obviously missing some dba 101 here -- but also feels like there ought to be some way to a translation between MS products (without necesarily both dbms being instaled on the same mc!).
Really appreciate any further thoughts here!
Regards,
Simon
February 24, 2006 at 8:42 am
The one thing that has kept us in our MDB is the large inventory of Access queries we call on. For many applications, you start with Access because it is both a good quick start and a fine long-term development tool. Then you build up a lot of queries, and you can't just dump them in SQL Server, as you can the tables.
We moved our tables to SQL Server eons ago, maybe 18 months.
We're just now actively involved in converting the Access queries to SQL Server views, but that is not a trivial process. I had to wait until I had an assistant because I couldn't keep up with the application development and take on time-swallowing tasks like editing views.
With over a hundred queries to convert, I found it worthwhile to automate writing the SQL out to a text file, replacing * with % and so forth as we went, and to write a test suite (in Access) that gets recordsets on both the original query and the SS view and then comparies rows, column counts, column names, and field-by-field data values.
All the while, the app itself continues in the MDB and little by little we get the SS views in place, remove the local query, and link to the view. When the last query bites the dust, we will go to ADP.
February 24, 2006 at 9:35 am
Well, thanks again for even more useful information. I just got our "convert the queries to view" project under way in prepartion for moving to an ADP, and here's the reason (http://www.databaseadvisors.com/gazette/sqlexpress.htm) not to make that move.
Oh, well, moving the queries to SQL Server won't hurt performance, and it removes one more tool a clever hacker could get into to cause mischief.
January 25, 2007 at 11:11 pm
Great article Andy, thank you so much.
Naser
November 2, 2007 at 12:49 am
Excellent way of showing the example with meaningful image clips for data extraction.
November 2, 2007 at 8:56 am
I use .mdbs for front-end applications for many of the reasons mentioned above. My backend databases are SQL Server 2005. While I am very comfortable using EM and am constantly write stored procedures to get or process data, using linked tables and pass-through queries also have important roles in Access .mdb front-ends. (I use .mdbs over .adps for many of the reasons already mentioned, especially the ability to have local tables. And I don't need to expose triggers or views since I can easily work with them in EM and take advance in the .mdb.)
Consider this example: Suppose you have a stored procedure or view that returns the data you need for a listbox (something non-editable). You can get good performance by calling that stored procedure via a pass-through query. In other words, you can use stored procedures or views WITH pass-through queries for top performance. It's not an either-or proposition. Now, that pass-through query needs a way to connect to the database. You can use an ODBC DSN (a system DSN or file DSN) as explained in the article, OR you can use the idea below.
One Idea : Skip the DSN and Still Link to Your Tables
I'm starting to play with going DSN-less. The article was focused on making a quick connection to a database in order to do some quick data manipulations. Using DSNs are probably the way to go for that purpose. However, this discussion has ranged into using Access for some front-end work, even for something as little as allowing staff to do some basic data entry and then get reports out. When Access is a front-end application, you might consider basically embedding the connection string in the linked table or pass-through query so that you don't need the DSN at all.
Why skip the DSN? Because if you want your application to be self-contained, then you will probably have code to create a system DSN automatically on start-up. For code to create a DSN, the user needs to be a local administrator, which is getting to be a problem. Or if you go with a file DSN, you have to deal with managing and circulating a file. In other words, it is all extra, somewhat chancy work. Going DSNless can be done relatively easily using some code for linking tables and queries. For example, on start-up, I have some code that loops through pass-through queries and links to the relevant databases for each query. This makes it easy for me to switch back and forth between connecting to test databases and production databases. The code for setting connection strings could use a DSN or go DSN-less. If it is just as easy to go either way, why not go DSNless? (Actually, I have an argument for keeping the DSN, but this comment is already too long...)
Just some thoughts.
November 2, 2007 at 9:03 am
Here's a tip for upgrading you Access front end to use a SQL server DBMS.
1) Copy the MDB to a backup location
2) Import your MDB data to SQL Server
3) remove the tables (Yes, remove them)
4) link in the tables from SQL Server
5) Rename the links and remove the DBO prefix
6) now all your access code will reference the linked tables by thier original table names. This will mean that your queries, reports, etc don't need to be fixed.
In less than 1 hour you can move your application to SQL server without rewiting your access code.
Hope this helps
November 2, 2007 at 9:09 am
Going DSNless can be done relatively easily using some code for linking tables and queries.
Not only that, if you go the code route you can select the index to use and the name of the table or view - rather than dbo_something you can have a "sensible" name. We use three tables - one for tables (none) and views (many) one for the indexes to be created and one for passthroughs which includes the SQL string and the local name. We generate the conection string in a local function using a number of variables and constants - in general the server name is a constant and the actual database is a variable. It all works very smoothly.
Interesting point about the article is that it failed to look at the ODBC set up for network users. We have it set up so that my data sources is in my documents on the network which is the basic location for file dsns which we sling around at a rate of knots. This helps get round some of the problems with setting up dsns.
November 2, 2007 at 9:12 am
Doug Stoltz (11/2/2007)
Here's a tip for upgrading you Access front end to use a SQL server DBMS.1) Copy the MDB to a backup location
2) Import your MDB data to SQL Server
3) remove the tables (Yes, remove them)
4) link in the tables from SQL Server
5) Rename the links and remove the DBO prefix
6) now all your access code will reference the linked tables by thier original table names. This will mean that your queries, reports, etc don't need to be fixed.
In less than 1 hour you can move your application to SQL server without rewiting your access code.
Hope this helps
That's ok for small volume: low number of users, small data sets. But you're still dealing with moving buckets of data across the network. If the application grows, and it usually will, you're going to have problems.
You're better off making the investment to split the development with SQL/Views/Procs and a VB/VC/Vwhatever front end to exploit the client/server paradigm.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
November 2, 2007 at 9:14 am
Joe Bigler (1/26/2006)
Has anyone seen a tool for connecting to SQL server with the capabilities of Access that would run on the MAC platform as well as a PC? We have a large MAC population here. I often use Access as a front end for staff assistants to enter data into SQL and print reports. I would like to be able to write one application that would work on both MAC and PC. Any thoughts or ideas?
About your only option is Parallels or similar virtualization software on your clients to run Windows. I just went Mac for my personal work and have an XP Pro partition with SQL Server 2000 and 2005, maybe 2008 tonight.
I was VERY disappointed in the eval edition of FileMaker that I wasted time downloading. I do so wish MS would port Access to the Mac, but that's one of their "lock" items and it just ain't gonna happen.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
November 2, 2007 at 9:24 am
Wayne, I agree with you on that. The renaming of the links applies to linked views as well. The idea is that you link and rename the objects instead of updating all the code that uses these objects.
Its all about upgrading and minimizing the rewriting of the presentation layer. You get the performance of a superior DBMS with minimal code changes.
November 2, 2007 at 9:24 am
I love Access. I've used it since the 1.0 days on Windows 3 (or was it WfW 3.11?) I could put up with Windows crashing 3+ times a day because it made my database development work SO much easier. Then NT 3 came out, and my many-per-day crashes went away.
I've been fortunate in database development, I went from dBase III v1.0 (barely missed having to work in dBase II!) and FoxBase to Wang Pace. Pace was a fully relational system with a wonderful data dictionary, but solely on Wang mini computers. Then I had to back-slide and started working in DataFlex when I changed jobs. If you're not insane before you start working with DataFlex, it's enough to push you over the edge. It's not really relational, though it claims to be, and has a VERY ugly procedural language!
Then SQL Server and Access came along and I was a very happy man. The only thing that I don't like about Access is that most places seem to buy Office Pro licenses, and Access in the hands of an amateur is a frightening thing. How about things that look like command buttons aren't, and things that don't look like buttons are, all on top of bright green and purple form colors?
Access serves its place quite well as a small group, small database, multi-user system. But it needs to be watched, along with network utilization, and rewritten and upsized for SQL Server when it gets too big. I love it as a quick & dirty system, and I think it thoroughly kicks Crystal Reports in the butt for report writing. It was also quite useful for querying, especially cross-tabs/pivots, but now we sort of have pivots in SQL 2005.
I'll continue using Access as long as I'm developing/adminstrating SQL Server.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 15 posts - 31 through 45 (of 71 total)
You must be logged in to reply to this topic. Login to reply