Import MS access data

[Kathie D. Baxter]

I have Excel 2004 on Mac OSX. I'm working in a primarily PC environment.
We have a database on the network that is in MS Access. I've found a Mac
program called Actual ODBC that allows me to use Excel and Query to pull
that data up on my Mac. However, the way the database has been created,
there are people's names and phone numbers in one table, and their addresses
in another table. The common field in each table is an Address ID number.
I've tried using a join in Query to combine the data, but it always freezes
up and crashes on me.

I can pull the data in each table into separate worksheets in Excel, but
have addresses without names and names without addresses and have to scan
through the 2 sheets to find which address goes with which name.

Does anyone have experience with this? Is there a better program than
Actual? is there some limit to the amount of data I can expect it to deal
with? Is there some workaround in Excel that will enable me to combine the
data in the two worksheets?

 

[CyberTaz]

Without actually seeing what is going on my _guess_ is that the Address ID
field is *not* defined the same way on each of the 2 tables, so it won't
serve as a valid common field and/or the tables contain mismatched records.
If that is the case the number of records may very well become an issue
because the resulting query is trying to return a record set comprising the
# of records in the one table *multiplied by* the # of records in the other.
IOW, the 'Cartesian Product' is the result of matching every record in the
one table with every record in the other.

If all were rosey, the number of records wouldn't be a problem since you can
get all records from either table - an accurate record set _shouldn't_
exceed that # of records (unless there are quite a few with multiple
addresses/phone numbers).

Another possibility is that there is at least one intermediary or junction
table involved in the Access relationship which serve as the "missing link"
connecting the two tables.

Have you tried creating the query in access? If that succeeds use it as the
Excel record source.

 

[Kathie D. Baxter]

The database is actually in a third party program that is not very user
friendly. But it uses Access database tables. I don't have the rights to
manipulate anything in the Access tables, only view the data, and I don't
know anyone else here who knows Access. I can go to a PC to view
everything, but can't always find one available that no one is using.

The two tables that have the data in that I need are "Members" (individuals)
and "Addresses" (households). Actually, almost all of the records in
Addresses would have multiple matching records in Members. For instance,
John Doe, Jane Doe and Baby Doe would each have a separate listing in the
Members table, but all would have the same address which is only listed once
in the address table. The Address ID seems to be the linking data. At
least it is the same number in all the records I've scanned.

I'm unfamiliar with the term, "Cartesian Product." Is it referring to the
many to one relationship and could this be the source of my problem? I've
created and maintained an Access database in a previous job and therefore
learned my way around it, but it's been a couple of years since I've had any
reason to use it, so I'm a little rusty.

Thank you for your help.

Without actually seeing what is going on my _guess_ is that the Address ID
field is *not* defined the same way on each of the 2 tables, so it won't
serve as a valid common field and/or the tables contain mismatched records.
If that is the case the number of records may very well become an issue
because the resulting query is trying to return a record set comprising the
# of records in the one table *multiplied by* the # of records in the other.
IOW, the 'Cartesian Product' is the result of matching every record in the
one table with every record in the other.

If all were rosey, the number of records wouldn't be a problem since you can
get all records from either table - an accurate record set _shouldn't_
exceed that # of records (unless there are quite a few with multiple
addresses/phone numbers).

Another possibility is that there is at least one intermediary or junction
table involved in the Access relationship which serve as the "missing link"
connecting the two tables.

Have you tried creating the query in access? If that succeeds use it as the
Excel record source.

"I have Excel 2004 on Mac OSX. I'm working in a primarily PC environment.
We have a database on the network that is in MS Access. I've found a Mac
program called Actual ODBC that allows me to use Excel and Query to pull
that data up on my Mac. However, the way the database has been created,
there are people's names and phone numbers in one table, and their addresses
in another table. The common field in each table is an Address ID number.
I've tried using a join in Query to combine the data, but it always freezes
up and crashes on me.

I can pull the data in each table into separate worksheets in Excel, but
have addresses without names and names without addresses and have to scan
through the 2 sheets to find which address goes with which name.

Does anyone have experience with this? Is there a better program than
Actual? is there some limit to the amount of data I can expect it to deal
with? Is there some workaround in Excel that will enable me to combine the
data in the two worksheets?"

 

[Jim Gordon]

Hi Kathie,

The actual ODBC driver should allow you to do any valid SQL query that
creates a result set. You should be able to as many joins as you want.
I've joined up to six tables at a time with that driver and the results
were correct.

The queries you make using Excel and MS Query are completely independent
of any queries that are in the .mdb database.

So that task at hand is to try to figure out why you're getting a crash
when you try to join the tables.

You get good data when you make single table queries. I take it that
you've looked at the results to make sure there isn't something screwy
in one of the tables, such as a letter where a number is expected, or a
date where text is expected, or a null character when there should be an
empty cell.

That leaves us with doing standard troubleshooting stuff as a starting
place.

Here's a list of things to try in order. After each suggestion one try
to make a join and see what happens.
http://mcgimpsey.com/macoffice/office/troubleshooting.html

The steps may or may not cure this problem, but either way please post
back and let us know how it turns out.

-Jim Gordon
Mac MVP

 

[Jonathan Monroe]

Kathy, I can look at the database and tell you the exact SQL statement
you should be using. Just compress the .mdb file and send it to me at
(e-mail address removed).

We take customer privacy very seriously and will delete the database as
soon as we have solved your problem. We will also gladly sign an NDA
(non-disclosure agreement) if that is important to you.

Jonathan Monroe
Actual Technologies - ODBC for Mac OS X
http://www.actualtechnologies.com