ADO SaveAs or Convert with Excel before Closing

Keywords:   ADO SaveAs

---

Question:

Is there a way to do a save as, or db convert to different format,just before closing DB???

file = "c:\TEMP\ORDERS.MDB"
table = "Orders"

Conn = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%file%;Jet OLEDB:Engine Type=4" DB =
ObjectOpen("ADODB.Connection")
DB.Open(Conn)

*****???? db.save or db.convert ????????*******

DB.Close()
ObjectClose(DB)
exit

Answer:

I don't know your level of familarity with OLEDB or SQL, but uou could open your intended file with the Jet Engine, and use the Exrended Properties clause ' i.e Extended Properties=Paradox 4.0 - but perhaps easier is something like - will work with any Jet ISAM - this one does Excel 97/2000 - pretty generic too, all ADO - and you can incluse a WHERE clause to filter the data, just ensure you use fully pathed data sources.

cMDB = "C:\TEMP\MyMDB.MDB"
cXLS = "C:\TEMP\TEMP\.XLS"
cSheet = "MySheet"
cTable = "MyData"
cIsam = "Excel 8.0"
cSQL = "SELECT * INTO [%cIsam%;DATABASE=%cXLS%].[%cSheet%] FROM
[%cTable%]"

cConn = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%cMDB%"
DB = ObjectOpen("ADODB.Connection")
DB.Open(cConn)
RS = DB.Execute(cSQL)
DB.close()
ObjectClose(DB)
drop(DB,RS,cSQL,cConn)

You get a pretty neat 'side-effect' (you may get the same with DAO, I don't know ) - but by selecting into another data type, ACCESS will either update or create schema.ini in the dir you ran the script from. You get a modest data-dictionary of your table- an excellent bodyguard for the LAFF Extender.

Also remember, when exporting to text/dbase or paradox, your DATABASE= is only the path, not the full file; Excel is an exception since each 'tab' can be a database.

---

Article ID:   W15236