ADO - Exporting Tables

[hkwood]

Robert Smith gave a great tip on "Export to any type of database"... The tip was directed for DAO. Does this information work as well for ADO? I would like to export a recordset from one database such as Oracle or SQL Server and import or append it to an Access DB using ADO. After the data is processed, the path needs to be reversed. Export from Access DB using ADO and appending to Oracle or SQL server.
Thank you in advance
KW

[SmithVoice]

Hi KW, glad you liked that code. Still undocumented into it's 3rd Jet release <g>.

That line of SQL now works with the ADOX2.1sp2 release and the new Jet4 provider. However it is a Jet based solution. So it exports and converts any ISAM format that the Jet ISAM filter dlls and/or ODBC dlls can work with (Excel2-9, Lotus 123 wk1/wks, HTML table, Jet 2/3/4, CSVText, Outlook/Exchange, xBase:CodeBase/Clipper/FoxPro/dBase and Paradox.

however, SQL Server and Oracle engines do not support the syntax. If you can get the remote server tables linked via DAO or ADO commands to a temporary Jet file then you could conceivably do the exports/conversions, but you have to have a physical Jet file (even if it is temporary as my demos show) to run the routines.

For more info (including the new ADO additions):
http://www.smithvoice.com/vb5expt.htm

------------------
http://www.smithvoice.com/vbfun.htm

[SmithVoice]

One last thought ... can you code to get the Oracle/SQLServer data out to a text file?

Once it's a CSVText file go ahead and use it as a linked table to a temporary Jet database and do your conversions to whatever other format you want. (Then delete the temporary jet file and the text file). It may sound complicated but it's only a few lines of code and it is really quite fast.

Such conversion is shown in the demos at the smithvoice.com/vbfun.htm "Export To Anything" page .. and as I said it is now available to ADOx.

-Smith



------------------
http://www.smithvoice.com/vbfun.htm

[LordCallubonn]

Could I ask if there is a way to export from a dbgrid (or the like) to a excel file?? I have been looking, but haven't seen a example yet.. thanks you...

[SmithVoice]

dbGrid is made by Apex (www.apexsc.com). The VB freebie is a really scaled back version of True DBGrid. I don't think that they give any more than a csv text output ... You *could* switch grids like to Sheridan's DataWidgets3 to get text and HTML output directly from the grid or to VSFlexPro6 www.videosoft.com (http://www.videosoft.com) for text (team it with VSView6 for HTML and color proprietary reports)

But to get out to Excel or Lotus or any other ISAM the only way is to use the line of SQL mentioned above. You won't get it right from your grid, but it is a simple matter of passing the same source SQL that you use for the grid to the database object and let it do the conversions for you. Grids are great for display and GUI but the best conversions are done with code. And it is very simple.

-Smith

------------------ http://www.smithvoice.com/vbfun.htm

[This message has been edited by SmithVoice (edited 08-05-1999).]

[LordCallubonn]

Thank you.. I see I have made more of a quest then I thought.*L* Here is the SQL line that I am using for this proj.. I have a excel writing codes so i know this should be simple, but I really don't know SQL as greatly as I should.. I apologise I I upgraded my dbgrid when I got vb6.. So I have the standard ver. not much better I know, but it works..

Private Sub cmdSearch_Click()

Data1.RecordSource = "SELECT * FROM Info WHERE ClockNumber = " & txtTargetName.Text & ""
On Error GoTo eh
Data1.Refresh

TDBGridS1.Visible = True

If txtTargetName.Text = "" Then TDBGridS1.Visible = False

eh: Exit Sub
End Sub


I just have to replace the sql line in the Excel exporting code to the sqlin this correct? *Kinda lost, but learning*

[LordCallubonn]

Thank you.. I see I have made more of a quest then I thought.*L* Here is the SQL line that I am using for this proj.. I have a excel writing codes so i know this should be simple, but I really don't know SQL as greatly as I should.. I apologise I I upgraded my dbgrid when I got vb6.. So I have the standard ver. not much better I know, but it works..

Private Sub cmdSearch_Click()

Data1.RecordSource = "SELECT * FROM Info WHERE ClockNumber = " & txtTargetName.Text & ""
On Error GoTo eh
Data1.Refresh

TDBGridS1.Visible = True

If txtTargetName.Text = "" Then TDBGridS1.Visible = False

eh: Exit Sub
End Sub


I just have to replace the sql line in the Excel exporting code to the sqlin this correct? *Kinda lost, but learning*

[SmithVoice]

If the SQL is getting records as is then it's a simple matter of duplicating teh SQL in the Export syntax such as:

''Jet to Excel 4

Dim sSQL as string

sSQL = "SELECT * INTO [Excel 4.0;DATABASE=" & --path to a folder where you want to make the worksheet -- & "].[" & -- unqualified name of the worksheet xls file -- & "] FROM [Info]"

dbobject.execute sSQL, dbfailonerror


''Jet to Excel 5/95

Dim sSQL as string

sSQL = "SELECT * INTO [Excel 5.0;DATABASE=" & --path and workbook xls file you want to make or use -- & -- name of the new or existing worksheet you want to create in the the workbook-- & "].[" & -- unqualified name of the worksheet to make or use -- & "] FROM [Info]"

dbobject.execute sSQL, dbfailonerror

''Jet to Excel97, the same as above but use Excel 8.0; as the specifier

You are not limited to dumping full tables you can merge multiple tables into a single Excel workseet, rename fields, etc, etc. Like:

Dim sSQL as string

sSQL = "SELECT (Info.Name) AS NewNames, (Details.Address) AS MailPlaces INTO [Excel 4.0;DATABASE=" & --path to a folder where you want to make the worksheet -- & "].[" & -- unqualified name of the worksheet xls file -- & "] FROM [Info], [Details] WHERE [Info].ID = [Details].ID AND [Info].BirthDay > #6/24/45# ORDER BY [Details].Address"


There is a demo that does Jet/Excel conversions at http://www.smithvoice.com/vbda.htm that might give you more.

-S

------------------
http://www.smithvoice.com/vbfun.htm