Solved

access 2007 vs Sql for local window application

Posted on 2011-02-22
10
230 Views
Last Modified: 2012-05-11
Hi Experts,

This time I need an advise
I have an Access 2007 local application that works good but I'd like to renew it (as It works already for many years)

Is it better to rewrite it using another interface?
can I use Sql as Database and write the interface in Access?
my data access database contains about 20,000 recorde in all.

what will be easer for me to develop and more efficient for my client?

Thanks

 

0
Comment
Question by:nuritat
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 19

Accepted Solution

by:
MINDSUPERB earned 94 total points
ID: 34950967
With about 20,000 records, I may suggest to use Access. You may do redesigning the appearance of your form to look at it as new.

So far, Access still excels as a user friendly and easy to create windows application.

Sincerely,
Ed
0
 
LVL 19

Assisted Solution

by:MINDSUPERB
MINDSUPERB earned 94 total points
ID: 34950979
The below link will help you decide on either use SQL or Access in your application.

http://www.quackit.com/microsoft_access/tutorial/microsoft_access_versus_sql_server.cfm

Ed
0
 
LVL 19

Assisted Solution

by:Bardobrave
Bardobrave earned 31 total points
ID: 34950980
I don't see any point in using Access for interface and SQL Server for data.

Access is not the best DBMS of the world, although it can work fine for small business applications (and this in combination with the easy of use and interface creation is the key of it's success) it lacks greatly when scalating to big projects.

If you app work ok on Access and you plan to create the interface on Access, stick to Access to data management as well. Using SQL Server you'll charge your client with the purchase of a server license and the connection between interface and data will be slower than if you use Access for both.

If your app is somewhat "laggy" and you expect it to grow, then you should think in jump to SQL Server and use any alternative for interface.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 94 total points
ID: 34950994
To be clear, a database 20k records is quite small, and any database platform should have no trouble with it.

Access is a great tool for small workgroups (<25 users, IMO). After that, it's time to move to a new database engine, but even with hundreds of users you can keep the User Interface in Access and move the data to a more robust platform. If you having no troubles with the database, and you don't forsee major changes coming for the business or userbase, then there's really no need to consider moving.

However:

If you have specific needs, like working with Grids or other external controls, then it's probably best to move to one of the .NET langauges.

If you need to support remote users (like OfficeA in New York and OfficeB in California), then often you should consider moving to a web-based language, like ASP.NET or PHP. You could also use Terminal SErvices or some other remoting process and keep the application entirely in Access.

If you think the userbase will increase significantly, you can move the data to a server platform, like SQL Server, Oracle, etc. SQL Server seems to be the easiest to work with in regard to the Access interface, but Access can connect to any ODBC-compliant platform.

As to "easier for you", that would depend on your skill set.

As to "more efficient for your client", that would depend on their specific set of needs. If you can gather those requirements (and whatever is being planned for the forseeable future), we might be able to provide more focused insight.

0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 94 total points
ID: 34951003
< Using SQL Server you'll charge your client with the purchase of a server license >

SQL Server Express is free and works well with a smaller number of users.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:nuritat
ID: 34951232
thank you all for most helpfull answers

I had a problem once exporting a queery that was rather complex with about 2,000 records from the access application
does this mean something I should consider in my decision?
0
 
LVL 19

Assisted Solution

by:MINDSUPERB
MINDSUPERB earned 94 total points
ID: 34951266
If your query is properly designed and structured, I did not see any problem with Access application.

LSM has presented the information well at ID: 34950994.

Ed
0
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 31 total points
ID: 34952775
nuritat,

Adding to the already excellent advice you have received:

<<what will be easer for me to develop and more efficient for my client?>>

What will be the easier for you to develop with will be what you know best. If you know Access better than any other front end development platform then Access is your best tool.

What I find to be is most efficient for my clients is a well designed front end matched to a back end that can support the front end /application's  needs.

<<I had a problem once exporting a queery that was rather complex with about 2,000 records from the access application does this mean something I should consider in my decision?>>

Exporting around 2000 records to excel should work fine from Access. Excel can easily handle that many records.

I have client's that   export 10,0000+ records form some of my  Access Applications without any issues. I have even exported close to 60,000+ records to Excel without issue.   I have one client who

I do lots of Excel Automation with Access to take take an Access report  and recreate it as a  Drill down report in  Excel. The largest report into Excel I have tested so far has had over 25,000+ detail lines.

<<access 2007 vs Sql for local window application>>

I will assume that you application is already split.

I look at how fast the back end is growing. In a year how much bigger does the back end grow?

For example say you have a back end that is growing at 100 meg a year. It will a bout 10 years to reach 1 gig.  It will take almost 20 years to reach the maximum  2 GIG file size limit.
 
If a back end is growing at a rate larger than 20-25% of the back end's capacity per year then you probably should consider a different back end.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 94 total points
ID: 34957447
<If your query is properly designed and structured, I did not see any problem with Access application. >

Agreed. In most cases, poorly performing queries on Access will be poorly performing queries on other platforms. Granted a true Server type database may have a bit more power in terms of query processing, but if the database is not properly designed, or the tables are poorly indexed, or the query is poorly formed, then your performance degradation is probably due more to that than your platform.

That said, some queries are simply complex, and take some time. If your query has a lot of IIF structure, Domain functions (DSum, DCount, etc), Grouping, subqueries, etc etc then you're not going to have a highly performant query.
0
 

Author Closing Comment

by:nuritat
ID: 34990580
thank you!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now