Best way to use Access as a front end
-
Hi folks, I've been using Access for years but this is my first time using SQL as a back end. Everything I read talks about pass-thru queries and attaching tables but don't these methods require all users to have a DSN on their workstation? I've got about 200 users and don't want to touch all the desktops.
So what I've been doing is just using an ADO connectionto hit the back end with stored procs and using GetRows and GetString to bring back the data and fill combo boxes or single record forms. For multi-record forms I'm dumping the data into jet tables sitting on the front end. All writable forms have a save button which will then send the insert, update etc. to the back end.
This way I'm never attached to the SQL Server for more than a few seconds at a time and no need for DSNs. Also if I change to ASP in the future it should be pretty easy since much of the vb can go unchanged.
Does this make sense? Opinions Please!
Mike
-
1. You can have your front-end database use a DSN file that is located on your network. This avoids touching every 200+ workstations.
2. Alternative method is creating your application using MS Access Project (ADP file extension). This will let you use Forms, Reports, Macros, and Modules within an Access Project file. However, your tables, views and stored procedures are connected and stored on your SQL Server.
-
Our Access apps create the DSN on the user's PC when the app starts. It's a neat enough solution, but I think the other suggestion of a DSN located on the network is cool - must try that one myself
-
I have got a lot of use out of the ADP(Project File) Solution. This will actually make using ado even easier because you can use the project's connection to the server to execute queries. You also will have the ability to directly bind forms to tables and stored procedures. There are a couple things to watch out for though; 1) Project Files do NOT have local data storage of any kind and 2) Each user must have a local copy of the app.
The First issue isn't a big deal and is just good client/server programming.
I've dealt with the second issue a couple ways. 1) by using a .bat file to check for an updated project file, it's it's newer, it copies the new file and then launches the app, otherwise, it just launches the app. My users use an short-cut to launch the app as opposed to launching the app directly. The other is an application launcher, written in VB.net (a really simple project, it only took an hour or so and was my first vb.NET project) to do the same function as the batch file. The difference is, the app launcher is "prettier" and a lot easier to deploy.
Just my two-cents, hope it helps!
Mark
-
the access project is fantastic.
Why?
Well, I design the web-interface for the database portion of intranets and internet sites. If I want to change values in a table, especially if it is a value in multiple tables, I have a few options. I can do it via SQL (which is a pain if it is complex). I can make a web interface (which can take a while). Or... I can make a new adp file and have a generic form put together in about 2 minutes.
Then.... I can actually let the administrative staff access portions of the systems that would not be needed by 99% of the users without spending a long time making an administrative module to handle that task. Over time, I do end up making the module, but the cost/benefit is clear.
Personally, I NEVER use it as a final solution, simply because it is a bit hard to manage remotely. Also, as a small business owner, I can create a financial dependancy between my company and the client via a simple $200-500 retainer for maintaining a web interface.
It is a piddleing amount for an insurance company or a bank, yet when they need a modification, there is simply no option to hire a different agency to do the work. With a local system like a project or access db, after a while, they simply forget you or end up having someone else do bits and pieces.
Anyway, back on point: the ADP is one of the most powerful tools available for development work with SQL Server under any situation. It is a fantastic money and time saver for administrative units to any DB that is accessable via LAN, VPN (or even a carefully set up internet connection over port 1400).
Developers constantly dismiss it or don't even know it exists. If you don't know what it is - give it a shot. If you do, and dismiss it, that's just silly.
-
Thanks, everyone, for the great replies.
It sounds like I can go ahead and use the DSN and ADP if I like. I really like being able to store some temp data locally though. I've got some users that have pretty slow connections thru the mountains, so anything I can do to make it faster will help ensure acceptance. On the flipside, I've got about 30 legacy Access databases that eventually need to go to SQL, so using the ADP will speed that development up significantly.
How about security with the ADP though? I'm doing a timecard app so while I need users to be able to write to the main timeEntry table, I don't want them to be able to get to that table for direct editing, etc.
-
Actually, ADP's seem to be on their way out. Look around the web, comp.databases.ms-access (newgroup) for example, and you will see sad comments that ADP's are no longer even recommended by MS.
You can do what you want with an MDB and DSN-less connections. It is especially tidy if you are using Windows Authentication.
Where you probably already have links set up, referring to the DSN, you would be in a position to just change those links using something along this line:
for each tdf in currentdb.tabledefs
tdf.connect = "ODBC;Provider=SQLOLEDB;DRIVER=SQL Server;SERVER=myservername;DATABASE=mydatabase;Trusted_Connection=Yes"
next tdf
If you need to create links in the future, you can use either the tabledef collection (add a new tabledef and set its connection just as above) or use docmd.transferdatabase.
One nice thing about using the DSN-less approach and having code that sets the connections is that it makes it pretty easy to run your app in different "modes" and detect that mode when you're setting connection strings. That way you can easily address data in a live instance, a devlopment instance, a training instance, etc etc etc.
-
MS is looking to phase out Access altogether. Its architecture is largely obsolete, and doesn't work well with either SQL Server or .NET. My money is on them coming out with some new desktop-oriented database system that essentially runs on MSDE.
That said, Access is still a fantastic product with a lot of potential.
The nice thing about ADP's and security is that it uses SQL Server's security for all data access. You're using SQL Server tables, views, and stored procedures, and can implement full security on those. The only things staying in the ADP file are the forms, reports, and code, all of which you can put Good Enough Security(TM) on, just with the Access tools. Or even a step better if you choose to compile it as an ADE file.
-
I believe it was Mark Twain who once said, "The rumors of my death have been greatly exaggerated." The same is true of Access, which has been the subject of that very rumor for years now.
In fact, with the totally redesigned version of Access, currently in beta, I think MS has made it very clear that they intend to grow their Access user base by making it much more accessible to the new user and much more powerful for more serious applications, such as integration with Sharepoint Services.
It is true that the file format for the new Access database is different; Jet is no longer going to be the engine in Access. And that is a good thing as well.
IMHO, Access will always be the tool of choice for a large, and growing, base of users who simply don't have time to wait for the "IT guys" to get around to addressing their needs with SQL Server and a cool .net application. Many, if not most, of the Access databases that get built today are found in small group settings, or on a single desktop where the people who use them are more concerned about getting their jobs done than they are about which tool they use to do it. When projects grow to the point where larger issues, such as scalability and security, become factors, the tools required are also going to be re-evaluated. In some cases, an ADP solution going forward makes sense. In some cases, it might not.
George
-
I don't think using DSN is a good idea: It's based on ODBC, and MS has stated in the MDAC SDK that "You are strongly encouraged to use one of the native OLE DB Providers instead of the Microsoft Open Database Connectivity (ODBC) Provider. Native OLE DB Providers provide better application stability and performance. Furthermore, native OLE DB Providers will be supported in the future, whereas MSDASQL will not have any new features added to it, will not be available on 64-bit, and will not be accessible from the OLE DB NET Data Provider.". I'm working on removing the use of ODBC in the company where I'm working, and turning peoples mind from ODBC to OLE DB. I figure it will take a while...
/Niels Grove-Rasmussen
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply