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

File Created: 2002:09:05:13:50:46

Last Updated: 2002:09:05:13:50:46

Database Search

TechHome

ADO SaveAs or Convert with Excel before Closing

Question:

Answer: