• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Export from any Database to any database in code

Export from any Database to any database in code

This article explains how to, using pure code, without having to reference Excel, Access, dBase libraries, etc, export data from one format to another.

Rate Export from any Database to any database in code

• 1
• 2
• 3
• 4
• 5

(9(9 Vote))

:::Updated 2/1/02:::I have received some corrected information thanks to Stephen Kent. This project requires MDAC, which apparently contains the Office (Excel, Access, etc) Libraries in it. Thus technically you do need the libraries/drivers for this to work, but you don't need the whole programs. To get Jet / MDAC, go to microsoft.com and search for MDAC 2.5 (I think 2.6 still has Jet...) or just MDAC, then Jet and download and install both.


:::Updated 1/31/02:::NOTICE: Some of the DAO code originated from Smith-Voice.com. All other code is from one of my programs. 


Also note that you do need MDAC AND MS Jet installed for THIS example to work properly.(MSJet is in either 2.6 or 2.5 or lower (can't remember for sure if 2.6 includes it). Or you can download them seperately from the MS Website.) You may also install/use a different provider by modifying the "Provider=" part of the SQL Statements. You do not need MS Office/Excel/etc if you have an appropriate Provider/Driver(i.e. - Jet) installed and MDAC.



If you have Excel 97, you will use the "Excel 8.0" definition in the SQL "INTO" Statements below. Excel 2000 is 9.0, and I believe 2002(XP) is 10.0. Replace "Excel 8.0" in the example code with the database name you wish to export to (I.E. - "Access 8.0", "Access 9.0", "dBase III", etc). Also in the project you must "Reference" (not add a component) One of the following: 



For DAO:

 
Microsoft DAO 3.6 Object Library 

Microsoft DAO 3.51 Object Library 


For ADO:


Microsoft Activex Data Objects 2.1(or higher) Library 

Microsoft ADO Ext 2.6 for DDL & Security 


Use one of the ADO/DAO references depending on what method you use. You should only need one. 


You need these for the ADO and DAO examples here. 





Here is some code that illustrates how to export, for example, from Access to Excel without having to have either product installed on your computer, or the libraries. 



(This example is ADO. you can also do this in code with DAO, altho it is an outdated method.)
Create a new project with a command button and a DAO (or ADO2.1sp2 & higher) reference, then copy this code to the button's Click event. This assumes there is a database at C:\WINDOWS\Desktop\Master Database\master.mdb.




=========================================

'

'This will demonstrate how to export from 

'MS Access to Excel, without either product 

'installed. This example uses ADO. It can also be 

'done in DAO with a little modification.

'NOTE: Must have ADO(Activex Data Objects)

'or DAO Referenced to use these examples




'Define the variables/objects 

Dim conn as new ADODB.Connection 

Dim SQL, ConnectString as String 


'Assign SQL And ConnectString --- Notice the 

'SQL "INTO" Statement---explained below: 


SQL = "SELECT * INTO [Excel 8.0;DATABASE=F:\Master\Exported.xls].[Master] From [Complexes]" 


CS = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\WINDOWS\Desktop\Master Database\master.mdb" 


'Open the Connection and export the database. 

conn.Open ConnectString 

conn.Execute SQL 


'Close the connection.

conn.close

set conn = Nothing


======================================


This example uses DAO:


======================================


Dim db as database


On Error Resume Next


Set db = Workspaces(0).OpenDatabase("C:\WINDOWS\Desktop\Master Database\master.mdb") 


db.Execute "SELECT * INTO [dBase III;DATABASE=C:\My Documents].[testb] FROM [Authors]" 


If Err.Number <> 0 then 'Always check this!!!

 Msgbox Err.Number & vbcr & Err.Description

End If 


=========================================


The generic layout for the SQL Statement:


SELECT tbl.fields INTO 
[dbms specifier;DATABASE="path"].[unqualified
filename; may be tablename or sheetname(in excel)] FROM [table or tables] 


=========================================


Using the brackets and dot operator, you get a proper output in the database type of your choice. You can customize the SQL statement to your needs. (Such as ordering, limiting to certain columns, adding columns, etc.) For some SQL help, go to Visual Basic Forum.com.


========================================


{Explanation of the ADO Example (Applies to DAO Example also)}


The important part of this, which does the exporting is the SQL Statement. The "INTO [Excel 8.0;DATABASE=F:\Master\Exported.xls].[Master]" is the part that does all the work. You can replace "Excel 8.0" with other databases such as dBase III, Access 8.0, etc. DATABASE="" is where you specify what file to export to, and if exporting to Excel, for example, the .[Master] is the sheet name to export to in the workbook. With Access it would refer to a table name. Every section of this part of the SQL Statement is required. Theoretically you could export to any database, altho I have not tested this beyond Access->Excel myself. 



I hope this will help some of you who do not wish to install Access/Excel/dBase III on every clients computer just for your program to run, as it has been a GREAT help to me.
FYI: The reasoning behind MS etc not making any fuss about this method is simple. They want you to have to install their software to perform this kind of functionality. In fact they say it is NOT possible. Well, now you know that it IS possible :)