Howdy, Stranger!

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

Categories

Help, please. Need to access database from VB6 using sql

Hallo

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.
control1
name = member
database = C:datamy access filesSosimple97.mdb
recordsource = personal

control2
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
data1.Refresh
If data1.Recordset.EOF Then
MsgBox "No records matched that query."
SQL = "Select * from [" & TableSource & "]"
data1.RecordSource = SQL
data1.Refresh
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.

Sven


Comments

  • baroonbikasbaroonbikas Member Posts: 7
    I would recommend u to use ADO instead of DAO. ADO or Active Data Object is composed of three main objects , that are CONNECTION Object, RECORDSET Object and COMMAND Object.

    CONNECTION Object : It is used to open a connection to any database. See MSDN on how to open connection through Connection object. This object is used by both Recordset and Command Object to connect to database.

    RECORDSET Object : This object is very flexible for manipulating database. It create a table of tuples in response to your sql queries, which can be easily manipulated, i.e, you can modify and add new data to it. Actually recordset are like views with capabilty to modify and add new data which are affected back in database, while views aren't able to do so.

    COMMAND Object : This Object excute a sql query and returns a recordset object.

    Plz see MSDN for more information about ADO objects. I would like to say ADO is the best way to use database in VB as they are most flexible and support any database on earth through ODBC connectivity, but DAO can only be used on ACCESS database.
Sign In or Register to comment.