Access or SQL Which one is better

Access or SQL Which one is better

• khyron

  Yes, you did, and it was implicit that you meant SQL Server Express, but there is just some confusion at times where people refer to SQL and then mean SQL Server, which is a program that uses SQL. It wasn't a problem here, but it has been elsewhere, and so I just thought I'd mention it.

  What's wrong with your SSE database How do you access your data Do you write SQL, or do you let a wizard do it for you Do you use stored procs

  
  
  

• Robert Schneider

  I should also mention that you should make sure you say SQL Server when that's what you mean. SQL is the language used by all databases, Access included.

  
  
  

• Micky D

  OK - I never use the adapters, so I can't really comment on this code, or how fast it may or may not be. I would always write stored procs for all SQL operations, including adding a new entity. Then I would just call a method in my data layer which would pass the values over to the stored proc. This is bound to be faster, I would expect, because you're manipulating tables and presumably the framework is working out which tables have been changed, and then generating SQL to make those changes in the database.

  
  
  

• taucher

  I'm sorry I thought I did mention SQL 2005 Express

  iscbaltazar wrote:

  Hi I'm wondering, which one would be better, i'm creating 2 medical programs, that are going to be running on a local machine, I Use visual Basic 2005 express and don't know if I should develop with access or SQL 2005 express. Which one is better and faster Any suggestions or comments will come in handy. PS. Future versions might have network connections, like for a secretary or interoffice connections. So should i work on access or SQL express

  
  

• Clay E. White

  Like I said, you need to start posting some code if you'd like me to tell you how to make it faster :-)

  
  
  

• Damir Dobric

  SQLE is *better* in that it does more. The real question is, which is the appropriate tool fo hte job. If you expect to run the database on a seperate machine at some point, with multiple connections, then I'd say SQLE. If it's only ever going to run on the one machine, then I'd say Access.

  What sort of medical programs - just client databases and so on

  
  
  

• Sperin

  What I have read is that when you use datasets and datatables it opens up the connection, so many of the errors that marked me was "operation time out, couldn't open database" or something like that.

  To solve the problem basically what i did was tell it to leave the Database connection open at the begging of the insertion or editing of a patient.

  Later I got myself a book called Beggining Visual Basic 2005 Databases, very interesting and it has solve many problems but still thinking what would be better, Access or SQL server express ( I use the SQL server express file).

  I'm not a wizard man, don't like them, never did, I used to used them to start learning, but in this case everything was hard coded, no errors in the database addind, updating, deleting coding, (took 2.5 years of Database in school and .5 in datawarehousing), but still theory but once placed it in practice many problems occure

  I know there is capacity limits in both databases, but still have to check which one is more convenient.

  Also in the book it states that stored procedures are much faster than in-line coding or hard-coding, is it true and how much faster (aprox %)

  In advance I would like to thank you for your time and patience

  
  

• stemill

  Will post it tomorrow, have to find the development files, had to move my work area and lost many CD's.

  Thanks

  
  

• Atmapuri

  Made one for a Gynecologist, used sql 2005 express and was very slow to access data and marked a lot of errors.

  so remaking the Gynecologist and doing 1 for patology and one for oftalmology, togheter with other medical sections

  Forgive the misspelling working on different things and have not much time to write

  
  

• KellyRainmaster

  OK - was SSE on the local machine If so, I'd be interested in seeing why your connections were timing out.

  Yes, stored procs are faster, as their execution paths are cached by the database. They are also more secure, enhance seperation of the data layer, and can do stuff that is hard to do with string mashed SQL.

  
  
  

• alex_g_73

  // and was very slow to access data and marked a lot of errors.

  Well, the errors in your code for SQL Express will flow into Access. SQL Server is faster and more responsive than Access, but no matter what you use, you have to get the code right, for it to work :-)

  
  
  

• Flap

  Just pasting some of the code, the same code applies to the rest of the project, just different form.

  Private Sub NuevoPacienteToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NuevoPacienteToolStripMenuItem.Click

  Dim myAltas As New Pacient

  Dim HeredoNew As New HeredoFamiliares

  Dim GineObstNew As New GinecoObst

  Dim MotivoExploraNew As New MotivoExploracion

  Dim ProDiaResPenNew As New ProcDiagResPen

  Dim CitasObservacionesNew As New CitasObservaciones

  Dim EmbarazoNew As New Embarazo

  Dim result As DialogResult

  'ALTA DE PACIENTE

  myAltas.IdPacient = 0

  StatusLabel.Text = "Nuevo Paciente"

  result = myAltas.ShowDialog

  If result = Windows.Forms.DialogResult.OK Then

  If myAltas.RowsAffected = 1 Then

  StatusLabel.Text = "Paciente agregado satisfactoriamente."

  End If

  Else

  StatusLabel.Text = "Cancelado la operacion de agregar paciente."

  End If

  *********************************** Code of Pacient *************************

  Private Sub AceptarButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AceptarButton.Click

  Try

  If IdPacient = 0 Then

  Dim Pacient As GineObsDataSet.PACIENTESRow

  Pacient = myGineObsDataSet.PACIENTES.NewPACIENTESRow

  With Pacient

  .nombre = NombreTextBox.Text

  .ocupacion = OcupacionTextBox.Text

  .direccion = DireccionTextBox.Text

  .ciudad = CiudadTextBox.Text

  .referencia = ReferenciaTextBox.Text

  .tipo = TipoTextBox.Text

  .edad = Edad

  .telefono = TelefonoTextBox.Text

  .email = EMailTextBox.Text

  .fnacimiento = FechaNacimientoDateTimePicker.Value

  End With

  myGineObsDataSet.PACIENTES.AddPACIENTESRow(Pacient)

  m_rowsAffected = PacientesTableAdapter1.Update(myGineObsDataSet.PACIENTES)

  IdPacient = Pacient.idPaciente

  Else

  PacientBS.EndEdit()

  m_rowsAffected = PacientesTableAdapter1.Update(myGineObsDataSet.PACIENTES)

  End If

  Catch ex As Exception

  MessageBox.Show(ex.Message)

  End Try

  End Sub

  Private Sub Pacient_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

  If IdPacient > 0 Then

  PacientBS.Filter = "IdPaciente = " & IdPacient.ToString

  PacientesTableAdapter1.Fill(myGineObsDataSet.PACIENTES)

  End If

  End Sub

  This how the code goes, in all the forms, i have a binding source (for the comboboxes and other info), a dataset, a tables adapter. Also this code is before i placed the connection.open command.

  And like i said before, in development machine works kinda fast, still has it's slows, but in client machine slow and if using a virtual machine also slow, i tried it on a virtual machine with the following specs

  Athlon XP 1.5 Ghz (1800+)

  750 MB Ram

  5 GB Hard drive.

  PS. Code is written for a mexican doctor, so if you see values in spanish it's because of the language requirements

  
  

• phu_and

  No worries. If you post it on this thread, I'll get an email, so I'll be able to come and check it out. The post will also bump to the top of the list, so others will see it as well.

  
  
  

• kshawol

  Ok, on the development machine it was a bit slow, my guess it was all the subprograms that i had running.

  I made a test on a seperate machine, Athlon XP 1800+ (1.5 ghz) 256 MB Ram, after it has installed the SQL server Express and the other files, then it installed the program and it was still slow the connections. Not sure if it because i use a SQL Server Express file or what.

  
  
• Access or SQL Which one is better