XBase programmer needs help understanding Access

[JAF]

I am an old Foxpro programmer, and I am having trouble udnerstanding
Acess. None of the books I have seem to explain things in terms I
understand.

I need to find out how to handle "relations" between files.

In Foxpro, if I had several files, I could "relate" them in order to
do lookups, data check's etc. How do I do this in Access?

For example, here are some sample files

PERSON file with the following fields

id
first name
last name
zip

ZIPCODE file with the following fields

zip
city
state

I would then give the following commands in Foxpro, whcih I think most
of you will udnerstand

use ZIPCODE index ZIP

select 2 (or select another data area)
usePERSON

set RELATION to zip into ZIPCODE

This line says to use the zip field in PERSON to do lok ups into the
ZIPCODE file, which is index on the zip field in the ZIPCODE file.

Now having done this, I can work with the fields from ZIPCODE as if
they are parrt of the PERSON file.

For example, I can list info

list first name, last name, address, zipcode.city, zipcode.state,
zipcode

Or I can sort like

sort on ZIPCODE.state, ZIPCODE.city to TEMPFILE

Realizing the commands are different betweent eh two software
packages, how do I achieve a similar relational capability in Access?

Thanks for your help

JF
www.discountdrivingschool.com





Best regards,

JAF
http://www.discountdrivingschool.com

 

[Ken Snell \(MVP\)]

In ACCESS, you would have tables for each of the "files" that you had in
FoxPro. Those tables can be in the same database; they do not have to split
up into separate ACCESS database files. Then, you can establish
relationships between the tables using the Relationships window. Also, you
can use queries as the RowSource of a combo box to do lookups for selecting
items in a form, etc.

 

[Albert D. Kallal]

The standard that we work in data in sol server, Oracle, Myself, and yes,
ms-access is to use sql.

Sql is the standard that binds all of these database systems together...

So,

For example, I can list info

list first name, last name, address, zipcode.city, zipcode.state,
zipcode

You use a query to produce the above...so, you would use the query builder..

In old foxpro, the old fashioned way you shown is to write
code. We save time, money, and effort in ms-access, and to accomplish what
you want, you would NOT have to write any code at all!

To accomplish what you want, you simply fire up the query builder, drop in
the

person file table, and then drop in the zipcode state file.

You then draw a join line from the persons zip code field to the zip code in
the zip code field.

