Hi guys. I want to know how i can generate SQL insert statements for my database called Northwind and save it in text file using VBA routine Generateinserts().
I be happy if some one show me how .Thanks

What exactly qare you trying to accomplish?

Looking at your post (and trying to discern what you mean), I think there is probably a better way to do what you are after.

Please try to use your shift key at the appropriate points; that makes for [u]MUCH</u> easier reading. (“I” is capital, “SQL” is capital, “VBA” is capital and function names like “generateinserts()” are clearer when they are more like “GenerateInserts().”)
A period after “Hi guys” would be helpful too.

It sounds to me like your goal is to populate a database with data.

What you seem to be asking is to have a text file that is essentially a series of insert statements, one for each record.

I'm guessing. But I think that once Brian gets beyond being annoyed at your typing skills, he'll tell you to store the data in a text file or Excel file, link to that file using a Linked table in Access and just build an Append query from the linked table to your database table. Of course you'll need to do this for each table. And you'll have to populate related tables in the right order.

I'm sure somebody out there will say this is an ideal situation for a macro. (Is that mmcdonal that would say that?) At any rate, once you get your files linked as tables and your append queries built, just create a macro that will run the queries in the right order.

I can understand why you desire a text file to have all the Insert Into statements for each record. But that will take a lot longer to build than the technique mentioned above.

But if that's what you want to do, it's not that difficult. You'll simply write a routine that opens a text file for output, then opens a record set on your table. Step through each record in the record set and write a line in the text file with the Insert statement. Something like,

INSERT INTO Table1 ( Field1, Field2 ) SELECT "Test" AS Expr1, "Test" AS Expr2 FROM Table1;

You can use some techniques to more easily build the insert statement. Like:

' This builds the list of fields to insert into
For each field in recordset
   strIntoList = strIntoList & field.name & ", "
next field

' This builds the values for the select
For each field in recordset
   strSelectValues = strSelectValues & recordset(field.name).value & ", "
next field

You'll have to do things like add quotes (") for text strings and hash marks (#) for dates. Sorry, I'm not going to write all of the code for you. I generally get paid to do that. But that should start you on the right track.

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

Thank u for your nice reply!
i try to do some programing with meta data using object models. There might be better ways to do it but my intention is to do some meta data programing!

Hey, I wasn't knocking Brian. We've all had days where we lash out at the wrong people for the wrong reason. I'm sure he wasn't picking on you specifically. It was just one of those days. I'm sure one day he'll have the perfect answer for one of your questions. No hard feelings.

I thought about your "project" some more. The procedure you're thinking about writing could easily be set up to take parameters allowing you to write your insert statements for any table to any file. Just have your parameters be the table name and the file name. For that matter, you could construct your file name from the table name. Then you'd have a consistent naming convention.

I'd definitely recommend using the File System Object for the file output.

BTW, Brian. Since you commented about writing techniques... I initially read most posts from this site in my email. Because of all of the formating you use, I have difficulty reading your posts. The formating just doesn't come through in my email. Instead I get brackets and slashes and the like. In fact, I usually end up opening the site to read your posts. But I'm not complaining. Just noting that to you in case it makes a difference to you.

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