Problems Importing from Access

[davey]

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

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?

Davey

[davey]

If it makes any difference, I might add that when I created the "New Database Query" for the Builder info (the part that worked fine), I also edited the query for Regions 1 and 3 and the info came across as expected. I say this, because I mentioned on the Client Table that one of my choices on the query was a Region 2.

- Davey