MOST IMPORTANT is to double click on the join line, and make the join a LEFT
join (you need to do this since some persons may not have a zip code, and
you sill want query results.

So, to achieve the same in ms-access, you don't have to write ONE line of
code, but use the industry standard approach of using sql to solve your
problem. The beauty of this approach is that once you learn how you do this
in ms-access, then you can do the same with oracle, mysql, sql server...or
whatever...

So, no code would be written in ms-access......

Besides, just listing the results to the screen is of little use, and you
would normally build form, or report to produce the results you need.
However, one certainly will use the query builder interactively to slice and
dice data (just like you did in the old days with the "dot" prompt.

you can write sql by hand. And, you also can hit ctrl-g and bring up the
code environment, and use the 'debug' window as a dot prompt (that means you
can execute commands to update data, run code from a command line interface,
and even execute queries (or even execute sql directly).

So, in place of typing in:

replace all city with 'Edmonton'

You could type into the debug windows

currentdb.Execute "update tblPersons set city = 'New York' where city =
'N.Y'"

However, most find using the query builder easer then typing in commands
directly (but, you *can* type in commands if you long for a dot type
interface).

I hate to say, but you can't use your "old" way of thinking with
ms-access.....

here is some tips for a foxpro guy here:

Also, browse to the following page, and do a search for xbase, you find:



Converting from the old xBase
If you have a background in the old dBase/Foxbase, you may appreciate:

http://allenbrowne.com/tips.html

 

[Albert D. Kallal]

The standard that we work in data in sol server, Oracle, Myself, and yes,
ms-access is to use sql.

gotta love the spell check.....

sql server, and MySql are the corrections in the above!!!

 

[John W. Vinson]

I am an old Foxpro programmer, and I am having trouble udnerstanding
Acess. None of the books I have seem to explain things in terms I
understand.

I need to find out how to handle "relations" between files.

In Foxpro, if I had several files, I could "relate" them in order to
do lookups, data check's etc. How do I do this in Access?

In Access, very much unlike in Foxpro, there is usually only ONE (or often
two, see below) "files".

What you're referring to a File in FoxPro is a Table in Access. An Access .mdb
file will typically contain MANY tables, forms, reports, modules and other
objects.

You would use the Relationships window in Access - graphically - to create a
Relationship between the tables PERSON and ZIPCODE. This interface lets you
enforce referential integrity, prohibiting entry of a PERSON record with a
nonexistant zipcode.

You can then create Queries selecting one, two, ten or twenty tables, joined
appropriately; you can sort the query by any combination of fields, use any
fields for criteria for searches, and so on.

In my experience both FoxPro and Access can be used "relationally" - but in
general it's easier to do so in Access.


John W. Vinson [MVP]

 

[JAF]

Thanks for all of the psot, but now you guys have me very worried.
HEre is why: I do analysis on large files, or tables, to use the
Access terminologly.

so it sounds like that if I want to do comparisons and lookups on
three fields each with over 100,000 records in them, I would have to
do query which would entail a join.

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

Or am I missing something?

By the way, at the risk of seeming to make this thread become an
Access flame which I do not want to do, Foxpro handles this much
better.

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.

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.

A comparison: it sounds like I need to learn the joins, I have no
choice. My employer wants me to use Access. But with Foxpro, if you
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.

Is there any alternative to this?

Thanks for your post by the way.
Best regards,

JAF
http://www.discountdrivingschool.com

 

[Guest]

In Access, once the relationships are declared in the database,
they stay there forever. You don't need to mention them again:
they are used automatically.

It is possible to write code exactly like that shown in your
example, and 15 years ago, when many people were coming
across from FoxPro and dBase, many people did. Actually,
in Access, that code is very efficient, and fast.

But gradually, as people got used to the new standard way of
doing things, everybody agreed that the new way was better!

So I don't even have any sample code that I can paste here
to show you how to use the words Tabledef and Index to
do things exactly the way you used to.

But it is not a lot of work to learn the new way. Thousands
did learn, and made their choice.

Open up the relationship window. Declare the relationship.
Open up the Querydef window. Select the 'files' you want
to use. The 'zip' fields will join automatically. Select the
fields you want to use. Save the query with a new name.

Now you have a new 'file' that you can use whenever you
want to work with the fields from ZIPCODE as if they were
part of the PERSON file.

(david)

 

[David Cox]

I have written project in FoxPro. You really have to try and wash FoxPro out
of your brain and learn Access as from ground zero to get into it. The
"whats the Access equivalent of FoxPro's **** is, in general, not a helpful
way of doing things. Most of the time you will be looking for difficulties
when Access is so much easier. It is not easy throwing away years of
painfully gained training, but that is what you have to do. The world has
moved on.

Ask yourself questions like : How do I store information? How do I extract
information? How do I make it run faster? Once you have got hold of the
Access way of doing things you can then import useful bits of insight from
your store of FoxPro expertise.

One reason Microsoft bought FoxPro to get hold of their optimisation
technology. If you use the right section of fields to index Access is
amazingly fast.

Google newsgroups search will provide a goldmine of information and
resources from the newsgroups, and point to the right places on the web to
look.

HTH

David F. Cox

 

[JAF]

Thanks for both of your posts.

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

We have such big files, and it takes forever to get anything done.

Thanks.
Best regards,

JAF
http://www.discountdrivingschool.com

 

[Tom McNally]

Having used both Foxpro and Access for different projects, for larger
files (200K+ rows) I found Foxpro to be more efficient. Access bombs
when it gets to be near 1Gig in size, and it "grows" as you run
queries and do imports/deletes etc, so you need to keep track of size
and do more housekeeping than you would in Foxpro.

You have about the same amount of tools and access methods at your
disposal but they're implementation is different: MS Access is VBA and
GUI-based while Foxpro is a derivative of its X-based predessors.

The programming and relationship modelling is more "elegant" in
Access, so if you're designing something that may have lots of reports
and need to have user access limited or assisted with form controls,
Access is far superior.

For quick and dirty analysis, or the manipulation large files, or
Oracle, SQL Server or Sybase imports, then I'd stick with Foxpro.

Indexing. MS Access is more geared to GUI-based tools, so for examp if
you have a Foxpro file that you want to import into an MS Access
database and add a new index (it should import existing indexes I
think), or create a new table, you use the Import feature from the
File | Import menu, which is pretty straightforward. Then you can go
into the design mode of the imported table and index (unique or
compound or multiple) whatever fields you desire or that would be
important for joins to additional tables. Depending upon size of
table (and # of fields or indexes), initial index is pretty fast. The
relational model is just a matter of opening up the relationship
"window" and dragging the simliar fields from one table to the
dependent or equal field of the other table(s) --
Table1.empID->Table2.empID. Foxpr and your options are inner joins,
left/right (one to many), and the other standard SQL fare.

Check out http://www.mvps.org/access for help getting started.

 

[JAF]

Thanks for your post.

What we do is this:

We have very large data files that are extracted from our accounting
system, 100,000 records and higher. Many times we have 2 or more of
these to work with at a time. Our MDBs are already at 670M for one
month's analysis, and they are growing.

The tables need some manipulation in order to bring in regional and
district data so that we can select based on this regional and
district data. After we have selected the region or district records
that we want, and we export this into Excel. Then we do pivot tables
and other manipulations to analyze the data. The reason we use pivot
tables is that we can hire analysts with Excel knowledge, but it is
hard to find Access savvy analysts.

I see two opportunities in our present process for improvement.

First: the current selections are done with queries that are
excruciatingly too time consuming. So I need to find a way to reduce
the amount of time it takes to "relate" regional and district data and
do a selection on these. We do this with joins currently.

Second, the work we do in Excel with pivot tables is simply sorts and
subtotals that could be done in Access. They are the same every time
we do the analysis, and I worry that manual processes, which Excel is,
are open to human error. Cut and paste one too few rows or one too
many rows and you have an inaccurate result.

So I am hoping to put this all into an automated process.

Our IT department is not comfortable with my doing this in Foxpro,
since they don't know it.

Thus I have to do everything in Access.

Some of the analysis is already being done in Access, but as the size
of our tables becomes bigger, the slower Access gets. As I said, we
are using joints to relate the tables and select the records, and it
takes too long to do this.

Thus I need to find a quicker way to get data into the tables, relate
them, select based on the relationships, and then report on it.

Any thoughts on this process would be greatly appreciated.
Best regards,

JAF
http://www.discountdrivingschool.com

 

['69 Camaro]

Hi.

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

Sure. However, we can't see your database and don't know what you've
already done, but it sounds like you only imported the tables and didn't
bother taking any of the other steps required for a relational database,
such as set up primary keys and foreign keys, and possibly add indexes to
other columns used in joins, sorts and query criteria to make queries run
faster. Therefore, I'll list all of the steps you _should_ have taken, and
you can skip the ones that you've already done.

We have such big files, and it takes forever to get anything done.

That's where you'll see the huge difference between a beginner and someone
experienced with relational databases, and particularly with the database
tool he's using.

Honestly, it's a lot easier for a beginner to take these steps using the
GUI, but if you prefer code, then that's easy enough. In this example, the
two tables are named PERSON and ZIPCODE. When the step calls for you to
create a query, this is what you'll do:

1.) In the Database Window, ensure no item is selected, or at least no
table or SELECT query is selected.

2.) Select the Insert -> Query menu, then select the "OK" button to display
the "Show Table" dialog window. Select the "Close" button, because you
aren't going to use the GUI.

3.) Select the View -> SQL View menu to display the SQL View pane. This is
where you will paste the SQL code examples or type your own queries.

4.) Whatever is shown in the SQL View pane ("SELECT;") needs to be deleted
or overwritten by whatever you write or paste into the SQL View pane.
Essentially, you need to start with a blank slate for your query, but Access
tries to help you. If you're pasting in SQL code, that SQL code is all that
you need, not Access's "help."

5.) After you paste the SQL code or write your query, you will need to run
the query. Close the table that is going to be used in the query if it's
DDL (data definition language), or change the table's view from Design View
to Datasheet View if it DML (data manipulation language). Yes, I'll warn
you if it's going to be DDL in the following instructions.

