Connecting to various data sources using an ADO connection string

Solution

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:mydatamyDb.accdb
  Where:
  • 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"
  Where:
  • 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.
  • Another useful thing to know about when working with text (csv)files is the schema.ini file that is found in the database folder specifies the names of the fields, their types, and some other useful info.
• Excel (xlsx)
  Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:tempCrash_Qtr01_2017.xlsx;Extended Properties=”Excel 12.0;HDR=Yes;”;
  Where:
  • Provider doesn’t change
  • Data Source always points to the appropriate folder
  • Extended Properties specifies that this is an Excel v12.0 file; has a header line (i.e. field names in the first record);

• 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
  Where:
  • 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:magicdemomap
  Where:
  • Provider doesn’t change
  • DBQ points to the appropriate folder
• PostgreSQL via ODBC driver
  Direct ADO:
  Provider=MSDASQL;Driver=PostgreSQL Unicode;Server=PdPostgreServer;Database=PeteTest1;MaxLongVarCharSize=500000
  Using DSN:
  Provider=MSDASQL;DSN=<aDSNName>
  Where:
  • Provider and Driver don’t change
  • aDSNName is defined in the Windows ODBC 32-bit manager
  • MaxLongVarCharSize determines how much varchar data will be read in a SELECT statement.  (default is 4096)
  • Driver can be found at: https://odbc.postgresql.org/