I want crerate a vba routine using GenerateInserts() to write insert statmeents for current population of database and it save it into a text file so i can use that to re build the database in sql sever for example.

Example: current database called .Teniss Database and it has 7 tables and each tables has 4 columns and each table has 10 records on it. I want to all the insert statment for all these records be saved in a text file!

I be happy if some one help me here. Thanks

You mean a .sql file.

Just write the file. Do you know SQL Script?


mmcdonal

well i want an applecation in access that do that for me and reads all database tables and records and make insert statment for them and save it in a file!

Whoa! That is a tall order.

You would need to get your system data, and then parse it, and then put in the syntax and writelines to your .sql file.

Once you do this, sell it and make a fortune and retire.

The other option is to pull your Access database into Microsoft Visio PROFESSIONAL database designer, and then export it to SQL. It automates this whole process. It has to be Professional though, since Standard doesn't have the Database designer.

HTH

mmcdonal

In Microsoft Visio 2003 Professional, open a Database design, then Click on Database>Reverse Engineer...

This takes you to some dialog boxes to connect to your database (Set an ODBC for it first to make this easier), and pull in what you need.

When you are satified with the design, go to Tools>Export to Database... and follow the wizards.

HTH


mmcdonal

mmcdonal is right. It is SQL.

This is VBA right?
A little array work, and send it to the text editor that accepts createobject.

The SQL lines in VBA are of str value and need a little bit practice.

If you engage SQL in VBA please ask for more details or goto
Visal Basic and VBA:Access VBA:Report based on query programatically.

well i know visio can do that! But i am trying to build an access form that has buton and once you click on it, it goes trought each table in db and counts the number of records and generates that many insert statment and it display it .

You're going to have to “roll your own.”
Iterate through the Tabledefs collection, getting each tabledef.
Create an array of the fieldnames and their types.
Open a recordset on the table
Looping through the recordset, use that array to write out to a file the insert statement with the fieldnames from the array, syntax according to the field types you found, and data from the recordset.

It's a lot of work...

Method,

You already asked this question!

See http://p2p.wrox.com/topic.asp?TOPIC_ID=27774

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org