i need to get help on how to connect database with visual basics project having many other tables in that database the basic thing i will like to know is about how to open multiple tables on single form and how to insert the values in table for the first time( ie when it is empty)

try this , declare this in module or general
but this is just a segment of add to the database code, my DB is called NHIF_DB

  

Public Cn As ADODB.Connection Public Rs As ADODB.Recordset

  

Public Sub DBConection()     Set Cn = New ADODB.Connection     'connection to sql server db if using sql server     ' Cn.ConnectionString = "Provider=MSDASQL.1;Persist  Security Info=False;User ID=sa;Password=jdev;Data Source=NHIF_DB;Initial Catalog=NHIF_DB"    ' Cn.Open 'connection to ms access bd if using MS Access     Cn.Provider = "microsoft.jet.oledb.4.0"     Cn.Open App.Path & "\NHIF.mdb" End Sub Public Sub addMnu_Click() call DBConnection      'Add into ADDRESS TABLE for spouse employer eddress             If frmSectionC.txtPostalAddress1.Text <> frmSectionA.txtSEmployedAddress.Text And _               frmSectionC.txtPostalAddress2.Text <> frmSectionA.txtCity.Text Then                 Set Rs = New ADODB.Recordset                 strSQL = ""                 strSQL = "Select * from ADDRESS  where [address_line1]='" & frmSectionA.txtSEmployedName.Text & "'" _                 & "and [city] ='" & frmSectionA.txtSEmployedAddress.Text & "'"                 Rs.CursorType = adOpenKeyset                 Rs.LockType = adLockOptimistic                 Rs.Open strSQL, Cn                 If Rs.EOF Then                    Rs.AddNew                    Rs!address_line1 = frmSectionA.txtSEmployedAddress.Text                    Rs!city = frmSectionA.txtCity.Text                    Rs.Update                 End If                 vAddress_id = Rs.Fields("address_id").Value                 Rs.Close                 Set Rs = Nothing           End If      ' add into SPOUSE_EMPLOYER TABLE             Set Rs = New ADODB.Recordset             strSQL = ""             strSQL = "Select * from SPOUSE_EMPLOYER  where [Spouse_EmployerName]='" & frmSectionA.txtSEmployedName.Text & "'" _             & "and [address_id]= " & CLng(vAddress_id)             Rs.CursorType = adOpenKeyset             Rs.LockType = adLockOptimistic             Rs.Open strSQL, Cn             If Rs.EOF Then                 Rs.AddNew                 Rs!Spouse_EmployerName = frmSectionA.txtSEmployedName.Text                 Rs!address_id = CLng(vAddress_id)                 Rs.Update             End If            vSpouse_Employer_id = Rs!spouse_employer_id             Rs.Close             Set Rs = Nothing                  'add into  SPOUSE             Set Rs = New ADODB.Recordset             strSQL = ""             strSQL = "Select * from SPOUSE  where [member_id]='" & frmSectionA.txtMembershipNo.Text & "'"             Rs.CursorType = adOpenKeyset             Rs.LockType = adLockOptimistic             Rs.Open strSQL, Cn             If Rs.EOF Then                 Rs.AddNew                 Rs!member_id = frmSectionA.txtMembershipNo.Text                 Rs!spouse_employer_id = CLng(vSpouse_Employer_id)                 Rs!spouse_firstname = frmSectionA.txtSFname.Text                 Rs!spouse_middlename = frmSectionA.txtSMname.Text                 Rs!spouse_lastname = frmSectionA.txtSLname.Text                 If frmSectionA.cboSBDay.Text <> "" And frmSectionA.cboSBMonth.Text <> "" And frmSectionA.cboSByear.Text <> "" Then                     Rs!spouse_birthdate = Format(frmSectionA.cboSBDay.Text & "/" & _                     frmSectionA.cboSBMonth.Text & "/" & frmSectionA.cboSByear.Text, "dd/mm/yyyy")                 End If                 Rs!spouse_check_number = frmSectionA.txtSChecknumber.Text                 Rs.Update            Else                 MsgBox "Spouse related to this Member arleady exist", vbInformation, "Add"             End If             Rs.Close             Set Rs = Nothing                          ' add into member DEPENDANT             Set Rs = New ADODB.Recordset             strSQL = ""             strSQL = "Select * from DEPENDANT  where [member_id]='" & frmSectionA.txtMembershipNo.Text & "'"             Rs.CursorType = adOpenKeyset             Rs.LockType = adLockOptimistic             Rs.Open strSQL, Cn             If Rs.EOF Then                 For rindex = 0 To 3 Step 1                     If frmSectionB.txtDependantNo(rindex).Text <> "" Then                         Rs.AddNew                         Rs!member_id = frmSectionA.txtMembershipNo.Text                         Rs!dependant_id = frmSectionB.txtDependantNo(rindex).Text                         Rs!full_name = frmSectionB.txtName(rindex).Text                         If frmSectionB.cboDate1(rindex).Text <> "" And frmSectionB.cboMonth1(rindex).Text <> "" And frmSectionB.cboYear1(rindex).Text <> "" Then                             Rs!de_birth_date = Format(frmSectionB.cboDate1(rindex).Text & "/" & _                             frmSectionB.cboMonth1(rindex).Text & "/" & frmSectionB.cboYear1(rindex).Text, "dd/mm/yyyy")                         End If                         Rs!relationship_id = frmSectionB.cboRelation(rindex).Text                         Rs.Update                      End If                  Next rindex             End If             Rs.Close             Set Rs = Nothing             MsgBox " Member Record Added Successfully", vbInformation, "Add"             Me.MousePointer = vbNormal

the point here is to open a table addd, close and open the next table or u can declar many recordset like Rs1,Rs2,Rs3 ...where each Rsx will refer to one table
hope it help

peter

[Edited by doup on 23-02-2005 at 01:27 PM GMT]

[Edited by doup on 23-02-2005 at 01:30 PM GMT]

____________________________
peter