6.) To run the query, ensure that the query you want to run is the active
window (the Title Bar of the window on top should give you a clue), and then
select the toolbar button marked with an exclamation point. (It's red in
Access 2003). It has a tool tip "Run" to help you identify it.

If you want to compare a "before" and "after" on a query with joined tables,
then create a new query and paste the following SQL code into the SQL View
pane:

SELECT PERSON.*, ZIPCODE.City, ZIPCODE.State
FROM PERSON INNER JOIN ZIPCODE ON PERSON.Zip = ZIPCODE.Zip
WHERE (ZIPCODE.State = 'Texas');

Replace Texas with any state you want that's listed in your table
(preferably with lots of records), then save this query. Get out your stop
watch, then run the query to see how much time it takes. Write down the
time, so you can compare it in a moment.

A. Primary Keys

Now, after importing the tables, a primary key should be assigned to each
table so that the database engine can uniquely identify each record in the
table. A side benefit is that the primary key will automatically be
indexed.

Did you add a primary key on the ID column in the PERSON table and the ZIP
column in the ZIPCODE table? If not, create a new query and paste the
following SQL code into the SQL View pane:

ALTER TABLE PERSON
ADD CONSTRAINT PrimaryKey PRIMARY KEY (ID);

This is DDL SQL, so ensure that the PERSON table is closed, then run the
query. Next, create a new query and paste the following SQL code into the
SQL View pane:

