473,549 Members | 2,626 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

General advice needed regarding MS Access, MS SQL Server, MySQL/PostgreSQL

Ted
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

Nov 13 '06 #1
5 2929


On 13 Nov, 20:40, "Ted" <r.ted.by...@ro gers.comwrote:
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

Nov 15 '06 #2
Ted
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.

Nov 15 '06 #3
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.
Nov 16 '06 #4
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

Nov 16 '06 #5
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
Nov 28 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
4019
by: nmac | last post by:
Hi all, hopefully someone can offer some sagely advice regarding Production use of Jakarta's Tomcat. First, some brief background. My company have a servlet application that connects to a MySQL database. The servlet is deployed on two seperate win2k servers (Access to the tomcat servers is via DNS round robin load balancing). The database...
4
2202
by: Socheat Sou | last post by:
After a brief, but informative, discussion on Freenode's #zope chatroom, I was advised to consult the gurus on c.l.p. I'm working for a small company who is in desperate need to rewrite it's 15+ year old, flat-file, client-tracking database. The staff uses OSX, while I administer the couple linux servers we have to run our website and...
6
1566
by: Örjan Skoglösa | last post by:
Hi all, My first posting here. I would be very thankful for a general advice about which "way to go" to accheive a certain arrangement on a web page. The page should show a list of items (a wishlist) with about 50 - 150 items, presented each one in a row in a table with e.g. 5 columns.
11
5842
by: CSN | last post by:
Is it possible to iterate over an array in plpgsql? Something like: function insert_stuff (rel_ids int) .... foreach rel_ids as id insert into table (rel_id, val) values (id, 5);
2
2241
by: Ian Baker | last post by:
We have developed an Access/Jet database (2000, XP & 2003 versions) that has been used by clients all around the world for several years and is extremely robust with 55 tables, 172 hard stored queries (plus about the same in VBA SQL code), 60 forms and about 18,000 lines of code. The biggest advantage with Access is that it doesn't cost the...
49
3186
by: Mell via AccessMonster.com | last post by:
I created databases on Access 2003 and I want to deploy them to users. My code was also done using 2003. If they have Ms Access 2000 or higher, will they be able to use these dbs with all code, etc? Please explain -- Message posted via http://www.accessmonster.com
1
2815
by: Ozz | last post by:
Hello, I am trying to turn on General logging on mysql server . If I go >mysqld -l filename on shell, it says "command not found". What is the best way to create General log on a live server. Somehow MYSQL server doesn't have a my.cnf file. I have searched all over but there are no signs. Whenever I have to start/stop/restart the...
1
1895
by: Ted | last post by:
I managed to get it installed OK, along side MS Visual Studio 2005 (with which I received it). During the install, I made sure I installed everything. I have developed a number of applications using MySQL v 5 and PostgreSQL, but I have not worked with MS SQL. Playing with it after installing it, and running through several tutorials, I...
0
1762
by: chitranjan | last post by:
Hi all I am working on PostgreSQL-8.2.4 ,and OS of my system is fedora-6. There are other 3 systems ,having same OS(fedora-6) and we can connect them through ssh.Actually, i am new in postgresql .I have already installed postgreSQL-8.2.4 on my system successfully and working fine. But I do'nt have any idea that ,how can i make server-...
0
7541
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7979
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7497
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6065
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5385
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5107
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3493
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1960
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
781
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.