XBase programmer needs help understanding Access

because foxpro is a SERVER database and Access is a PEER database


"Albert D. Kallal" wrote in message
...
"JAF" wrote in message
...

My experience, as little as it is, with Access is that joins of this
size take a lot of time to do.

Why would join data be any slower (or faster) in FoxPro?

Let's digress for a second and then come back to this point. It is
not a time consuming task to set up a relation in Foxpro. You do have
to do it in code, but you can save the code, and run the saved code
very quickly.

Right, but you telling me that you have to use developer and programming
time to set this up?


But to return to the point, I can set up a relation, and Foxpro does
not need to join in order for me to use the relation.

Well, actually, you DO NOT need to setup a relation in ms-access. You
simply
use the query builder, build your query and drop in the 2nd table..and
join
the data. You don't have to go into the table design mode and setup
relationships AHEAD of time. So, really, in Fox, you write code, and in
ms-access you use the query builder (and do not have to write code).

I not sure if you thinking that you somehow have to setup the
relationships
ahead of time...you do NOT have to at all!!!


The query builder is NOT dependent in anyway on the relationships you
setup.
You can join two tables and any same "type" field...and this join is no
some
special "ahead" of time setup.

So, what you having a problem here is that you expecting us to write code,
build software when the query builder lets I do this graphically, and
interactivity, and WITHOUT having to write code.

You can most certainly use in-line sql in your code to do this join also.
You seem to be hinting that it is some better, less work, and preferable
to
start writing software to do a relational join? (I defiantly missing
something here).

A comparison: it sounds like I need to learn the joins, I have no
choice.

And, you mean I have to learn code, write software to build that join in
Fox? (your kidding right!!!)

Look, the simple approach here is to use the tools you have, and to stop
thinking of some other tool. the difference between a good developer, and
a
bad one is that they realize every new set of tools they learn means
leaning
a new approach to how you solve problems...

ran the code I gave you earlier, not much is processsed until you ask
for the necessary fields in related files. With Access, you have to
process the query before you can use the related files.

No, not really. It depends on how your processing this data. If you
sending
the data to a report, then you going to process every record anyway. if
you
talking a code loop in which you process record by record, then no...you
don't have to load, or processing all records at once.

And, if you open the query, then the first page of the will only display,
and all records are not loaded.

This is matter of your context of how you need to process this data. In
your
postal code example, you not really explain when, or how you need to
process
this data.

Really, build the join, built a report...and out goes the data..and not
written ONE line of code. In fox, you *will* have loaded every record if
you
sending to the report.

You can certainly write a code processing loop in which you process this
joined data record by record, but your example does not hint of any needs
for this approach.

So, assuming you build your query, you could process the 1st 10 records
like:


dim rst as dao.recordset
dim i as interger
set rst = currentdb.OpenrecrodSet("myQuery")

for i = 1 to 10
debug.print rst!PersonName, rst!Postalcode
next i

And, if you don't even want to build the query, then go:

dim rst as dao.recordset
dim i as interger
dim strSql as string

strSql = "SELECT Personname, Postalcode, ZipcodeCity FROM Contacts " & _
"LEFT JOIN tblPostalCode ON Contacts.PostCode =
tblPostalcode.Postcode"

set rst = currentdb.OpenreocrdSet(strSql)

do while rst.Eof = false
debug.print rst!PersonName,rst!ZipcodeCity
rst.MoveNext
loop

So, you can write looping code, but why on earth would you? There is not
need, and the savings in the developer time can be used to feed the poor.

I spent a number of years in FoxPro, and now ms-access. You just need a
mind
set change. I used the last foxpro "dos" version, and a bit of the first
windows version. I prefer ms-access hands down, and I actually tend to
have
*strong* coding approaches in ms-access. So, you can write looping code
for
most stuff, but you will tend use the "looping" approach less when you
have
sql at your disposal. The "replace with" type commands in foxpro are
replaced with sql commands in ms-access. You can also use looping code,
but
then again..you don't need to.

Being to think in terms of sets, and sql. If you take the time to learn
sql,
then you don't need to define those relationships in the table design.
However, for parent/child relationships, you WANT to setup relationships
so
that deleting the parent record will delete the child record for you (and,
you will not be able to add a child record without first having a parent
record -- however, these *enforced* relationships are NOT needed for your
example type join, and DO NOT need to planned (setup) ahead of time.

Further, learning sql is the *best* thing you can learn. In fact I learned
sql in foxpro, and 16 years later sql is the ONLY skill set from back then
I
use. (it was also the most valuable thing I learned, and I have FoxPro to
thank for that!!!).

Learning sql means you can transfer that skill to any major database
system
you learn...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Access _IS_ a flawed implementation of SQL Server

there is no 'different relational needs'

there are crapppy ass programmers that use obsolete databases.. and there
are SQL Server developers





"John W. Vinson" wrote in message
...
On Sun, 06 May 2007 07:55:38 -0400, JAF wrote:

Thanks for both of your posts.

Can someone give me a small sample of the code for fast indexing and
relational work?

Just to make an additional emphasis to Gunny and David's excellent
suggestions:

Access is NOT a flawed implementation of FoxPro.

Both FoxPro and Access are very capable relational database tools.

But they are *DIFFERENT* relational database tools.

Insisting on using Access as if it were FoxPro, or using it slavishly in
exactly the same way that one would use FoxPro, is a recipe for
frustration.

John W. Vinson [MVP]