ObjectPal equivalent of TCursor and Scan?

[wdsnews]

I'm helping a school turn an Access 2000 Jet flat file database into a
relational database and this is my opportunity to get introduced to Access
VBA coding. In this example, I want to move phone numbers to a child table
and I hope to find an example code listing of the types of commands and
their use in Access. Two things would help me tremendously; a suggestion on
how to use the MS Access Help system, and a link to a sample listing that
demonstrates the use of basic open and record navigation.

I'm familiar with Paradox and I can use the ObjectPal 'Help' system to find
the 'Open' command and see some code listing choices that usually include a
loop, record or field addressing, and a 'Close'. But the MS Access Help
system seems much more limited.

In ObjectPal I would normally open a TCursor and Scan through the parent
table one record at a time. There's no need to open a visible table during
this one-time operation. Inside my 'Scan' loop I'd create a new record in
the child table for each phone number in the parent table.

;move Home phone number
if not parent.homephone.isblank() then
child.newrecord()
child.ID = child.ID.maxRec() + 1
child.parentID = parent.ID
child.phonetype = "Home"
child phoneNo = parent.homephone
endif
;move Work phone number
....etc...

Thank you for your help. I've already bought two books, but the code
examples all seem focused on opening visible tables. So, if you can provide
this little bit of help, I think I can run with it. Thank you.

 

[Dirk Goldgar]

In

I'm helping a school turn an Access 2000 Jet flat file database into a
relational database and this is my opportunity to get introduced to
Access VBA coding. In this example, I want to move phone numbers to
a child table and I hope to find an example code listing of the types
of commands and their use in Access. Two things would help me
tremendously; a suggestion on how to use the MS Access Help system,
and a link to a sample listing that demonstrates the use of basic
open and record navigation.
I'm familiar with Paradox and I can use the ObjectPal 'Help' system
to find the 'Open' command and see some code listing choices that
usually include a loop, record or field addressing, and a 'Close'. But
the MS Access Help system seems much more limited.

In ObjectPal I would normally open a TCursor and Scan through the
parent table one record at a time. There's no need to open a visible
table during this one-time operation. Inside my 'Scan' loop I'd
create a new record in the child table for each phone number in the
parent table.
;move Home phone number
if not parent.homephone.isblank() then
child.newrecord()
child.ID = child.ID.maxRec() + 1
child.parentID = parent.ID
child.phonetype = "Home"
child phoneNo = parent.homephone
endif
;move Work phone number
...etc...

Thank you for your help. I've already bought two books, but the code
examples all seem focused on opening visible tables.

Those would be bad or misleading books, then.

So, if you can
provide this little bit of help, I think I can run with it. Thank
you.

It is sometimes hard to find things in the help file, because of its
divided nature. The examples you were looking for are there, but not
always easy to lay your fingers on. The sort of operations you are
looking at involve the use of recordsets -- either DAO (using the
Microsoft DAO 3.6 Object Library) or ADO (using the Microsoft ActiveX
Data Objects 2.x Object Library). I prefer to use DAO, as it contains
special features for work with Jet databases.

I am not familiar with Paradox, but in Access there are two ways to
approach the problem you describe. By far the simplest and most
efficient way, though it doesn't correspond to the code sample you
posted, nor use recordsets, is to execute append queries to load the
child table. You would execute one query for each phonetype. Supposing
you have tables

Persons
----------
PersonID (autonumber primary key)
LastName (text)
FirstName (text)
HomePhone (text)
WorkPhone (text)
(other fields ...)

PhoneNumbers
-------------------
PhoneID (autonumber primary key)
PersonID (long integer)
PhoneType (text)
PhoneNo (text)

[Persons] is the parent table, and [PhoneNumbers] is the new, empty
child table.

You can load PhoneNumbers with each person's home and work phone numbers
by executing two append queries, like this:

'----- start of code example #1 -----

' Note: use of the defined constant dbFailOnError requires that
' a reference be set to the DAO library.

With CurrentDb

.Execute _
"INSERT INTO PhoneNumbers " & _
"(PersonID, PhoneType, PhoneNo) " & _
"SELECT " & _
"PersonID, 'Home' As PhoneType, HomePhone " & _
"FROM Persons " & _
"WHERE HomePhone Is Not Null",
dbFailOnError

.Execute _
"INSERT INTO PhoneNumbers " & _
"(PersonID, PhoneType, PhoneNo) " & _
"SELECT " & _
"PersonID, 'Work' As PhoneType, HomePhone " & _
"FROM Persons " & _
"WHERE WorkPhone Is Not Null",
dbFailOnError

End With

'----- end of code example #1 -----


A recordset-based approach to the same operation, though less efficient,
would look like this:

'----- start of code example #2 -----

' Note: requires that a reference be set to the DAO library.

Dim rsPerson As DAO.Recordset
Dim rsPhone As DAO.Recordset

With CurrentDb
Set rsPerson = .OpenRecordset("Persons")
Set rsPhone = .OpenRecordset("PhoneNumbers")
End With

With rsPerson
Do Until .EOF

' Add home phone, if there is one.
If Not IsNull(!HomePhone) Then
rsPhone.AddNew
rsPhone!PersonID = !PersonID
rsPhone!PhoneType = "Home"
rsPhone!PhoneNo = !HomePhone
rsPhone.Update
End If

