moving away from Access

[phil cunningham]

Hope someone can offer some advice here

I have quite a bit of experience in Access but need to move up to a more
robust system.

At the moment I use Access for in-house data handling (reports, modules and
forms) and have ASP.NET to provide some public web access to the same
databases

After reading quite a bit on this I get the feeling that a better solution
would be

Visual Basic.NET - to handle forms and programming for in-house data
handling
ASP.NET for the public web access
SQL Server or Server Express for the tables and queries (views)
Reporting services for generating the reports - If I want to stick with SQL
Express then maybe Crystal reports would work OK

In the past I've relied on Access for all these functions but need to be
clear which are the best tools for the job



Any information supplied will be very much appreciated
Cheers
Phil

 

[Brendan Reynolds]

ASP.NET for web access, yes, sure. SQL Server instead of JET, probably. But
VB.NET and Reporting Services instead of Access for the in-house solution
why? What is it you hope to gain from the change? In what way is Access not
meeting your in-house requirements? What is your definition of 'better'?

Developing a data-centric desktop app using .NET Windows Forms is going to
take you much, much longer than developing an equivalent app using Access,
so before making that decision you really need to have some clear vision of
what you hope to get from the change that is going to justify the
much-increased development cost.

 

[Arvin Meyer [MVP]]

Developing a data-centric desktop app using .NET Windows Forms is going to
take you much, much longer than developing an equivalent app using Access,
so before making that decision you really need to have some clear vision of
what you hope to get from the change that is going to justify the
much-increased development cost.

And when Brendan says "much-increased development cost" he's not kidding.
Count on at least 3 times the cost, and it could be as much as 10 times the
cost. I am in the process now of replacing an ASP application which cost
slightly over $250K with an Access one which cost slightly under $30K. The
Access solution also includes over $8K for for hardware and licenses. Both
databases use JET, to use SQL-Server would add an equal amount for licenses
and development to either of them.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[phil cunningham]

Funny.

Over on the ASP.NET site they all seem to hate Access and tell me to move up
to SQL Server otherwise all manner of calamity will befall me and my
customers.

I've never yet had a problem with MDB files (as long as I back them up) but
then I've only been writing desktop apps for 1-5 users at a time - I've been
a little anxious about what happens when I expose our system to thousands of
our customers (OK maybe 50)

I cant really see a problem with Access files and ASP.NET as long as I keep
the reading and writing of the MDB files as fast as possible. If I don't
leave any files open for any length of time, the chances of the number of
users exceeding 5 at a time are slim to vanishing.

If I did use SQL Server express am I right in thinking I can still use
Access exactly as I do now but just link to the SQL Server tables rather
than have then in the MDB

Many thanks for the replies - it may have saved me a great deal of
work-money etc.

Thanks
Phil

 

[Arvin Meyer [MVP]]

A company I recently worked, for has a website running on JET which gets
several hundred hits a day. It has been running for 5 years with minimal
problems. I understand that the UtterAccess website
http://www.utteraccess.com gets thousands of hits per day without problems.
I have approximately 50 concurrent users on my current system split like: 35
on workstations, 10 on IIS with ASP access, and 5 on Terminal Server. The
ASP app will soon be converted to Terminal Server. That may be stretching a
bit, but my last corruption was in April of 2003 when a Dell WiFi card kept
dropping connections.

If you feel more comfortable with SQL Server Express, by all means, use it.
It would be a necessity if you needed a server to maintain security from
inside threats. Outside threats are more often managed by your network. It
would also be a requirement to move from JET if you had large numbers of
users on a poorly designed system, or large amounts of data on a poorly
designed system. Server databases are more tolerant of design and network
errors. All the Access objects work well with almost any back-end database
engine. (That's why they call it Access).
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Brendan Reynolds]

As I mentioned during the discussion in the ASP.NET group, we really need to
distinguish between Access and JET here. The advice to move from JET to SQL
Server may be sound (I don't know enough about your situation to be more
definite than that). The problem is that in that discussion people keep
saying 'Access' when they mean 'JET'. The question of whether to move the
database from JET to SQL Server is - or should be - a completely separate
question from whether to move the application from Access to .NET.

 

[Rick Brandt]

Funny.

Over on the ASP.NET site they all seem to hate Access and tell me to move up
to SQL Server otherwise all manner of calamity will befall me and my
customers.

There is some legitimacy to that position when creating a web app and deciding
which *database* to use. That is not what Arvin and Brendon were talking about.
They were discussing building a dot-net windows client for your internal
application versus using Access for that. You could write the application in
Access and still use SQL Server for the database. That is in fact a very common
and good combination.

 

[Mick Ruthven]

Isn't that called an Access Project? I'm developing my first one and it's
Access with some difference-learning involved, but still mostly Access-easy.
You could use the same SQL Server database as a back end for web
applications written in ASP.NET.

 

[phil cunningham]

Many thanks for all the time you've all taken to comment on this. It's been
a big help in clearing away some of the confusion that I've picked up.

