Background
This is an example showing how to work with Text and Excel data in conjunction with Jet MDBs, VB6, and ADO. Much of this only varies slightly when using ADO or ACE ACCDBs.
For some reason people seem challenged by the documentation. Without going into all of the excuses, I'll just assume they don't know where it is.
Documentation
ADO is pretty thoroughly documented in the MSDN CDs which you should have installed as your online Help for VB6. There are discussions and documentation here under the topic:
You can find a ton of great information about Jet SQL here too, under the heading:Code:MSDN Library
Platform SDK Documentation
Data Services
Microsoft Data Access Components (MDAC)
Some the choice items here include:Code:MSDN Library
Technical Articles
Microsoft Office and Visio
Microsoft Office 2000
Microsoft Access 2000
- Fundamental Microsoft Jet SQL for Access 2000
- Intermediate Microsoft Jet SQL for Access 2000
- Advanced Microsoft Jet SQL for Access 2000
Another valuable reference is only available if you have a version of Office or Access installed, and even then you might find that it is optional so you might have to go back into Office Setup and add it:
C:\Program Files\Common Files\microsoft shared\OFFICEnn\1033\JETSQL40.CHM
Where OFFICEnn will have the nn replaced by the version of Office or Access you have installed. I made a shortcut pointing to this that I keep in my development work area folder. You can put one on your Desktop or add one to your Start Menu.
The Example
This example begins by creating a database with an empty Ballots table. Then a text file in CSV format with 2000 Florida election ballot data by county is made into one Excel Workbook for each county, with the data for that county in one Worksheet named Ballots. Finally these Workbooks will have their data appended to the Ballots table in the database.
The example is attached as a ZIP archive. Here <root> is where you unZIPped to, and it contains the folder structure:
MakeMDBCode:<root>
Create Database {VB6 program MakeMDB}
Create Workbooks {VB6 program MakeWBs}
Create Workbooks 2 {Alternative MakeWBs}
Import Workbooks {VB6 program ImportWBs}
Source Data {A CSV-format text file under here}
Run this first to create database.mdb under <root>. It creates the database with a single empty table named Ballots.
There is no GUI, but it will signal completion via a MsgBox.
MakeWBs
Run either one of the two versions second, provided to cover some different ways of using Jet to work with text files and Excel Workbooks. MakeWBs will read:
<root>\Source Data\Florida2000.csv
To create one Excel Workbook per county in:
<root>\Workbooks
There is no GUI, but it will signal completion via a MsgBox.
If you see an exception like "Selected collating sequence is not supported..." you can ignore this. It comes from a known incompatibility between ADO and the VB6 IDE. Just run it a second time. This error never occurs for a compiled program.
ImportWBs
Run this program third. Unlike the others it has a GUI Form.
Click the Fetch Workbooks List button. This will search the Workbooks folder and list the Workbook file names in a checkmark ListBox.
Then you can click the Import Workbooks button. This will append the data from the Ballots Worksheet in each of the Workbooks into the Ballots table of the database, checking them off as it goes.
After that you can open <root>\database.mdb if you have MS Access installed to see all of the imported data in the Ballots table.