' Add work phone, if there is one.
If Not IsNull(!WorkPhone) Then
rsPhone.AddNew
rsPhone!PersonID = !PersonID
rsPhone!PhoneType = "Work"
rsPhone!PhoneNo = !WorkPhone
rsPhone.Update
End If

.MoveNext ' advance to next person record
Loop
.Close
rsPhone.Close
End With
'----- end of code example #2 -----

So you see, that last code snippet doesn't look all that different from
the Paradox code you posted. We don't have to calculate and assign a
value to PhoneID, because we have defined that field (when we created
the table) as an autonumber.

 

[wdsnews]

Dirk,

That was very very helpful. Thank you.

In

I'm helping a school turn an Access 2000 Jet flat file database into a
relational database and this is my opportunity to get introduced to
Access VBA coding. In this example, I want to move phone numbers to
a child table and I hope to find an example code listing of the types
of commands and their use in Access. Two things would help me
tremendously; a suggestion on how to use the MS Access Help system,
and a link to a sample listing that demonstrates the use of basic
open and record navigation.
I'm familiar with Paradox and I can use the ObjectPal 'Help' system
to find the 'Open' command and see some code listing choices that
usually include a loop, record or field addressing, and a 'Close'. But
the MS Access Help system seems much more limited.

In ObjectPal I would normally open a TCursor and Scan through the
parent table one record at a time. There's no need to open a visible
table during this one-time operation. Inside my 'Scan' loop I'd
create a new record in the child table for each phone number in the
parent table.
;move Home phone number
if not parent.homephone.isblank() then
child.newrecord()
child.ID = child.ID.maxRec() + 1
child.parentID = parent.ID
child.phonetype = "Home"
child phoneNo = parent.homephone
endif
;move Work phone number
...etc...

Thank you for your help. I've already bought two books, but the code
examples all seem focused on opening visible tables.

Those would be bad or misleading books, then.

So, if you can
provide this little bit of help, I think I can run with it. Thank
you.

It is sometimes hard to find things in the help file, because of its
divided nature. The examples you were looking for are there, but not
always easy to lay your fingers on. The sort of operations you are
looking at involve the use of recordsets -- either DAO (using the
Microsoft DAO 3.6 Object Library) or ADO (using the Microsoft ActiveX Data
Objects 2.x Object Library). I prefer to use DAO, as it contains special
features for work with Jet databases.

I am not familiar with Paradox, but in Access there are two ways to
approach the problem you describe. By far the simplest and most efficient
way, though it doesn't correspond to the code sample you posted, nor use
recordsets, is to execute append queries to load the child table. You
would execute one query for each phonetype. Supposing you have tables

Persons
----------
PersonID (autonumber primary key)
LastName (text)
FirstName (text)
HomePhone (text)
WorkPhone (text)
(other fields ...)

PhoneNumbers
-------------------
PhoneID (autonumber primary key)
PersonID (long integer)
PhoneType (text)
PhoneNo (text)

[Persons] is the parent table, and [PhoneNumbers] is the new, empty child
table.

You can load PhoneNumbers with each person's home and work phone numbers
by executing two append queries, like this:

'----- start of code example #1 -----

' Note: use of the defined constant dbFailOnError requires that
' a reference be set to the DAO library.

With CurrentDb

.Execute _
"INSERT INTO PhoneNumbers " & _
"(PersonID, PhoneType, PhoneNo) " & _
"SELECT " & _
"PersonID, 'Home' As PhoneType, HomePhone " & _
"FROM Persons " & _
"WHERE HomePhone Is Not Null",
dbFailOnError

.Execute _
"INSERT INTO PhoneNumbers " & _
"(PersonID, PhoneType, PhoneNo) " & _
"SELECT " & _
"PersonID, 'Work' As PhoneType, HomePhone " & _
"FROM Persons " & _
"WHERE WorkPhone Is Not Null",
dbFailOnError

End With

'----- end of code example #1 -----


A recordset-based approach to the same operation, though less efficient,
would look like this:

'----- start of code example #2 -----

' Note: requires that a reference be set to the DAO library.

Dim rsPerson As DAO.Recordset
Dim rsPhone As DAO.Recordset

With CurrentDb
Set rsPerson = .OpenRecordset("Persons")
Set rsPhone = .OpenRecordset("PhoneNumbers")
End With

With rsPerson
Do Until .EOF

' Add home phone, if there is one.
If Not IsNull(!HomePhone) Then
rsPhone.AddNew
rsPhone!PersonID = !PersonID
rsPhone!PhoneType = "Home"
rsPhone!PhoneNo = !HomePhone
rsPhone.Update
End If

' Add work phone, if there is one.
If Not IsNull(!WorkPhone) Then
rsPhone.AddNew
rsPhone!PersonID = !PersonID
rsPhone!PhoneType = "Work"
rsPhone!PhoneNo = !WorkPhone
rsPhone.Update
End If

.MoveNext ' advance to next person record
Loop
.Close
rsPhone.Close
End With
'----- end of code example #2 -----

So you see, that last code snippet doesn't look all that different from
the Paradox code you posted. We don't have to calculate and assign a
value to PhoneID, because we have defined that field (when we created the
table) as an autonumber.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)