Help, please. Accessing database using Sql from VB


I am fairly new at VB. I am busy writing an application where
I am using VB6.0. On a screen I have fields which are linked to
2 database tables. I want to create an array with details
of both database tables using sql code.

I have 2 data controls which are linked the following way.
name = member
database = C:datamy access filesSosimple97.mdb
recordsource = personal

name = leave
database = C:datamy access filesSosimple97.mdb
recordsource = leave

I basically want to get all employees and the amount of leave
they have take between various dates. The key field in both tables is called personal(number field). The date field is called datefrom
and dateto. The field where the user enters his date selection is
called dateselect. I have looked at various code but it seem's they
are all sql coding done in access and not in Vb itself. The code
below is very rough with quite a few elements missing.

I think I have to open both tables first.

Public Sub get_sqldata()
dim data1() as string
dim dataindex as integer
Dim dbs As dao.Database
Dim rst As dao.Recordset
dim strdbpath As String
Dim TableSource As String
Dim SearchField1 As String
Dim SearchField2 As String
Dim SQL As String

strdbpath = "C:datamy access filesSosimple97.mdb"
Set dbs = OpenDatabase(strdbpath)
Set rst = dbs.OpenRecordset("Personal", dbOpenTable)
Set rst = dbs.OpenRecordset("Leave", dbOpenTable)

TableSource = "Personal"
SearchField1 = "datefrom"
SearchField2 = "dateto"

SearchString = dateselect
SortField = "startdate surname"

'Here I do not know how to link from the personal table to
'the leave table. Does below actually write sql or do I have
'to show somewhere that below must be executed as sql code.
'I also have no idea what data1.RecordSource means.

SQL = "Select * from [" & TableSource & "]"
SQL = SQL & " Where [" & SearchField & "] Like ""*" & SearchString & "*"""
SQL = SQL & " Order by [" & SortField & "]"
data1.RecordSource = SQL
If data1.Recordset.EOF Then
MsgBox "No records matched that query."
SQL = "Select * from [" & TableSource & "]"
data1.RecordSource = SQL
End If

'Below is the code where I want to create the data in the array.
'Not sure how to fit this into the code.

ReDim Preserve data1(7, excelindex)
data1(0, excelindex) = personal
data1(1, excelindex) = surname
data1(2, excelindex) = name
data1(3, excelindex) = address
data1(4, excelindex) = postal
data1(5, excelindex) = telephone
data1(6, excelindex) = datefrom
data1(7, excelindex) = dateto
excelindex = excelindex + 1
first = first + 1

Thank you for your help and time.


Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!