Your opinion: programming Access vs. SQL

[vbmom]

This question is for people who know how to code for both MS Access and SQL Server databases.

I want to write some code to create & manage a small database. I can create a SQL db, and I've managed to write some code to read and write records, but I don't really know what I'm doing. I've been shown how to use a wizard in MS Access to create a db. I see that I can access the underlying code and with some effort can figure out how to customize stuff, but alas, still don't know what I'm doing.

My database must be accessible from the (mostly NT/W95) network. I don't know if MS Access can do that. Also, I don't know if MS Access would be installed on all computers, is that necessary to use the "application" or whatever it is you make with it?

Given that I could probably figure out ways to use either tool, which is the *easiest* way to go; the way that would not require weeks worth of research and posts to this message board?

Thanks for anything you got.

[freewilly]

The first question you need to ask regards the size of the database. Access is a baby, great for small databases. SQL Server is for medium to large databases. If your dtabase is small use Access

The best way to acces it is with ADO

Add this to your VB Project by Clicking Projects|References|Microsoft Activex Data Object

Then you can declare a connection object and a record set object. Here is an example from my code

Dim cnPrintLetterAux As New ADODB.Connection
Dim rsLetterDetails As New ADODB.Recordset
Dim intListIndex As Integer
Dim intTotalItems As Integer
Dim strInto As String
Dim strConc As String
Dim strSubject As String
Dim blnNeedsUpdate As Boolean
' Dim intTotalPages As Integer
m_intTotalLetters = 0
intTotalItems = lvwDebtors.ListItems.Count + 1
intListIndex = 1

'Connect to the access database
If cnPrintLetterAux.State = 0 Then
cnPrintLetterAux.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51; Data Source=" & App.Path & "\JiwaLetterAux.mdb"
cnPrintLetterAux.Open
End If

'Open the rs
rsLetterDetails.Open "Select * from LetterDetails", cnPrintLetterAux, adOpenKeyset, adLockOptimistic, adCmdText

'Delete old records
While Not rsLetterDetails.EOF
rsLetterDetails.Delete
rsLetterDetails.MoveNext
Wend

'Step through the records and put them into the table
While intListIndex < intTotalItems
'Item not checked so no letter sent Email: If no email address and no fax number

If (lvwDebtors.ListItems(intListIndex).Checked) And lvwDebtors.ListItems(intListIndex).SubItems(10) = "" And lvwDebtors.ListItems(intListIndex).SubItems(11) = "" Then 'send them to the letter table

With rsLetterDetails
.AddNew
!AccountNo = lvwDebtors.ListItems(intListIndex).SubItems(1)
!DebtorName = lvwDebtors.ListItems(intListIndex).SubItems(2)
!Address1 = lvwDebtors.ListItems(intListIndex).SubItems(12)
!Address2 = lvwDebtors.ListItems(intListIndex).SubItems(13)
!Address3 = lvwDebtors.ListItems(intListIndex).SubItems(14)
!Address4 = lvwDebtors.ListItems(intListIndex).SubItems(15)
!PostCode = lvwDebtors.ListItems(intListIndex).SubItems(16)
!LastLetter = lvwDebtors.ListItems(intListIndex).SubItems(3)
!LastLetterDate = lvwDebtors.ListItems(intListIndex).SubItems(4)
!ThisLetter = lvwDebtors.ListItems(intListIndex).SubItems(5)
!P3060Days = lvwDebtors.ListItems(intListIndex).SubItems(6)
!P6090Days = lvwDebtors.ListItems(intListIndex).SubItems(7)
!P90DaysPlus = lvwDebtors.ListItems(intListIndex).SubItems(8)
!Salutation = m_strSalutation
!Company = m_strCompany
!SignOff = m_strSignOff
!Sender = m_strSender

'Check what they owe, concatenate the details and add the correct intro string
If lvwDebtors.ListItems(intListIndex).SubItems(6) > 0 Then 'add the following text
!LetterSubject = m_strSubject1
!LetterIntro = m_strLetter1Intro
!LetterConc = m_strLetter1Conc
End If
If lvwDebtors.ListItems(intListIndex).SubItems(7) > 0 Then 'add the following text
!LetterSubject = m_strSubject2
!LetterIntro = m_strLetter2Intro
!LetterConc = m_strLetter2Conc
End If
If lvwDebtors.ListItems(intListIndex).SubItems(8) > 0 Then 'add the following text
!LetterSubject = m_strSubject3
!LetterIntro = m_strLetter3Intro
!LetterConc = m_strLetter3Conc
End If

blnNeedsUpdate = True
m_intTotalLetters = m_intTotalLetters + 1

End With
End If
intListIndex = intListIndex + 1
Wend


Good Luck