|
Check this
Mazy
Don't Marry a Person You Can Live With...
Marry Someone You Can Not Live Without
|
|
|
|
|
I have that book already actually...
"An expert is someone who has made all the mistakes in his or her field" - Niels Bohr
|
|
|
|
|
If you read first day of that book you'll notice that there is good comparsion between SQLServer and Access.SQLSever is faster than Access and better when want to write database in large scale for server and clients.
Let me give you more details:
You want to use this command:"SELECT * FROM anything WHERE something"
your database has 500,000 records,when you execute this command,Access first first return all 500,000 records to the client and then client filter
it,but SQLServer send command to server,server filter records and only return
proper records,you this is really affect on speed.(your process happend on server.)
Also Access databese size has more limitation than SQLServer .
HockeyDude wrote:
Where is the SQL engine...? Is it part of the Access program...?
I'm not sure but I don't think that is correct.
Hope that helps you
Mazy
Don't Marry a Person You Can Live With...
Marry Someone You Can Not Live Without
|
|
|
|
|
Mazdak wrote:
You want to use this command:"SELECT * FROM anything WHERE something"
your database has 500,000 records,when you execute this command,Access first first return all 500,000 records to the client and then client filter
it,but SQLServer send command to server,server filter records and only return
proper records,you this is really affect on speed.(your process happend on server.)
I remember that much, but do Access and SQL Server has the ability to use the same DB...?
Is the Access file format the same as SQL Server...? probably not right..? So you would need a go between driver wouldn't you...?
ODBC, this was developed to solve this very problem correct..? However it seems ADO is the way to do thing now, so what I really wanted to do was.
Create a DB in Access, cuz it's all I have on my client computer. upload it to a website...but use SQLServer to access and return recordsets...? The Server would require the special SQLServer software (MySql) whatever right..?
But can these (Say i use Perl's DBI) which I think uses ODBC and MySql...??? Can scripts I write in Perl/ASP access Access DB...???
I hope I made myself clear...it's really early morning here and I'm quite tired..
Cheers!
"An expert is someone who has made all the mistakes in his or her field" - Niels Bohr
|
|
|
|
|
HockeyDude wrote:
The Server would require the special SQLServer software (MySql) whatever right..?
MySQL has nothing to do with Microsoft SQL Server, I thought you were asking about MS SQL Server?
You can use DBI to use any database with an ODBC driver, including MS Access. You can't use MySQL to handle Access database files.
|
|
|
|
|
Isn't MySql like SQLServer, but freeware...?
"An expert is someone who has made all the mistakes in his or her field" - Niels Bohr
|
|
|
|
|
MySQL is like MS SQL Server in the same way as Linux is like Windows XP.
|
|
|
|
|
So one is super charged the other is freeeware...i know this...but unless your developing enterprise solutions, couldn't you get away with MySQL...?
SQL Server i've heard is like typical MS product $$$$
For web development...do some web hosting services already supply SqlServer on there Server...? And charge you monthly fees...? Even with this solution i'd need cheaper than that...so i'm thinkiing MySql would be best
"An expert is someone who has made all the mistakes in his or her field" - Niels Bohr
|
|
|
|
|
Good morning(or afternoon)!
HockeyDude wrote:
but do Access and SQL Server has the ability to use the same DB...?
no,but SQLServer(MS SQLServer) can import database from Access or any other type,
to SQLServer DB or any other kind like visual foxpro ,even Excel or text file.But it can not use .mdb(Access) file directly,just import and export.
I don't know if it is possible to do this in program with codes.
HockeyDude wrote:
Create a DB in Access, cuz it's all I have on my client computer. upload it to a website...but use SQLServer to access and return recordsets...? The Server would require the special SQLServer software (MySql) whatever right..?
It's not very clear for me,but I think you can't do all these things in one file.(as I said above)
HockeyDude wrote:
Can scripts I write in Perl/ASP access Access DB...???
I've never test it but I don't see any reason that they can't access Access DB.(.mdb file).
Mazy
Don't Marry a Person You Can Live With...
Marry Someone You Can Not Live Without
|
|
|
|
|
Good enough then.
I'll have to go do some more reading when time permits and maybe come back with more questions.
Til then!!
"An expert is someone who has made all the mistakes in his or her field" - Niels Bohr
|
|
|
|
|
HockeyDude wrote:
Can scripts I write in Perl/ASP access Access DB...???
I found it,you can
Mazy
Don't Marry a Person You Can Live With...
Marry Someone You Can Not Live Without
|
|
|
|
|
Using ODBC...?
"An expert is someone who has made all the mistakes in his or her field" - Niels Bohr
|
|
|
|
|
why not?it's one of basic drivers
Mazy
Don't Marry a Person You Can Live With...
Marry Someone You Can Not Live Without
|
|
|
|
|
What OS your script engine hosted? Windows or Unix?
E.g. you use IIS (Windows) as web server. You can use COM objects and work with ADO.
Regards.
|
|
|
|
|
Some other things that you may not be aware of is the ability for SQL Server to run stored procedures, that is essentially a compiled version of your favorite "Select * from Here" statement stored right on the server. Stored Procedures are really a beautiful thing if you have never used them before. They allow you to create "dynamic" tables and do joins between them. SQL Server also exposes you to the robust environment of T-SQL, it is designed to scale better when needed. Access is a great tool, don't get me wrong, however SQL Server allows so much more to the developer.
Hope some of this helps.
Nick Parker
|
|
|
|
|
Nick Parker wrote:
SQL Server to run stored procedures,
That i was aware of.
Nick Parker wrote:
They allow you to create "dynamic" tables and do joins between them.
That I didn't know..
Nick Parker wrote:
however SQL Server allows so much more to the developer.
You get what you pay for...pay for being the key...i can't afford SQLServer
"An expert is someone who has made all the mistakes in his or her field" - Niels Bohr
|
|
|
|
|
Nick Parker wrote:
They allow you to create "dynamic" tables and do joins between
I think that is possible in Access too.
Mazy
Don't Marry a Person You Can Live With...
Marry Someone You Can Not Live Without
|
|
|
|
|
Most database types will have an 'SQL Engine' some where as this is what takes the SQL statement and sarches through the database for the results you want.
The most important difference between MS Access DB and a SQL Sever DB as far as I am concerended is this.
Assume that you had a 1 million row database, and you want to run a query that returns only 100 of those rows. Also assume the the database and the client app are on different machines
Access is a file based database, so to do the above, the _client_ machine must open the database file and read through all 1 million rows looking for the ones that match. This means that 1 millions rows are returned over the network to the client and 999,900 rows are thrown away not to be used. This wastes a lot of network traffic and is slow!
SQL Server (be it MS-SQL or mySQL) databases have an service running on the machine where the database file resides. The client appliction talks to this service rather than the database file. This allows the client to tell the service what data it wants (via an SQL statement) and that service will sort it locally making full use of the servers fast disks and lots of memory, and will only return the 100 rows of intreast. A lot faster as all the work is done where the data is and only the small results set is returned over the network to the client.
Also because all request to the database come through this single service, it allows much more efficent caching of the database. Because this service knows about every change to the database, it knows that if there have been no changes and some else asks for the same 100 rows, there is no need to re run the query, it can just return the same 100 rows it did to the last client.
stephen.
|
|
|
|
|
Access does make a good database for low volumne web sites. As this post points out, Access is NOT a client/server application. It is executed soley on the client machine, the 1 million row example, is something to be avoided in either database (use indexes to reduce the amount of data to be examined).
MySQL may work for the application in mind, as long as its simple and fairly low volume.
Good luck,
Bill
|
|
|
|
|
Okay don't know if any of the replies mentioned this, or even the book you read, but I had to explain a similar sounding question at work today.
From a muti-user point of view, Access is a shared file, like a shared Excel Workbook. You have a number of users on seperate PC's each running their own PROCESS - not thread, but a number of proccess on different machines under the control of a number of users
SQL Server is that - a single Process on a PC-Server, that takes requests from remote connections. It sits their on the Server doing the thinking and looking after itself. An analogy is that if your database data was in your shoes, you would have another brain down in your shoes processing the requests that would be questions asked by the brain in your head.
Access has your brain in your head grab all the data in your shoes, and process the quesry in your head to get the resulting record set, except that everyone is trying to read from your shoes!!!!!
If that makes sense then....god help you.
Giles
|
|
|
|
|
Giles wrote:
If that makes sense then....god help you.
uh oh, that made sense.... whats going to happen to me?
James
Sonork ID: 100.11138 - Hasaki
"Smile your little smile, take some tea with me awhile.
And every day we'll turn another page.
Behind our glass we'll sit and look at our ever-open book,
One brown mouse sitting in a cage."
"One Brown Mouse" from Heavy Horses, Jethro Tull 1978
|
|
|
|
|
Your going to have to explain it to me!
Giles
|
|
|
|
|
I don't know if I can do that; bad things could happen :P
James
Sonork ID: 100.11138 - Hasaki
"Smile your little smile, take some tea with me awhile.
And every day we'll turn another page.
Behind our glass we'll sit and look at our ever-open book,
One brown mouse sitting in a cage."
"One Brown Mouse" from Heavy Horses, Jethro Tull 1978
|
|
|
|
|
HockeyDude,
From some of what I see posted here I think you are missing an underlying item. Access is really what I would call a front end to a database. MS JET comes as the default database engine with access. So you really need to compare JET to SQL Server to MySQL to Oracle ... Which are the databases.
Good ideas are not adopted automatically.
They must be driven into practice with courageous patients. -Admiral Rickover. ...
|
|
|
|
|
I’m new to Visual C++ programming, now exploring the CDatabase and CRecorset classes.
I made a database with MS Access, created a new SDI project, using ODBC without file support. I created some edit boxes and added variables to them, which are connected to the Database, made by Access. Now my problem: of course it’s easy to use the ExecuteSQL method for everyone, but not me If I add a new menu entry, and add a function for this entry, how can I use the ExecuteSQL method to retrieve data from my database, what are exactly the steps to take? If I type CDatabase db; db.ExecuteSQL(SELECT * FROM Leverancier ORDER BY Id), and run my program, calling the function, the database don’t display the records ordered by Id... help!
|
|
|
|