Queries losing fieldnames when importing from external mdb

[CompleteNewb]

I'm having this problem in Access 2007 and 2003 and 2000.

I was kind of surprised not to find a HUGE amount of info about this on the
web; perhaps by now it's a given, and only complete newbs like myself are
looking for answers.

I made a series of MakeTable queries, each using the tables made by the
last, then deleted my tables and compacted/repaired so I could email the mdb
to a user; they imported my queries, and already had the table that my FIRST
MakeTable query would use; so I figured when they ran my queries in order,
each successive one would have the table it needed, thus wouldn't lose its
design and/or field names. As you probably know, no dice.

The first MakeTable query ran OK, as it had the tabl there in the user's mdb
already. The 2nd make table DID make a table, and used the right table to
do it (the one made by the previous MakeTable query), but the fieldnames
were all "Expr:" and then the field from the table, etc. In other words,
the query DID make my table, and used the right table to make it, but in the
new table all the fields names were "Expr1", "Expr2", etc. Thus, when we
got to the next query that used THAT table, it couldn't find the right
fields, because they were all "Expr:" field names.

I'm familair with queries saying they can't make the connection, etc. etc.
when you try to run them or open them in design view and their tables are
gone. But I'm NOT familiar with deleting a table in an mdb, then
re-importing it, and queries STILL all lost their marbles when you next
opened them. It seems that importing them from an external mdb is a
completely different issue.

Now, I resolved this by sending the correctly named almost-empty tables
along with my queries in my .mdb, and the user HAD to import the tables
FIRST, then leave the "Get External Data" window, then go back and import
the queries. It wouldn't work to import the tables AND queries at the same
time.

So, in this day and age, is there really no way to send someone queries
without them losing their field names, even when they're not run yet?

Thanks for any help.

 

[John W. Vinson]

I'm having this problem in Access 2007 and 2003 and 2000.

I was kind of surprised not to find a HUGE amount of info about this on the
web; perhaps by now it's a given, and only complete newbs like myself are
looking for answers.

I made a series of MakeTable queries, each using the tables made by the
last, then deleted my tables and compacted/repaired so I could email the mdb
to a user; they imported my queries, and already had the table that my FIRST
MakeTable query would use; so I figured when they ran my queries in order,
each successive one would have the table it needed, thus wouldn't lose its
design and/or field names. As you probably know, no dice.

The first MakeTable query ran OK, as it had the tabl there in the user's mdb
already. The 2nd make table DID make a table, and used the right table to
do it (the one made by the previous MakeTable query), but the fieldnames
were all "Expr:" and then the field from the table, etc. In other words,
the query DID make my table, and used the right table to make it, but in the
new table all the fields names were "Expr1", "Expr2", etc. Thus, when we
got to the next query that used THAT table, it couldn't find the right
fields, because they were all "Expr:" field names.

I'm familair with queries saying they can't make the connection, etc. etc.
when you try to run them or open them in design view and their tables are
gone. But I'm NOT familiar with deleting a table in an mdb, then
re-importing it, and queries STILL all lost their marbles when you next
opened them. It seems that importing them from an external mdb is a
completely different issue.

Now, I resolved this by sending the correctly named almost-empty tables
along with my queries in my .mdb, and the user HAD to import the tables
FIRST, then leave the "Get External Data" window, then go back and import
the queries. It wouldn't work to import the tables AND queries at the same
time.

So, in this day and age, is there really no way to send someone queries
without them losing their field names, even when they're not run yet?

Thanks for any help.

I'm curious that you need any MakeTable queries AT ALL, and even more curious
that you would cascade them so that you are (apparently) creating multiple
identically structured tables. I've never had to do this, and am having
trouble visualizing why you would!

You can use a DDL (Data Definition Language) query to create a table, or
TransferDatabase to import an existing one, and append queries to migrate data
from one table into another, with the same or different fieldnames.

My only speculation is that you might have a timing issue: your code runs a
MakeTable query and then immediately tries to run a second query. The first
query is perhaps still running in the background and has not yet created and
stored the intermediate table.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[CompleteNewb]

Thanks for the response, John, but it's not a timing issue. When my code was
erroring out, I had the user reimport the queries and try manually running
them one at a time without the code. The first one went OK, because the
table in the design existed in the user's db when they imported the query.
But the next one that used the newly made table that previously did not
exist, while it used the right new table made by the last query, named all
the fields "Expr1" Expr2" etc. for its make-table output.

I make tables because users frequently want to be able to go back and look
at what the results WERE for the last time they did this, and the main table
that starts the process and upon which the rest is built is always changing,
so the tables need to be separate and not updated until they run the entire
process again.