ALTER TABLE ZIPCODE
ADD CONSTRAINT PrimaryKey PRIMARY KEY (ZIP);

This is DDL SQL, so ensure that the ZIPCODE table is closed, then run the
query.

B. Relationships

The PERSON and ZIPCODE code tables are related, so if you don't want to set
the relationship in the Relationship window, the create a new query and
paste the following SQL code into the SQL View pane:

ALTER TABLE PERSON
ADD CONSTRAINT tblPersonZipCode_FK
FOREIGN KEY (ZIP) REFERENCES ZIPCODE ZIP;

This is DDL SQL, so ensure that the PERSON and ZIPCODE tables are closed,
then run the query. A side benefit to this constraint is that Jet will
automatically place a hidden index on the related column (because
referential integrity is enforced), so you don't need to add an index to the
column.

You can save these queries, but honestly, you'll probably never need them
again (unless you drop these constraints later and want to redo them), so
you don't need to save them. However, if you want an example for later
reference, you should save these queries.

C. Create a Query With a Join

Next, save your work and compact the database. (Select the Tools ->
Database Utilities -> Compact and Repair Database... menu.) If you didn't
create a query with a join on the two tables earlier, then create it now
with the SQL code I gave you above, save it, get out your stop watch, then
run the query to see how much time it takes.

It's a lot faster than without primary keys and indexes, huh?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

