Crash Magic Online
  Intersection Magic
  Map Magic
  Knowledge Base
  Contact Us


Knowledge Base Article:CMW128 

Registration keys moved to client area: Select login on the left menu bar.

Search home page  

Connecting to various data sources using an ADO connection string

Article created: Aug 28 2003, updated: Feb 11 2014

Background:  ADO is the current preferred means of connecting a program to a data source in an MS Windows environment. This article describes how to connect to a text or dbase file data source using ADO without first creating an ODBC DSN.

Status:  Info - "how to" article
Keywords:  ado,odbc,control panel,del,csv,text,delimited,fixed field,fixed-field,connection string,DSN,dbase,dbf,ADO,mdb,accdb,access
Categories:   *Data* *Related software*

Using ADO to connect to an SQL Server is fairly straightforward, as is using ADO to connect to an ODBC data source. To connect to a text or dbase data source, the usual process is to create a named ODBC connection to the data source and connect to it through ADO. However, it can often be beneficial to connect directly to a file without creating an ODBC DSN.

The trick is to create an appropriate connection string.

A text/dbase data source is referred to by the folder/directory that it is in. That is, if you have a folder called c:\mydata with several data files in it, you connect to them by specifying the folder. Each file then becomes a table in the "database".

Here are connection strings that will provide access in such a situation:
  • Microsoft Access (accdb, not mdb) Newer Access database files require a driver that does not ship with Windows.  Download the Microsoft Database Engine Office 2010
    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\mydata\myDb.accdb
    • Provider doesn't change
    • Data Source always points to the appropriate Access database
  • Delimited text (csv)
    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\MyFolder;Extended Properties="text;HDR=NO;FMT=Delimited"
    • Provider doesn't change
    • Data Source always points to the appropriate folder
    • Extended Properties specifies that this is a text file; has no header line (i.e. field names in the first record); is delimited.

  • dBase (dbf) with files names longer than 8 characters can use the Microsoft OLE DB Provider for Visual FoxPro 9.0  (Contact Pd Programming if the link is no longer valid).
    Provider=VFPOLEDB.1;Data Source=C:\mydata;Password="";Collating Sequence=MACHINE
    • Provider doesn't change
    • Data Source always points to the appropriate folder
    • Collating Sequence is always equal to MACHINE
  • dBase (dbf) with file names shorter than 8 characters
    Provider=MSDASQL;Driver={Microsoft dBase Driver (*.dbf)};DBQ=c:\magic\demo\map
    • Provider doesn't change
    • DBQ points to the appropriate folder

Another useful thing to know about when working with text (csv)files is the schema.ini file that is found in the database folder that specifies the names of the fields, their types, and some other useful info.
January 21, 2018 5:08PM

© 1999-2018 Pd' Programming, Inc - Lafayette, CO USA