SEO

I am working on two versions of an application, one of which will be a
windows forms application (which will need to be redistributable) and
the other will be a web application.

I have MS Visual Studio 2005 (along with the developer's edition of MS
SQL Server), but not MS Access. I also have MySQL, PostgreSQL, Sun's
application server, Tomcat and Apache web server. I am working on
Windows XP Pro, and have installed the .NET 3 SDK and all relevant
related products I could find (e.g. 2 extensions packages for Visual
Studio).

I have one MS Access database, to which my users should have read only
access. I have, and have used, a tool for importing MS Access
databases into MySQL. I expect that SQL Server has a similar utility
hidden somewhere (where I haven't yet looked, though I HAVE been
looking - obviously in the wrong places). I have located a similar
utility for importing MS Access databases into PostgreSQL. I have not
yet decided which servers to use for the web version, but that is
another story, for which I may raise another thread in due course (but
I welcome suggestions which may reduce the effort required given
required effort for the windows forms app).

My problem is for the windows form aplication (intended for use by a
single family). I expect to use ADO.NET. The question is, should I
import the Access database into MS SQL, and redistribute it, along with
MS SQL Server Express (or is that necessary), or distribute it just as
an Access database and use the jet engine to access it. A related
question is, "Does ADO.NET support creating new databases for a given
engine?" Imagine a recipe database. It is easy enough to create a SQL
script that creates all the required tables, indices, foreign keys,
&c., but can I submit that SQL script to an ADO.NET object, along with
a file name, and have it create, e.g., an Access database with the
supplied name. Or do I have to create a database file with nothing in
it other than the schema?

I have more questions, but they'll have to wait.

Thanks

Ted

On 13 Nov, 20:40, "Ted" <r.ted.by...@rogers.com> wrote:
> I am working on two versions of an application, one of which will be a
> windows forms application (which will need to be redistributable) and
> the other will be a web application.

Hi there

I've come across something similar before in the days of VB6 but not
sure how well ADO.NET can deal with it, might require alot of coding.

My recommendation would be to have a common business layer which is
shared by the winforms and web applications. You can slap the two
different UIs on top which should be fairly simple. On the data side,
you can create some interfaces which will allow you to communicate with
your data using the same methods and create data access code for Access
and your other choice of server

Shout if you have any questions

Sam

Hi Sam,

My first question is this: "If you were in my place, and wanted to
simplify deployment, would you use some of the ADO.NET classes to copy
the MS Access database to SQL Server, and then deploy SQL Server 2005
Express with your application, or use the Jet engine for both the
existing Access database and the new recipe database, or leave the
Access database as it is and create a SQL Server Express database for
the new database?"

I ask this first because a) I don't have Access so working with an
Access database is a PITA except within my Visual Studio 2005
application projects, b) IIRC the Jet engine is included with all
recent versions of Windows (at least the ones I'll support), and c)
based on my reading, I can deploy the SQL Server 2005 Express with my
application (or is this necessary - am I mistaken in assuming the SQL
Server 2005 Express is not included in the latest versions of Windows)

A second question is this: "Is the dialect of SQL used by Access the
same as that used by SQL Server 2005?" In other words, can I create a
DDL SQL script in SQL Server 2005's Management Studio that will create
my recipe database and then use ADO.NET 2 or ADO.NET 3 to submit it to
the Jet engine, along with a file name ending in mdb and have the
application properly create a NEW Access database? I ask because, with
my current suite of tools, it is trivially easy to create my new
database in SQL Server (and in a form entirely supported by the
capabilities of SQL Server Express - this db doesn't need the
capabilities in the other editions of SQL Server), but I am concerned
about how to deploy it or to make a distribution that will install
everything my application requires on a new machine (or a client's
machine). When I bought Visual Studio v6 oh so many eons ago, it came
with a utility for building distribution images that could be placed on
floppies of CDs, but I can't find the counterpart for Visual Studio
2005.

Thanks

Ted.

Hi Ted

I dont know enough about your situation to make a suggestion yet. A
couple of questions from me:
Why do you need to use Access if you're going to install SQL Server
Express?
Is this a client requirement?
Is there alot of information? Could you use XML?

Not sure Jet is actually included with XP, think you have to install
it.

With regards to your second question, I'm afraid I dont know. I do
remember Access having a subset of commands, not sure about the latest
versions

Sam

Ted wrote:
> Hi Sam,
>
> My first question is this: "If you were in my place, and wanted to
> simplify deployment, would you use some of the ADO.NET classes to copy
> the MS Access database to SQL Server, and then deploy SQL Server 2005
> Express with your application, or use the Jet engine for both the
> existing Access database and the new recipe database, or leave the
> Access database as it is and create a SQL Server Express database for
> the new database?"
>
> I ask this first because a) I don't have Access so working with an
> Access database is a PITA except within my Visual Studio 2005
> application projects, b) IIRC the Jet engine is included with all
> recent versions of Windows (at least the ones I'll support), and c)
> based on my reading, I can deploy the SQL Server 2005 Express with my
> application (or is this necessary - am I mistaken in assuming the SQL
> Server 2005 Express is not included in the latest versions of Windows)
>
> A second question is this: "Is the dialect of SQL used by Access the
> same as that used by SQL Server 2005?" In other words, can I create a
> DDL SQL script in SQL Server 2005's Management Studio that will create
> my recipe database and then use ADO.NET 2 or ADO.NET 3 to submit it to
> the Jet engine, along with a file name ending in mdb and have the
> application properly create a NEW Access database? I ask because, with
> my current suite of tools, it is trivially easy to create my new
> database in SQL Server (and in a form entirely supported by the
> capabilities of SQL Server Express - this db doesn't need the
> capabilities in the other editions of SQL Server), but I am concerned
> about how to deploy it or to make a distribution that will install
> everything my application requires on a new machine (or a client's
> machine). When I bought Visual Studio v6 oh so many eons ago, it came
> with a utility for building distribution images that could be placed on
> floppies of CDs, but I can't find the counterpart for Visual Studio
> 2005.
>
> Thanks
>
> Ted.

samuelhon wrote:
> Hi Ted
>
Hi Sam,

> I dont know enough about your situation to make a suggestion yet. A
> couple of questions from me:
> Why do you need to use Access if you're going to install SQL Server
> Express?
> Is this a client requirement?
> Is there alot of information? Could you use XML?
>
The one database I am using is an MS Access database that has been
placed in the public domain by the USDA. It has about 80 MB of
nutrition data. I use it to allow a user to enter a recipe and obtain
an analysis of the nutrition in the prodct of the recipe, either per
serving or per 100 grams, and I support storing the recipes entered by
the user. The schema for both the USDA's nutrition database and my
recipe database is very simple. I suppose I could use XML, but I am
not sure what that buys me. The recipe database will initially be
small (actually it will be empty unless I create a few recipes and
store them as samples of what can be done). The remainder of the
application is smple. It supports creating a weeklong meal plan,
assessing the meal plans entered for how well it meets the nutritional
requirements for each member of the family (there is a window that
allows the user to enter these requirements for each member of the
family), and maintain a health diary, including what has actually been
eaten, any of the user's family's ailments and medications/remedies
used to deal with them. So, if Dad has a heart condition, Mom has
diabetes, and junior has colitis, each of their special nutritional
needs can be satisfied without Dad ;-) having to prepare three
different meals. Additionally, they can assess how well their diet and
medications or remedies serve their respective needs.

