Can I export a query's structure and data to SQL?

[ignite]

I have a relatively simple query where I would like to export its structure and data to SQL. This SQL will hopefully later be used in phpMyAdmin.

Is this possible in Access (my version is part of Office XP)?

Any help is greatly appreciated.

 

[Banana]

If it's one-shot deal, just go to query design, select that query and Tools -> View -> SQL to get the SQL code.

If it needs to be automated, loop through QueryDef.SQL property.

A cavaet: If the query has any functions, it may or may not work wherever you take it to (MySQL?) and also, Jet does not comply with ANSI SQL standard so you may trip over some issues such as using " instead of ' among other things.

HTH.

 

[MSAccessRookie]

I have a relatively simple query where I would like to export its structure and data to SQL. This SQL will hopefully later be used in phpMyAdmin.

Is this possible in Access (my version is part of Office XP)?

Any help is greatly appreciated.

If you are asking whether your query will run on an SQL or Oracle Server (or anything like them), then it will depend on a few things.

• Does the Query require input from the user?
  • The Input Control will need to be revised, or you can handle this like Form Controls below.
• Does the Query have any reference to Form Controls?
  • Not supported, but the query can be split and the Server Side can get and process the data, while the Access Side provides the input from the form
• Does the Query have Order By Requirements?
  • This may or may not be supported, depending on the version of SQL and the Server being used.
• Does the Query Use Other Access KeyWords not supported by SQL (DISTINCTROW, IIf, etc.)
  • Not supported and need to be replaced by Server alternatives.

I am sure that there are other considerations as well, but these are some of the more common ones.

 

[ignite]

Thank you for the replies.

If it's one-shot deal, just go to query design, select that query and Tools -> View -> SQL to get the SQL code.

This is SQL to build the query, I'm looking for SQL to export the structure and the data. Is this doable?

 

[MSAccessRookie]

Thank you for the replies.



This is SQL to build the query, I'm looking for SQL to export the structure and the data. Is this doable?

Can we see an example?

 

[Banana]

Thank you for the replies.



This is SQL to build the query, I'm looking for SQL to export the structure and the data. Is this doable?

Oh, I thought we were talking about queries, not table structures.

You would just do File->Export and decide on format you want to export- either RTF or ODBC database if you can link to the MySQL or whatever it is you're using. (You'll need a ODBC driver for that in case; should be a free download)

HTH.

 

[MSAccessRookie]

Oh, I thought we were talking about queries, not table structures.

You would just do File->Export and decide on format you want to export- either RTF or ODBC database if you can link to the MySQL or whatever it is you're using. (You'll need a ODBC driver for that in case; should be a free download)

HTH.

Be aware that not all structures export the same from Access to a Server, and each Server may have different rules. Among other things, there is a difference in the way servers handle Memo, Date, and Y/N class variables. In addition, some servers have a different Integer Base, and therefore might handle Numbers differently as well.

 

[Banana]

Thanks for that note, Rookie. You also reminded me of another way- you can export it as CSV file, which is next to SQL file in universal acceptance by various RDBMS.

HTH.