Exporting Access 2003 queries causes problems with definition

  • Thread starter Thread starter Rotsey
  • Start date Start date
R

Rotsey

Hi,

When I export queries in 2003 to a empty db that are to be
imported into a another version of the original db
the queries lose there joins for one thing and also
the columns have "Expr1" in the field row.

Is this by design, worked fine in 97.

Anyone know how to fix this??

rotsey
 
Hi, Rotsey.
When I export queries in 2003 to a empty db that are to be
imported into a another version of the original db
the queries lose there joins for one thing and also
the columns have "Expr1" in the field row.
Click to expand...

That's because the tables the queries depend upon don't already exist in the
empty database file. Import the necessary table definitions (without data)
into the (new) empty database, then export the queries from the original
database into the new database, and Jet won't rewrite the queries for you.
It's easiest to just create a template database file with the table
definitions and then make a copy of this file to export the queries into.

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

Yes that s what I didn't want to have to do and you didn't with 97.

But you bring up a good idea to create template file with definitions.

The tables are linked SQL tables. 300 of them.

I have a automated build process and the tables being there
is going to confilct because they are local. So I will have to
change that as well.

All because microsoft changed/or stuffed up

I really can't see why they have so many problems with new versions
have functionality removed. Surely if you had a database of tests for a
previous version and then running those tests on your new version
would make sure all functionality is sustained.
 
Hi, Rotsey.
The tables are linked SQL tables. 300 of them.

I have a automated build process and the tables being there
is going to confilct because they are local.
Click to expand...

How about waiting to import the queries until after the SQL Server tables
are linked? That way the table definitions are available, and Jet won't
hunt for what isn't there and rewrite the SQL statements when it can't find
the tables.

Or . . .

In your template database file, turn off "Track name AutoCorrect Info"
(which you should do a as a rule anyway), then import the Jet table
definitions and save that as your template. Make a copy of the template
file and import the queries into the copy. Do whatever else needs to be
done for the build, then delete the Jet tables and link to the SQL Server
tables. Don't open the queries in Design View between the time the Jet
tables are deleted and the SQL Server tables are linked. The imported
queries' SQL statements should remain as they were in the original database
file.
I really can't see why they have so many problems with new versions
have functionality removed.
Click to expand...

Often the new version (Jet 4.0 vs. Jet 3.5 in this case) has a different
architecture to enable additional and improved features, and that different
architecture may not allow some of the old features to exist.

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.
 
thanks Gunny, I will sort it out

'69 Camaro said:
Hi, Rotsey.


How about waiting to import the queries until after the SQL Server tables
are linked? That way the table definitions are available, and Jet won't
hunt for what isn't there and rewrite the SQL statements when it can't
find the tables.

Or . . .

In your template database file, turn off "Track name AutoCorrect Info"
(which you should do a as a rule anyway), then import the Jet table
definitions and save that as your template. Make a copy of the template
file and import the queries into the copy. Do whatever else needs to be
done for the build, then delete the Jet tables and link to the SQL Server
tables. Don't open the queries in Design View between the time the Jet
tables are deleted and the SQL Server tables are linked. The imported
queries' SQL statements should remain as they were in the original
database file.


Often the new version (Jet 4.0 vs. Jet 3.5 in this case) has a different
architecture to enable additional and improved features, and that
different architecture may not allow some of the old features to exist.

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.
Click to expand...
 
Back
Top