The idea of the web application is to extend this to create a global
recipe database, and opportunities for anyone who knows how to cook
earn a little money by contributing their favourite recipes to the
database and supporting people paying a pittance each time they wish to
use someone else's recipe. Of course, the option will be available for
a recipe's author to place his recipes in the public domain. This
would empower all users to try foods they may never have seen before.
I could, for example, try a desert made from lychees and longans (I'm
not sure I have the right spelling for these asian fruits) and 1) know
how to prepare it and 2) know what impact it will have on the
nutritional aspects of that week's meal plan. I don't know about you,
but I see a lot of fresh produce in the supermarkets these days that I
don't know anything about, so I don't buy them. If I had a resource of
the sort I'm trying to create, I could try them in safety.

Here you have the rationale for two versions, one accessable on the web
and the other distributable on CD and usable without access to the web.

As I see it, I either use Access databases for both the USDA data and
mine, or I use Jet to use the USDA data and SQL Server Express for my
recipe database, or I find a way to import the USDA data into SQL
Server Express and use SQL Server Express to access both the USDA data
and mine. Dealing with the web application is fairly straight forward
since I'd be running any server I'd need. But I want to make creation
of the distribution on CDs, or an image that can be downloaded from a
website, as simple as possible.

> Not sure Jet is actually included with XP, think you have to install
> it.
>
I am running the 64 bit version of Windows XP Pro, and it has Jet;
either that or the professional edition of MS Visual Studio installed
it. This I know because I have already used it within a test program
that looks at the USDA data. While my application uses this to analyse
foods and recipes, my end user will never need to look at the raw
nutritional data.

> With regards to your second question, I'm afraid I dont know. I do
> remember Access having a subset of commands, not sure about the latest
> versions
>
Thanks

Ted

Ted wrote:
> Hi Sam,
>
> My first question is this: "If you were in my place, and wanted to
> simplify deployment, would you use some of the ADO.NET classes to copy
> the MS Access database to SQL Server, and then deploy SQL Server 2005
> Express with your application, or use the Jet engine for both the
> existing Access database and the new recipe database, or leave the
> Access database as it is and create a SQL Server Express database for
> the new database?"

Yes, I would have utilities or procedures that could read data in
various formats, but would store and deploy all my data in one standard
database format(such as SQL Server 2005 Express), if possible, to
simplify application development and maintenance.

> A second question is this: "Is the dialect of SQL used by Access the
> same as that used by SQL Server 2005?"

No, not by a long shot. Different built-in functions (such as IIF() and
CStr() in Access vs CASE statement and CAST() in SQL Server), different
wild cards (* in Access vs % in SQL Server - and completely different
regular expressions for the LIKE statement), different data types, etc.

Some overviews of the differences:
http://sqlserver2000.databases.aspfa...ql-server.html
http://www.mssqlcity.com/Articles/Co..._vs_access.htm