|
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!
|
|
|
|
|
The executeSQL doesn't return a recordset...
The ExecuteSQL is for make Updates, Inserts or deletes.
For execute that sql sentences you need to use CRecordset::Open
prs.Open(CRecordset::dynaset, _T("SELECT * FROM Leverancier ORDER BY Id"));
Regards...
The programmer's national anthem is 'AAAAAAAARRRRGHHHHH!!'.
Carlos Antollini.
Sonork ID 100.10529 cantollini
|
|
|
|
|
Before you use Open function you can use m_strSort and m_strFilter ,members of CRecordset.For the example you can check them in MSDN
Mazy
Don't Marry a Person You Can Live With...
Marry Someone You Can Not Live Without
|
|
|
|
|
When you execute a CRecordset::Open, the class fill thouse variables...
Regards...
A printer consists of three main parts: the case, the jammed paper tray and the blinking red light.
Carlos Antollini.
Sonork ID 100.10529 cantollini
|
|
|
|
|
I don´t know if I´ve posted this in the right place but maybe some CPian can help me with this.
One of my customers have a 3-tier app that uses Oracle (in a LINUX server) and COM+ (in a W2K). The strange thing is that when the app runs, the W2k event log gets filled with this message:
"The XA Transaction Manager attempted to perform recovery with the XA resource manager.
The XA resource manager reported that recovery was unsuccessful. DSN = MTxOCI.Dll."
Any ideas what could be causing this? Or any ideas how to prevent this message ?
Mauricio Ritter - Brazil
Sonorking now: 100.13560 Trank
|
|
|
|
|
I use ADO(in VC6.0,Oracle8.16) connection object to execute SQL command.My Code looks like this:
_ConnectionPtr m_pConnection;
_variant_t v(0L);
m_pConnection->Execute(MyCommandText,&v,adOptionUnspecified);
The MyCommandText is a _bstr_t object.Here,the Problem raise :it seems that the max length of _bstr_t is too short and i can't hold a long SQL command.
What shall i do?Thanks
God blessing me...
|
|
|
|
|
That must be one big SQL statement. The BSTR does have a length limit. The characters in the BSTR are preceded by a 4 byte number indicating the length of the string. Since its a 2 byte / character Unicode string, the limit is the largest number that can be stored in 16 bits. Somewhere on the order of 65000 characters.
Maybe thats not the problem.
If you post some code, someone might be able to help.
Good luck,
Bill
|
|
|
|
|
I have a recodset that has been impoted from a csv text file. This is done via reading each line if the csv/text file into a sting procing the sting into an array and then add the values to the recordset.
So I end up with a nice recordset. Now I seem unable to sort the recordset so that it contins a list of distint names. (EG I want a list of of the printers in the recordset). normanly this can be done via sql with the command "select distinctrow field list from tablename" however as i've populated the recordset from a text file i do not know what the tablename is?
can anyone tell me how this is done or suggest another way? I'm programing this in vb6.0.
thank you
|
|
|
|
|
You didn't mention if you use SQLServer,access or vf so
How did you import them?(programmically)As I remember,when SQLServer import database you can
define table name.
Can't you set name manually if you use Access or SQLServer or..?
Mazy
Don't Marry a Person You Can Live With...
Marry Someone You Can Not Live Without
|
|
|
|
|
Thanks for responding, I am not connecting to any SQL server. I am just opening a text file and populating a recordset My code is below.
After I run this I am having trouble finding a way to just get a list of unique QueueName's from the recordset??
I create a recordset via this code....
Set rs = New Recordset
rs.CursorLocation = adUseClient
rs.Fields.Append "UserName", adBSTR, 128
rs.Fields.Append "DocName", adBSTR, 128
rs.Fields.Append "QueueName", adBSTR, 128
rs.Fields.Append "Date", adDBDate
rs.Fields.Append "Time", adDBTimeStamp
rs.Fields.Append "Workstation", adBSTR, 128
rs.Fields.Append "ClientCode", adBSTR, 128
rs.Fields.Append "Subcode", adBSTR, 128
rs.Fields.Append "PaperSize", adBSTR, 128
rs.Fields.Append "Features", adBSTR, 128
rs.Fields.Append "Sizeinbytes", adBSTR, 128
rs.Fields.Append "Pagecount", adBSTR, 128
rs.Fields.Append "cost", adBSTR, 128
rs.Fields.Append "AccountBalance", adBSTR, 128
rs.Open
Then I import the txt file into the recordset using this code...
Dim F As Long, sLine As String, A(0 To 13) As String
F = FreeFile
Open "C:\temp\text.txt" For Input As F
Do While Not EOF(F)
Line Input #F, sLine
ParseToArray sLine, A()
rs.AddNew
rs.Fields(0) = A(0)
rs.Fields(1) = A(1)
rs.Fields(2) = A(2)
rs.Fields(3) = CDate(A(3))
rs.Fields(4) = CDate(A(4))
rs.Fields(5) = A(5)
rs.Fields(6) = A(6)
rs.Fields(7) = A(7)
rs.Fields(8) = A(8)
rs.Fields(9) = A(9)
rs.Fields(10) = A(10)
rs.Fields(11) = A(11)
rs.Fields(12) = A(12)
rs.Fields(13) = A(13)
rs.Update
DoEvents
Loop
Close #F
End Sub
Sub ParseToArray(sLine As String, A() As String)
Dim P As Long, LastPos As Long, i As Long
P = InStr(sLine, ",")
Do While P
A(i) = Mid$(sLine, LastPos + 1, P - LastPos - 1)
LastPos = P
i = i + 1
P = InStr(LastPos + 1, sLine, ",", vbBinaryCompare)
Loop
A(i) = Mid$(sLine, LastPos + 1)
End Sub
|
|
|
|
|
Sorry to disappointed you,I can't understand these code because I don't know VB
Mazy
Don't Marry a Person You Can Live With...
Marry Someone You Can Not Live Without
|
|
|
|
|
This may be disloyal, but look on the code guru site. It contains several examples of FTP code.
http://codeguru.com/internet/index.shtml
Hope this helps,
Bill
|
|
|
|
|
Please disregard the previous post, I typed it into the wrong window. It has nothing to do with this question.
Thanks for the help,
Bill
|
|
|
|
|
I have a Access database running on Win ME with VC6.0
and I created a table (by hand in Access) then I try and
use my program to create a record in that table.
the code is executed but the record is not created and
no error messages are thrown. If there is a record already
in the table (again put in by hand) then additional records
can be added without any problems.
Any ideas on what may be wrong ?
KaS
|
|
|
|
|
Can you tell me the code that you add recordset with it?
Mazy
Don't Marry a Person You Can Live With...
Marry Someone You Can Not Live Without
|
|
|
|
|
presenting the code:
m_rs is a recordset class
m_pRs is a pointer to the above (type _RecordsetPtr *)
m_piAdoRecordBinding is of type IADORecordBinding *
//... code omited for brevity
strcpy(m_rs.m_szSerialNumber, (LPCTSTR)SerialNumber);
//... code omited for brevity
m_piAdoRecordBinding->AddNew(&m_rs);
m_pRs->MoveLast();
KaS
|
|
|
|
|
I can't understand what is wrong there,but I have two suggestion,first
debug your code to see whats going on there,and second update your database
after you addnew ,also try this,may be this one is ok
hr = pRs->AddNew();
if( FAILED( hr ) ) throw _com_error( hr );--------->you'd better to put it in your code too
pRs->Fields->GetItem( L"fieldname1")->Value = anything1;
pRs->Fields->GetItem( L"fieldname2")->Value = anything2;
pRs->Fields->GetItem( L"fieldname3")->Value = anything3;
hr = pRs->Update();
if( FAILED( hr ) ) throw _com_error( hr );
Hope thats help you
Mazy
Don't Marry a Person You Can Live With...
Marry Someone You Can Not Live Without
|
|
|
|
|
One field of my database is Text(string).
Is there any SQL statement or ODBC function to find a word in text of
each recordsets and return recordsets contain that word?
Mazy
Don't Marry a Person You Can Live With...
Marry Someone You Can Not Live Without
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
