Erlandsen Data Consulting. These pages are no longer updated and are only available for archive purposes. Click here to visit the pages with updated information. Below you will find example ADO connection strings that you can use when connecting to. Connecting to any data source using an ODBC DSN Data Source Name You must first create the DSN, this can be done using the ODBC Data Source Administrator. Control Panel or Administrative Tools on your computer. Make sure to create a SYSTEM DSN not a USER DSN when creating an ASP solution. Open DSNSystem. Data. Source. Name. Uiduserid Pwduserpassword. Connecting to an Access database using standard security OLEDB cn. Oracle Instant Client enables applications to connect to a local or remote Oracle Database for development and production deployment. The Instant Client libraries. Open ProviderMicrosoft. Jet. OLEDB. 4. 0. Data Sourcec foldernamedatabasename. User Idadmin Password. Connecting to an Access database using standard security ODBC cn. Similar to user1206604s answer I set up an ODBC connection using ODBC Data Source Administrator for examples sake well name it DEMO and connect like this. Erlandsen Data Consulting, Excel VBA tips. Connecting to an Oracle database using the current Microsoft provider OLEDB. Accessing ODBC Databases from Windows with Oracle Database Gateway for ODBC DG4ODBC The Oracle Database Gateway for ODBC DG4ODBC allows you to build heterogeneous. Oracle Call Interface OCI is an application programming interface API that lets you create applications that use function calls to access an Oracle database and. OLE DB Provider for Active Directory Service OLE DB Provider for Advantage OLE DB Provider for AS400 from IBM OLE DB Provider for AS400 and VSAM from Microsoft. Open driverMicrosoft Access Driver. Connecting to an Access database using a workgroup system database OLEDB cn. Hi, I am using SQL SERVER 2005. Oracle db This has been working perfectly for weeks and even this mornign adn now all of a. The database recognizes 1,746,000 software titles and delivers updates for your software including minor upgrades. Open ProviderMicrosoft. Jet. OLEDB. 4. 0. Data Sourcec foldernamedatabasename. BIEMG/img/standby_db_object.gif' alt='Microsoft Odbc For Oracle Without Tnsnames Example' title='Microsoft Odbc For Oracle Without Tnsnames Example' />Jet OLEDB System Databasec foldernamesystemdatabasename. Connecting to an Access database using a workgroup system database ODBC cn. Open driverMicrosoft Access Driver Connecting to a MS SQL server using standard security OLEDB cn. Open Providersqloledb. Data Sourceservername. Initial Catalogdatabasename. User Iduserid Passworduserpassword. Connecting to a MS SQL server using standard security ODBC cn. Open driverSQL Server. Connecting to an Oracle database using the current Microsoft provider OLEDB cn. Open Providermsdaora. Data Sourceservername. User Iduserid Passworduserpassword. Connecting to an Oracle database using the current Oracle provider OLEDB cn. Open ProviderOra. OLEDB. Oracle. Data Sourceservername. User Iduserid Passworduserpassword. Connecting to an Oracle database using the current Microsoft driver ODBC cn. Open driverMicrosoft ODBC for Oracle. Connecting to an Oracle database using the Oracle ODBC driver ODBC cn. Open driverOracle ODBC Driver. You have to define the dbq databasename in the tnsnames. Connecting to a Sybase ASE database using the OLE DB provider OLEDB cn. Open ProviderSybase. ASEOLEDBProvider. Srvrservername,5. Catalogdatabasename. User Iduserid Passworduserpassword. Connecting to a Sybase database using the Sybase System 1. ODBC cn. Open driverSYBASE ASE ODBC Driver. Connecting to a Sybase database using the Sybase System 1. ODBC cn. Open driverSYBASE SYSTEM 1. Connecting to a My. SQL server OLEDB cn. Open ProviderMy. SQLProv. Data SourceMYSQLDB. User Iduserid Passworduserpassword. Connecting to an Excel workbook OLEDB cn. Open ProviderMicrosoft. Jet. OLEDB. 4. 0. Data Sourcec foldernameworkbookname. Extended PropertiesExcel 8. HDRYes. Use Excel 8. Excel 9. 7 or later. Use Excel 5. 0 for workbooks created by Excel 5 or 9. Use HDRYes if the data source contains a header row in the cell range or named range. If HDRNo the returned recordset will include the first row. Connecting to an Excel workbook ODBC cn. Open driverMicrosoft Excel Driver Connecting to a d. Base database ODBC cn. Open driverMicrosoft d. Base Driver dbf. Open select from tablename. Cmd. Text. You will need the Borland Database Engine BDE to update d. Base files. Connecting to a Paradox database ODBC cn. Open driverMicrosoft Paradox Driver Paradox 5. X. ASCII. The extra space character after You will need the Borland Database Engine BDE to update Paradox ISAM files. Connecting to a text file OLEDB cn. Open ProviderMicrosoft. Jet. OLEDB. 4. 0. Data Sourcec foldername. Extended Propertiestext HDRYes FMTDelimited. Open select from filename. Open. Static, ad. Lock. Read. Only, ad. Cmd. Text. Connecting to a text file ODBC cn. Madison Makes Bounce Madison Rose there. Open driverMicrosoft Text Driver Open select from filename. Open. Static, ad. Lock. Read. Only, ad. Cmd. Text. The format of the text file is determined by using a schema information file. The schema information file, which is always named Schema. A Schema. ini file is always required for accessing fixed width data. You should use a Schema. Date. Time, Currency, or Decimal data. Example Schema. ini file for a file named filename. FormatTab. Delimited. Col. Name. HeaderTrue. Character. SetANSI. Example Schema. ini file for a file named filename. FormatDelimited. Col. Name. HeaderTrue. Character. SetANSI. Example Schema. ini file for a file named filename. FormatFixed. Length. Col. Name. HeaderFalse. Col. 1Field. Name. Char Width 3. 0. Col. Field. Name. 2 Date Width 1. Col. 3Field. Name. Integer Width 1. 5. Col. 4Field. Name. Float Width 2. 0. Character. SetANSI. More information about ADO, DAO, RDO, SQL, Oracle and. NET is available from. Carl Prothman. net. Document last updated 2. Printerfriendly version. Paresh from India wrote 2. CET Excellent. Hye All I was just in search of such a marvellous site, which can helpful in any critical conditions. Keep developing. Ole P. Norway wrote 2. CET Re What Driver Should I Use for Foxpro 2. Database. Take a look at the link above to Carl Prothman. Lester wrote 2. 00. CET What Driver Should I Use for Foxpro 2. Database. What Driver Should I use for foxpro 2. Greg from Boca Raton, FL wrote 2. CET Connect to Oracle in Access through Excel. I have an Access database that runs a query from an Oracle server. I want to be able to run this query from Excel and pull in the data. The Oracle Connect pop up screen comes up and asks for a username and password. Is there a way to send the username and password without having to type it in Thank you. Ole P. Norway wrote 2. CET Re Index file request when importing dbf file. I have never seen this error message before. You can try this optional approach to opening d. Base files. It it doent work, youll have to search the Google Groups to see if you find another solution, since this seems to be a rather common problem. You will have to create a data source name DSN for your d. Base files first e. Base. Files. DSN. Dim cn As ADODB. Connection, rs As ADODB. Recordset. Dim str. Conn As String, str. Table As String. str. Conn ProviderMSDASQL. Persist Security InfoFalse DSNd. Base. Files. DSN UID. Source. DBC Folder. Name Source. TypeDBF ExclusiveNo Background. FetchYes CollateMachine NullYes DeletedYes. Set cn New ADODB. Connection. cn. Open str. Conn. Set rs New ADODB. Recordset. str. Table dbf. File. Name. Without. The. File. Extension. Open select from str. Table, cn, ad. Open. Dynamic, ad. Lock. Read. Only, ad. Cmd. Text. Flora wrote 2. CET Index file request when importing dbf file. Hi, Ive made an ADO connection to a dbf file which I would need to import to excel and using it, but unfortunately when I try to select data from the dbf file, it keeps on giving me the error message Index not found but the. Gilson Jr. from Piracicaba, Brazil wrote 2. CET Good information. Congratulations Very good Ole P. Norway wrote 2. 00. CET Re Very Good Site. Try, it will probably work. Charles from England Wiltshire wrote 2. CET Re Very Good Site. ODAC 1. 2c Release 2 installation instructions. December 2. 01. 3Download includes the following products a Oracle Developer Tools for Visual Studio 1. Oracle Data Provider for. NET 4 1. 2. 1. 0. Oracle Data Provider for. NET 2 1. 2. 1. 0. Oracle Providers for ASP. NET 4 1. 2. 1. 0. Oracle Providers for ASP. NET 2 1. 2. 1. 0. Oracle Database Extensions for. NET 4 1. 2. 1. 0. Oracle Database Extensions for. Annette Larkins Book. NET 2 1. 2. 1. 0. Oracle Services for MTS 1. Oracle Provider for OLE DB 1. Oracle ODBC Driver 1. Oracle SQLlus 1. Oracle Instant Client 1. Oracle Universal Installer 1. System Requirements The following items are required for ODAC Windows operating system. Windows 8 Pro and Enterprise editions, Windows 7 Professional, Enterprise, Ultimate editions, Windows Server 2. Windows 8 Pro and Enterprise editions, Windows 7 Professional, Enterprise, Ultimate editions, Windows Server 2. Standard, Datacenter, Essentials, and Foundation Editions, Windows Server 2. R2 and Windows Server 2. HPCAccess to an Oracle Database Server Oracle 1. Release 2 or later Additional Notes on Requirements ODP. NET requires Microsoft. NET Framework. ODP. NET, Managed Driver requires. NET Framework 4 or later up to. NET Framework 4. 5. ODP. NET, Unmanaged Driver requires. NET Framework 3. 5 SP 1 or later up to. NET Framework 4. 5. Oracle Developer Tools for Visual Studio requires Microsoft Visual Studio 2. Visual Studio 2. 01. Visual Studio 2. 01. Note Visual Studio Express Editions are not supported with the Oracle Developer Tools. However, ODP. NET is supported with Visual Studio Express Edition. If you use distributed transactions with ODP. NET, Unmanaged Driver, then Oracle Services for Microsoft Transaction Server Ora. MTS must be installed. If you use distributed transactions with ODP. NET, Managed Driver, then only the Ora. MTS recovery service needs to be configured. The recovery service can be configured by installing Ora. MTS or running the Ora. MTS recovery service utility, oramtsctl. Consult the Ora. MTS documentation for instructions on creating the recovery service with the utility. ODAC does not support Code First in this release. Installation Instructions The instructions below apply to installing ODAC using Oracle Universal Installer. Xcopy installation instructions are included with the download itself. Download the ODAC zip file into a temporary directory. Note Do not download this file into the Tmp directory. Unzip its contents to the directory. Run Oracle Universal Installer OUI by launching the setup. OUI will lead you through ODAC installation on your machine. After the installation, you may delete the zip file and the unzipped folders and files. Note If an ODAC beta is installed, uninstall it before installing this ODAC release. Connection Setup Quick Start Automatic Setup New in ODAC 1. When installing ODAC in a new Oracle Home, OUI automatically copies the Oracle local naming tnsnames. Oracle Home into the newly installed ODAC home. Alternatively, existing Oracle Home, besides the last active one, to the new ODAC Oracle Home. OUI provides location information for these files from up to three other existing Oracle Homes if they exist. The ora files can be customized if the new Oracle Home uses a different configuration from the previous Oracle Home from which the files were copied over. If installing into an existing ODAC Oracle Home, no new If installing onto a machine without any previous Oracle Homes present, OUI will ask the user for the database connection alias information. OUI will then automatically create the tnsnames. If no alias information is provided, no tnsnames. Even if the user doesnt have all the database connection information readily available, Oracle recommends inserting placeholder values during the install process, then modifying the tnsnames. Install Changes to Machine. ODP. NET, Managed Driver. If installing ODP. NET, Managed Driver, OUI automatically copies TNSADMIN and LDAPADMIN entries into the. NET maching. config file if present in pre existing Oracle Homes. Manual Setup. Two of the most common methods for connecting an Oracle client to a database are EZCONNECT and TNSNAMES. EZCONNECT is the easiest to setup. TNSNAMES is much more maintainable in the long term. If you are new to Oracle, we recommend you use EZCONNECT. You only have to choose one or the other to connect. These quick start instructions assume you have a valid username and password for the database server. Note In the setup instructions below, ORACLEHOME represents the directory where the Oracle client Home was installed on your machine. A typical directory for an OUI Oracle client Home is C applt user product1. You will need to know where this directory is before proceeding. EZCONNECT Setup 1. Copy the sqlnet. ora file located in the following directory ORACLEHOMENetworkAdminSample to this directory ORACLEHOMENetworkAdmin This file tells the Oracle client by what methods e. EZCONNECT Oracle client can connect to the Oracle database server. The Oracle client must then specify a valid user name, password, and data source to connect to the database server. To specify a data source, you can use the EZCONNECT format. In the ODP. NET data source attribute, use the following format to define how the client connects to the database server host portservicename host the database server machines host nameport the database server machines port on which it listens for incoming connection requestsservicename the databases global name. For example, some syntactically valid connection strings follow user idhr passwordhr data sourcesales server 1. If the port number is not specified, 1. TNSNAMES Setup. An Oracle Net service name allows the Oracle client to use a simple alias to connect to the database server. The alias definition contains all the information needed to create a connection to the database server. Alias information is stored in the tnsnames. ORACLEHOMENetworkAdmin directory. This alias is used as the data source value in your connection string. ODAC installations do not create a tnsnames. The following instructions assume you will have to create a new tnsnames. Copy the tnsnames. ORACLEHOMENetworkAdminSample to this directory ORACLEHOMENetworkAdminlt data source alias DESCRIPTION           ADDRESS PROTOCOL TCPHOST lt hostname or IP PORT lt port           CONNECTDATA           SERVER DEDICATED           SERVICENAME lt database service name  data source alias the shortcut name given to identify the connect descriptor. In the ODP. NET connection string, developers set the Data Source attribute to the data source alias. IP the database server machines host name or IP addressport the database server machines port on which it listens for incoming connection requests. In general, this value is set to port 1. The data source alias, hostnameIP, port, and database service name in the tnsnames. If the TNS entries in the ORACLEHOMEnetworkadmintnsnames. Oracle client, create a ORACLEHOMEoracle. SOFTWAREORACLElt home key where lt home key is the registry key for the ODAC Oracle Home. For example, if the default Oracle Home location was used during the install, the proper entry for the oracle.