Thanks again
Phil

 

[Rick Brandt]

Isn't that called an Access Project? I'm developing my first one and
it's Access with some difference-learning involved, but still mostly
Access-easy. You could use the same SQL Server database as a back end
for web applications written in ASP.NET.

Using an Access Data Project (ADP) is one way to do it, but another (simpler
IMO) way is to use ODBC to link to the SQL Server tables in a regular MDB
file and then develop much as you do when using Access/Jet tables. That is
how nearly all of my applications are set up.

 

[Mick Ruthven]

That certainly is another way. Are there benefits/tradeoffs for either way?

 

[Larry Linson]

That certainly is another way. Are there benefits /
tradeoffs for either way?

In my limited work with ADPs, I could see no advantage. When I couple that
with knowledgeable Microsoft insiders (on the Access development team) now
recommending MDB and ODBC as a general rule over ADP and ADODB, I continue
to use the MDB & ODBC approach that I have successfully used since Access
2.0.

One poster claimed that Microsoft support told him that ADPs were to be
"deprecated" in the next release of Office. I have not heard nor seen any
official Microsoft announcement or published statement to that effect.

Larry Linson
Microsoft Access MVP

 

[Arvin Meyer [MVP]]

Using ADO, in your code, you can use OLEDB and that's slightly faster than
DAO and ODBC. An Access data project can use either or both, the same as an
MDB. An MDB does use JET as a translator to get to the server. The real
advantage to using the MDB (and the JET expression service) is that you can
use VBA functions in your queries. ADPs do not allow that. For me, that
controls 90% of my decision. Even when I do write an ADP front-end, I have
always found that I prefer an MDB to do the reporting because of the ability
to use VBA in the underlying queries. All of my ADPs have had an MDB
reporting front-end.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Mick Ruthven]

Thanks, Arvin. I've been bemoaning the loss of VBA functions in queries when
using Access with SQL Server. So now I'd like to convert my Access Project
..adp to an Access .mdb conntectd to SQL Server, hopefully being able to use
the SQL Server data already present for the app.

As a start I created a new mdb, then imported the tables from the adp. But I
guess that makes a stand-alone mdb not connected to the data contained in
SQL Server. I'm in pretty new-to-me territory as you can see. What would be
next?

Thanks,

Mick Ruthven

 

[Arvin Meyer [MVP]]

Create a DSN and use it to make the connection and link the tables. To
create a DSN, go to the Control Panel, find Administrative Tools, then click
on Data Sources (ODBC). Follow the wizard and you should get there just
fine.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Mick Ruthven]

Thanks, Arvin. Do I need any tables in the Access mdb when I make that
connection, or does that connection bring in the tables from the SQL Server
database? Or...?

 

[Mick Ruthven]

Ignore prior message; I did what you said and it worked just fine! Thanks so
much.

Except... Two of the five queries I had created in the Access Project (using
the same SQL Server instance) don't show up in the link-table list. I can
easilty recreate them, but can you think of a reason they wouldn't show up?

Thanks again,

Mick Ruthven

 

[Mick Ruthven]

I have a few basic questions...

1. The tables linked from the SQL Server database show up in design mode
with Access-type fields, not SQL Server-type fields. Is that correct?

2.. If I want to change the design of a table, I need to go back to the
Access project to do that. Correct?

3. The two queries I have in the Access project that didn't appear in the
link list were the ones with more than one table in them. I was about to ask
how to get them to show up so I can link them. But... one purpose of using
the Access mdb instead of the project was to allow Access queries instead of
SQL Server queries. So I guess I should just recreate all the queries in the
Access mdb. Correct?

 

[Arvin Meyer [MVP]]

I have a few basic questions...

1. The tables linked from the SQL Server database show up in design mode
with Access-type fields, not SQL Server-type fields. Is that correct?

That's correct. JET is overlaying the design of the SQL tables so that you
see an Autonumber instead of an Identity column, etc.

2.. If I want to change the design of a table, I need to go back to the
Access project to do that. Correct?

You can do it with a DDL (Data Definition Language) query, with an Access
Project, or as I prefer, use Enterprise Manager directly on the SQL tables.

3. The two queries I have in the Access project that didn't appear in the
link list were the ones with more than one table in them. I was about to ask
how to get them to show up so I can link them. But... one purpose of using
the Access mdb instead of the project was to allow Access queries instead of
SQL Server queries. So I guess I should just recreate all the queries in the
Access mdb. Correct?

Correct. I simple create the queries in Access unless I'm using a
Pass-Through query, which is executed in the back-end.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Mick Ruthven]

Thanks again, Arvin!

That's correct. JET is overlaying the design of the SQL tables so that you
see an Autonumber instead of an Identity column, etc.


You can do it with a DDL (Data Definition Language) query, with an Access
Project, or as I prefer, use Enterprise Manager directly on the SQL tables. instead

Correct. I simple create the queries in Access unless I'm using a
Pass-Through query, which is executed in the back-end.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
front-end,