ExcelBanter - Problems Importing from Access

Hi,

This has to do with Access, but from inside of Excel, so I hope its
appropriate for this Forum.

Part of the Excel project I am working on has an external Access
database to retrieve client & contact information from. The VBA code
then reads the imported data and displays it in a userform.

The external database has 2 simple tables: Clients and Contacts.
These 2 tables have a one to many relationship (one client, many
contacts) based on a Region Number which is basically a number 1, 2, or
3 that represents the 3 major metropolitan areas we serve. The 2 tables
work fine together inside Access. I can add builders in the builder
table and add contacts from either the builder table or the contact
table.

In Excel, I created a "New Database Query" for the Client Table info,
filtered it for Region Number 2, and everything came in from the
database just as I expected for Region Number 2. Looked good. This
took up 9 columns (A-I). Then I skipped a column and starting in
Column K I did another "New Database Query" for the Contact Table Info,
but when I went to select my filter for the region number, my choices
were 2, 9, 16, and 210 but of course I was expecting choices of Regions
1, 2, and 3. In my database there are no regions 9, 16, and 210. If I
forced in a Region 1 on this query, my field labels came in, but no
data. I did end up choosing each of these oddball region numbers just
for kicks and data came into my spreadsheet, and in the correct columns,
but was not in any list order that I would have wanted (obviously
something is haywire). :confused:

Then I tried it on a completely blank worksheet in a new workbook and
only did the "New Database Query" for the Contact Table info and I
still got the choices of filtering on Region 2, 9, 16, and 210.

Then instead of doing a "New Database Query" I just did a plain and
simple "Import External Data" and brought in the whole table, and all
my regions were numbered 2, 9, 16, and 210.

I then went back into Access to look at my tables and they both showed
regions 1, 2, 3.

What happened to my 1, 2, 3 on the 2nd table when it came over into
Excel? :confused:

Davey

--
davey
------------------------------------------------------------------------
davey's Profile: http://www.excelforum.com/member.php...o&userid=24340
View this thread: http://www.excelforum.com/showthread...hreadid=385639