Create new blank MDB???

[mtl]

I am trying to create a new blank MDB using code from another MDB. But I seem to be beating my head against a wall :banghead: . Basicaly I want to create a new database and xport data from the current database into the new one for transfer to another. I have also thought of exporting into excell but am reluctant to do so as I am exporting data from 4 different tables and want to export to only one file. Which leads me to another question, can you specify a worksheet name when exporting to excell? If I could export the information from each table into a seperate worksheet of an excell spreadsheet, that would do the job.

Thanks ahead of time.

Mike:hi:

[alimcpill]

Look at the DoCmd.TransferSpreadsheet() method. This will output a specified table or query to a specified workbook (creating it if it does not already exist). It will create a new sheet for each query, so you can output 4 queries to the same workbook, each sheet named the same as the query it contains.

Note this is different to the DoCmd.OutputTo method, which will overwrite the workbook each time.

[OBP]

See this post for creating a blank MDB -
http://www.vbaexpress.com/forum/showthread.php?t=9029

You can then use the Transfer database function to transfer the data.

[mtl]

Thanks, I am on my way to work now and will give it a go.

[Ken Puls]

Here is another example (http://www.excelguru.ca/node/60) of the ADO route to create one. It also shows how to create a basic table in it if required.

HTH,

[mtl]

OK, I ran into one little snag.

When using :


DoCmd.TransferSpreadsheet acExport , , "tblAcft"

I get this error: This action or method requires a file name argument.

But the help file says: File Type Optional Variant.

Maybe I dont get the correct meaning of Optional!

Is there a way to get a "Save File As" dialog box to pop up?

If It can be done prior to running the TransferSpreadsheet action and store the path and file name to a string variable would be best as I have four tables to export to the file.

Thanks

[OBP]

mtl, the TransferSpreadsheet Help states -
FileName Optional Variant. A string expression that's the file name and path of the spreadsheet you want to import from, export to, or link to.
Your "Filename" does not include any kind of "Path".