['69 Camaro]

Hi.

We have very large data files

Very large data files are in the terabyte range. Yours isn't even in the
gigbyte range yet. This is subjective, but most database experts would tell
you that your database is small if it fits on one CD without even compacting
it and compressing it first.

Our MDBs are already at 670M for one
month's analysis, and they are growing.

Whoa! Red flag! Have you compacted the database? If so, and it's still
670 MB, then you will run out of space in a Jet 4.0 database file within
another two months. Jet 4.0 only holds up to 2 GB of data. With a more
optimized database design, you can stretch that to six or twelve months
total from the start, but you will need to migrate this data to a
client/server database with a much higher capacity, such as SQL Server or
Oracle.

However, I suspect there's no reason to panic, because you most likely
merely need to compact the database periodically and use a more optimized
database design. I would suggest hiring an expert Access developer to fix
things up and give further suggestions on the future of this database
application, because migrating to a client/server database if you don't
really need to will cost a _lot_.

The reason we use pivot
tables is that we can hire analysts with Excel knowledge, but it is
hard to find Access savvy analysts.

The main reason it's so hard to find Access-savvy analysts is that most
companies want to pay Access experts on the same pay scale as someone who
can only build an Access database application with the wizards, and doesn't
know a lick about relational database design, software development, nor how
to code in VBA. In other words, someone with world-class skills and many
years of experience gets paid about the same as a beginner with one day's
training or experience. Not too many folks are willing to put forth the
time and effort to reach that level if they get paid the same as folks who
put almost no effort into it.

the current selections are done with queries that are
excruciatingly too time consuming. So I need to find a way to reduce
the amount of time it takes to "relate" regional and district data and
do a selection on these. We do this with joins currently.

Joins are the only way to run a query with more than one table as the data
source. You need to divorce yourself of the belief that joins are
time-consuming. It's poorly designed table structures and queries, along
with database bloating, that make the query run slowly.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

['69 Camaro]

Hi.

Also, if you want to add an index to a table on certain columns to make
sorting on those certain columns faster or queries with criteria on those
certain columns run faster, then the SQL syntax is the following:

CREATE INDEX Name_IDX
ON PERSON (LastName, FirstName);

To create a unique index on two columns:

CREATE UNIQUE INDEX Name_UQ
ON PERSON (LastName, FirstName);

Or to create an index on a single column:

CREATE INDEX LastName_IDX
ON PERSON (LastName);

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[David W. Fenton]

A comparison: it sounds like I need to learn the joins, I have no
choice. My employer wants me to use Access. But with Foxpro, if
you 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.

This makes no sense to me at all.

If you have two tables in your Access MDB and you want to display
information from both, you create a query, join the tables on the
appropriate field and then save the query. Each time you need to
view the two tables joined, you use that saved query. How is that
more work than programming the same thing?

Secondly, if the join is slow, then you haven't defined appropriate
indexes on your Access tables. Once you do so, the join should be
extremely fast, unless you're dealing with millions of records.

So, it seems to me that you just aren't doing it right.

 

[David W. Fenton]

We have very large data files that are extracted from our
accounting system, 100,000 records and higher.

That's not a very big table, in my opinion. I have clients with that
many records in use all the time, running reports against it, etc.
The key is correct structuring of the tables and proper indexing.

Many times we have 2 or more of
these to work with at a time. Our MDBs are already at 670M for
one month's analysis, and they are growing.

Are you compacting on a regular basis?

The tables need some manipulation in order to bring in regional
and district data so that we can select based on this regional and
district data.

Are the tables properly normalized? If not, that could be a major
source of bloat and problems with indexing. That is, if you don't
have a properly normalized structure, your table may not offer you a
field to index that will allow a proper join with other tables.

Also, you need indexes for sorts not just for joins.

After we have selected the region or district records
that we want, and we export this into Excel. Then we do pivot
tables and other manipulations to analyze the data. The reason we
use pivot tables is that we can hire analysts with Excel
knowledge, but it is hard to find Access savvy analysts.

Why export the data to Excel? Excel can use Access data directly.

I see two opportunities in our present process for improvement.

First: the current selections are done with queries that are
excruciatingly too time consuming. So I need to find a way to
reduce the amount of time it takes to "relate" regional and
district data and do a selection on these. We do this with joins
currently.

*What* takes too long? The running of the query? If that's the
problem, then it means you haven't created appropriate indexes when
you imported the data.

If you're saying that *creating* the queries takes too long, then I
think you're nuts! Writing a query to join two tables takes about 60
seconds at most. Once you save it you don't have to do anything but
open it, change your criteria (if needed) and run it again.

Second, the work we do in Excel with pivot tables is simply sorts
and subtotals that could be done in Access. They are the same
every time we do the analysis, and I worry that manual processes,
which Excel is, are open to human error. Cut and paste one too
few rows or one too many rows and you have an inaccurate result.

Yes, sounds like it could be easily done in Access. Sorts and
subtotals are very easy in Access reports, not so easy in forms.

So I am hoping to put this all into an automated process.

Our IT department is not comfortable with my doing this in Foxpro,
since they don't know it.

Thus I have to do everything in Access.

Sounds like something that would be very trivial for Access, and
possibly a good learning project for you.

Some of the analysis is already being done in Access, but as the
size of our tables becomes bigger, the slower Access gets. As I
said, we are using joints to relate the tables and select the
records, and it takes too long to do this.

It seems to me that you've obviously not added appropriate indexes.

Thus I need to find a quicker way to get data into the tables,
relate them, select based on the relationships, and then report on
it.

I think the introduction of the term "relationships" into this
discussion was a mistake. A "relationship" in Access is a permanent
property of two tables, where the values in a field of one table are
related to values drawn from another table. In the context in which
you are working, I would say that you don't even need any
relationships -- all you need is joins. You *do* need the
appropriate indexes on the tables, but that's a matter of creating
them when you import the tables (or using the same table and
importing each data dump into the same table, with pre-existing
indexes on the appropriate fields).

The key thing that I would guess you are missing is proper indexing.
That would seem to me to explain all the performance problems you
are experiencing, which I have never seen in datasets of only a few
100K records.

 

[David W. Fenton]

Honestly, it's a lot easier for a beginner to take these steps
using the GUI, but if you prefer code, then that's easy enough.

I wuold say that learning Access with the steps you've just
suggested is going completely against the basic philosophy of the
whole design of Access. In other words, your instructions will get
the job done, but won't really help a FoxPro user understand the
basic ideas behind the design of Access.

I've been making a living as an Access developer since 1996 have
never executed a single DDL statement. Not even once.

 

[Albert D. Kallal]

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...

 

['69 Camaro]

Hi, David.

I wuold say that learning Access with the steps you've just
suggested is going completely against the basic philosophy of the
whole design of Access.

Primary keys, foreign keys, and indexes aren't against the basic philosophy
of a relational database, which includes Access. I agree that the GUI
interface is soooo much easier to learn with, but his question, "Can someone
give me a small sample of the code for fast indexing and relational work?"
indicates that he wants the equivalent code for the commands he's used to in
FoxPro, not dragging, pointing, and clicking on items in the windows in
Access.

In other words, your instructions will get
the job done, but won't really help a FoxPro user understand the
basic ideas behind the design of Access.

I and most other Oracle professionals (at least those of us with formal
training) learned relational database design through SQL, not through a GUI
interface, and I suspect others who take formal courses using other
client/server databases also either learn the SQL first or else the SQL
behind the commands when working with the GUI interfaces of their database
tools, too.

I've been making a living as an Access developer since 1996 have
never executed a single DDL statement. Not even once.

I won't hold that against you. ;-) I will always have a very high respect
for your database skills, which are considerable. Most Access database
developers never have a need to use DDL, because the GUI has more than
enough tools for the job. But Jet SQL can do the job, too, and is often
more familiar to others whose backgrounds include using other relational
databases.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Larry Linson]

I agree that the GUI interface is soooo much
easier to learn with, but his question, "Can
someone give me a small sample of the code
for fast indexing and relational work?" indicates
that he wants the equivalent code for the
commands he's used to in FoxPro, not dragging,
pointing, and clicking on items in the windows in
Access.

But, as we know, one of the fastest routes to complete frustration is to
expect a different product to work, or be used, the same as some other one
with which you are familiar. Encouraging someone to try to do in code in
Access, which has easier alternatives, all the things they used to do in
code in FoxPro or dBase is "enabling" them to indulge in that fantasy which
will inevitably lead them to frustration.

Larry Linson
Microsoft Access MVP