Replace Access With A .NET Front End And SQL Backend Solution.
Jul 21, 2006
My company currently has about 20-25 Ms Access Database that they want to
replace the FE with .net and the BE on SQL.
This will be done using Visual Studio 2005. Once the FE is converted to .net
and the BE is SQL they all will be accessed through our intranet (sharepoint).
I work in Ms Access and intermediate at VBA and just learing SQL through the
ENTERPRISE MANAGER SCREEN.
I am just now looking at what Visual Studio 2005 is, but can some one tell me
how this will all connect?
What is the typical route for this process?
Ms access to SQL - upsizing wizard or SQL importing?????
Ms Access FE to .net - summarize how this is done in visual studio (user face
rebuilt) then placed on sharepoint?
If I'm using an Access front-end, and the data is on SQL Server being accesses via a linked table, and I create a query in Access, Where is all the work done?
I know access has the option of using a pass-through method, but if I do not use it, is Access processing the query locally? I plan on migrating several tables to SQL because the sizes are getting to large for Access and want to know if their will be a performance increase with out re-writing the queries in Access.
I have an application that uses Acces as a backend and VBA as front end. Application is secured and is supplied on a CD with setup.exe.
Can I use VB 2005 and MS SQL to achieve the same?
Would I be able to package my application with all the neccessery files (assuming that client does not have any e.g. SQL server) so that multiple front ends can access data from common source?
Would I be able to secure such an application using only VS 2005?
What would I need to quickly learn how to achieve the above ( any books you can suggest maybe)?
Hi there,I sincerely hope that someone out there can help. I have twoinstances of the SQL 2000 Desktop Engine running. One is on my localmachine for development and the other is on another machine on ournetwork which is the production environment. I have built an Access2003 front end application which connects to this database. Thisworks fine locally, as you would expect. I successfully installed thedatabase on the production machine and am able to connect to it viaAccess 2003 (using the Data Link Properties window) and from thirdparty database manager software (similar to Enterprise Manager). I amnot able to to connect to the database via my application.I am using the "sa" account with a strong password. This is myconnection string:strConnection = "Provider=sqloledb;DataSource=server02;UserId=sa;Password=strong;Initial Catalog=Test"The error I'm getting is:"Connection cannot be used to perform this operation. It is eitherclosed or invalid in this context."The connection string is the only thing that changes in my code when Iswitch from my local to my production database. Is there some reasonthat I can't use the "sa" account in this fashion that I'm not awareof? I'd rather not use integrated security for simplicity's sake asthis is a small, internal application. Also, I would have thoughtthat if that was the issue, I couldn't use "sa" at all, even locally.I'm going to post to the Access group as well but thought someone heremight have some advice to offer as well.Thanks,Barb
This maybe belongs in the Data-Access Forum, but I'm not sure.
Is it generally a better idea to enforce things like unique constraints in the Database or the Webform? Say for example I want to make sure no duplicate Social Security Numbers are entered. Is it better to have an "If Exists" clause in my query, with a function to deal with it in the application or is it better to just fire the data to SQL Server and let the unique constraint on the dbase column deal with it? I then still have to have some code in my application to deal with the potential exisatance of that number, so is it a case of tomatoe, tomahtoe? If I understand things correctly, SQL server will return an error code if the piece of data does exist, and I will be able to parse the error code and display a message to the user.
Are there performance/coding issues involved? Best practices?
Hi,I'm new to SQL and databases in gnereal but am trying to source anapplication for windows that will sit on top of a small SQL database Ihave (created for local scout troup), I want an easy way to retrieve/add/view data from backend in nice menu-driven GUI format (no cmd-line).Can anyone recommend a useful app I could source to do thistask....thanks in advanceterry
I created a SQl Server 7 database and used an Access 97 frontend on the same computer. The Access frontend uses linked tables to reach the SQl server. The security setup I have is based on the NT Domain Accounts and whatever group I have them in (example: Mr. D is in Marketing therefore he is in a NT Domain Group called "SQL_MARKETING"...the SQL Server recognizes that everyone in this group has access to whatever role I have designated for it in the database"). I can access the linked tables fine on my computer at my desk or at the server. Another person with the exact same administration rights as me can get to the server through the network neigborhood or by mapping..and they can access the Access frontend with no problem from their computer. But when they try to open a linked table they recieve an error message that the ODBC connection has failed. We are all running Access 97 service pack 2 and another person and me have the exact same administration..but only I am able to access the Access linked SQL tables but not him.I tried deleting the ODBC system dsn on the server and the linked tables and then re-adding them and the ODBC link but to no avail. Does anyone know why this would be occuring? Thank You in advance. and the
We are entertaining the idea of moving our data to a cloud and I need to know how to convert my Access backend DB to SQL. which version of SQL will I need to use to do the conversion and will also work in a cloud environment.
I have a database with a sql backend and an access frontend. I put it in a folder on the network for users to access. First, they were getting errors like not being able to access the database if someone else had it open and not being able to perform certain functionality. It was suggested that i put a mde on each of the users pc. i gave them all access to their own front ends on the folder because they use different pcs. this solved the problem. however, i need a way to perform updates without manually creating mdes for every user. i used to work for a co that there was one mde was put into a folder on the network with a config file. The users access the database through CITRIX. Whenever they accessed the shortcut on the server, it created the users their own front end. Whenever we need to provide updates, we simply made a new mde from the mdb and whenever the user accessed the database again, they would have the latest mde and it would created an update frontend for the user. Does anyone know of such an utility or know what I am referring too? I encounted several suggestions like a batch file; but they refer to putting the file and the mde on each users pc. This is not how i'm set up. Thanks
Hello. I have an Access 97 frontend and a SQL 7 backend. When I try to open up the linked SQL tables in the frontend I recieve the error " This recordset is not updatable" from Access. I have the permissions in SQL set at this point to public having all permissions but it still does not change my problem. Does anyone know the answer?
Ok, little frustrated hence the excessive exclamation points.
I'm designing a database in access to use a sql backend. Table structures are setup and am creating a view to join multiple tables together for data entry purposes. Tables are joined properly but when I add more than one join, ie adding more than two tables, I can't add records!?!
I checked the joins, it's not the table, since if I add one table its ok, remove table, add another table and that works, but when I add both tables together, it prevents me from adding records!
And another thing, when I have just two tables joined together and add a record, it comes up with this save changes, drop changes or cancel thingie!!! WTF!!!?
Is there something simple i'm missing or is it my table structure????
Hi, this is my first post (hopefully of many) on this board. Just wanted to say a quick hello before I dive into my question. :)
As the title suggested, I have to develop a MS Access form app (yes, it has to be Access - I know it sucks) that will post and query data to and from a remote SQL Server db. While I have no problem linking the two through the default ODBC drivers, my question is security. Some (actually most all) of the data being passed back and forth is sensative information, and I would like to know the best way to keep it safe.
If anybody has any suggestions, instructions, or can redirect me to a good FAQ site on the subject, I would be most appreciative. I have already done a search on these forums for an answer, but have sadly come up short.
We are new Access users and trying to take our database live...here are some of the problems we're facing.
When we split our Access database into a Backend and Frontends, a couple of strange things happened in the Frontend: 1.) When we run our queries, multiple copies of the same record are displayed (this is wrong). 2.) When we look at those queries in design view, the field names are preceded by Expr1, Expr2, Expr3, etc.
We have a large number of queries. How can we fix this problem?
Also, if anyone knows any websites or resources that can help us understand what things like Expr1 mean (a reference) that would be helpful too!
We are not a very advanced group of access users and are trying to split our database for a research study...here is our question:
When we split our Access database into a Backend and Frontends, a couple of strange things happened in the Frontend: 1.) When we run our queries, multiple copies of the same record are displayed (this is wrong). 2.) When we look at those queries in design view, the field names are preceded by Expr1, Expr2, Expr3, etc.
We have a large number of queries. How can we fix this problem?
Also if anyone has a good source for trouble shooting this or simmilar difficulties we'd appriciate any spots or advice.
i am almost finished building the front end for an application to manage restaurants. i am developing the front end with access vba, and intend to use sql server as a back end for the service. i just came across a group of people bashing access developers on a different site. i have several restaurants interested in using the service, and believe that the service should be very successfull (i have a very specific customer base that is currently not being targeted). the access component would not be a multiuser application. before i distribute the app i would appreciate any input. am i getting myself into trouble using access vba? should i run scared and switch to a vb.net web service? thanks in advance for any thoughts.
I am using Access 97 as a front end to access SQL 7 server on NT 4.0 server. I've set up security model based on NT authentication only. Users have login right to login to SQL and they have public & dataread & denydatawrite access. They also have SELECT permission on a table object and have no permission to INSERT, DELETE and UPDATE. When I use Access 97 to access a database, users are still capable of inserting and deleteing records in tables.
Maybe because I have worked mainly with VB as a front end to SQL Server so I am biased, but I now need definite reasons (I am on a committee for potential future directions) for using VB as opposed to Access for front ending SQL/Oracle, etc. I would also like to use ADO as oppose to DAO. Right now we are using Access with DAO.
We have just moved a largish Access database (180mb, 78 tables, largest tables have about 250k records) to SQL 2k. The original app had an Access back end (now loaded to SQL), and an Access front end (on each client) which is using some local temporary tables, about 600 queries, and several thousand lines of code using ADO and DAO). The Front end was relinked to SQL back end. When testing everything seemed to run OK, but under load (15-20 users) the new app just crawls. Routines that used to take seconds now take 10s of minutes. ODBC timeouts or blocks are common.
Any idea why should the SQL back end be so much slower than Access. both the Access back end and SQL2k are on the same server (Win 2k Adv. with RAID 5, dual 600mhz Pent III, 512Mb RAM). I realize that Access is not the best front end but that is what we have to work with.
Any help would be appreciated, as I am ready to swith back to Access.
My company uses MS SQL Server for the back end and a Retail specific CRM as the front end. I wish to develop some internal peices of software for our use. I was planning on doing this with access.
my options are:
*Use access as front end and backend *Use access as front end and SQL server as backend (create new DB) *Use other front end and SQL Server as backend.
My question is, what are some good front ends that are availble for reletively small demands? How does Visual Studio come into play?
*Also, I would prefer to be able to create a .exe. I dont think access alows that. I would not want users to be able to go in (or even see) the tables and queries. They should only be able to see the one main menu form at the very least.
When I build an MS Access front-end for an SQL Server backend, how does it take care of data integrity and concurrency , if it is only a front-end ? Is Access smart enough to do the job ?
Hello,I have an sql server 2000 on the network and one installed locally inmy computer. I use access as front-end.I go through odbc to connect to the sql server on the network.I was wondering if it is possible to setup the access file to link toeither server.I need to do this because I would like to use the local server as testenvironment. I know I could achieve that by creating 2 users on my XPclient and having odbc liking depending of the users.Is any other way to do that without login and logoff everytime I wantto use a different database.thanks,Giovanni
HI, i am working on ASp.net web applicaton , i am using the following connection string in my web.config file. <add name="X_Conn" connectionString="Data Source=XXX;Initial Catalog=XXX;User ID=XX; Password=XXX;" providerName="System.Data.SqlClient"/> How can i get this connectionString attribute value from the front end.
Does anyone know how to link (not import) an sql server table to an access database using script? I tried a few methods but it doesn't seem to be working. Please help.
I'm using SQL server 7 on Win NT. I have Access 97 as a front end, with linked tables though ODBC to SQL Server. Everytime I open a table in Access, a session appears when I type sp_who2. I close that table in Access, but I when I type sp_who2 the table session is still present. Does anyone know a cause for this?
I am researching why sometimes when we close are queries and tables in Access we have sessions in SQL server that becomes orphans/ghost. I try to kill the session but can't, so therefore I have to recycle the database.
We use SQL Server 2000 on the back-end of our directory web site and ASP on the front end which works fine. However, for my own uses (since I don't create the asp and have to pay a programmer), would it be better to set up an Access 2003 project for my own data entry forms, standard reports and quick searching?
What would be the negatives of this approach. Remember, this is just for me.
Afternoon all,Apologies for cross-posting but as my query covers both Access and SQLServer I thought I'd send it both!I have inherited a project to migrate a fairly complex series ofAccess databases into a single proper SQL database with a web frontend.Its quite a nasty job as people are working on a variety of data setsat several Universities around the world and the data has got verymessy; hence the requirement to put it all on one live web enableddatabase server and provide a web-based front end (particularly assome users insist on using Macs so can't run Access as a front endanyway).If anyone could give me hints on how to perform such a migration or ifanyone knows of any good books or other documents on this I'd begrateful for assistance.Many thanksRich MayMuseum of London
Dear Friends, We have MS Access database with Forms and Reports, which was started 10 years ago by users and now the data is growing very rapidly.
Did anyone tried by having MS Access as front end and SQL Server 2000/2005 as backend with minimum modifications to the forms and reports in MS Access?
Please let me know, your ideas and if there are any links in the web or in Microsoft please provide here.
I have a back-end front-end application in MS Access. Instead of using MS Access queries it gets data via recordsets generated from SQL scripts in VBA routines.
I'm planning to upgrade it to use SQL Server as the back-end. However I need to retain the alternative option of using a file based back-end. (It currently has the capacity to be switched between alternative Access .mdb back-end files.) Is there any information available on how to do this and on how to get Access to synchronize between SQL Server and SQL Server CE back-ends? (Eventually the application will be migrated to VB.NET, but that is a long way down the track.)
I've got an access front end containing various forms and sub forms, and we have just transferred the data into SQL, for storage, we can use the majority of the forms but, I now have a problem with updating the related data.
We have had problems updating the data, we need to close the form down in order to get amended data to register, just moving onto the next record give an ODBC error message.
Even using this method some details refuse to update, although the changes are initially visible on the form you cannot get them to transfer to the datafile.
the error message we get is
[microsoft][odbc sql server driver][sql server] the text, ntext, and image datatypes cannot be used in the where,having, or on clause, except with the like or is null predicates (#306)
we've checked the structures of the tables and the code in the form (it works in the old access back end).
