Results 1 to 2 of 2

Thread: VB6 - Jet, Text, Excel

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Exclamation VB6 - Jet, Text, Excel

    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:
    Code:
    MSDN Library
        Platform SDK Documentation
            Data Services
                Microsoft Data Access Components (MDAC)
    You can find a ton of great information about Jet SQL here too, under the heading:
    Code:
    MSDN Library
        Technical Articles
            Microsoft Office and Visio
                Microsoft Office 2000
                    Microsoft Access 2000
    Some the choice items here include:
    • 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:
    Code:
    <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}
    MakeMDB

    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.
    Attached Images Attached Images  
    Attached Files Attached Files

  2. #2

    Thread Starter
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: VB6 - Jet, Text, Excel

    Ask here instead of PMs so others can benefit.

    Yes, of course you can import text data in tablular form (CSV, Tab-delimited, fixed field, etc.) directly into a Jet MDB table.

    This example was meant to show how to work with Excel as input, and the CSV text processing was just used to create a set of Workbooks to use. Consider MakeWBs a bonus, it covers both Jet text processing and another approach to Jet SQL's Excel syntax.

    And it saved on the attachment size quite a bit, XLS is a bulky format.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width