Статьи » Программирование » Delphi » Примеры подключения различных Баз Данных через ADO

odbc dsn connections

dsn

oconn.open " dsn=advworks;" & _
                   "uid=admin;" & _
                   "pwd=; file dsn

oconn.open " filedsn=\somepath\mydb.dsn;" & _
                   "uid=admin;" & _
                   "pwd=;"

Подробнее см.:  about odbc data sources и  how to use file dsns and dsn-less connections

  odbc dsn-less connections

Драйвер odbc для access

Стандартная политика безопасности:

oconn.open "driver={ microsoft access driver (*.mdb)};" & _
                   "dbq=\somepath\mydb.mdb;" & _
                   "uid=admin;" & _
                   "pwd=;"

Если используется workgroup (system database):

oconn.open "driver={ microsoft access driver (*.mdb)};" & _
                   "dbq=\somepath\mydb.mdb;" & _
                   " systemdb=\somepath\mydb.mdw;", _
                   "admin", ""

Подробнее см.:  microsoft access driver programming considerations

  Драйвер odbc для dbase

oconn.open "driver={ microsoft dbase driver (*.dbf)};" & _
                  "driverid=277;" & _
                  "dbq=\somepath;"

Примечание: Задайте имя файла в запросе sql. Например:
         ors.open " select * from user.dbf", oconn, , ,adcmdtext

Примечание: mdac 2.1 (и далее) требует borland database engine (bde) для обновления dbf-файлов dbase. ( q238431).

Подробнее см.:  dbase driver programming considerations

  

Драйвер odbc для excel

oconn.open "driver={ microsoft excel driver (*.xls)};" & _
                   "driverid=790;" & _
                   "dbq=\somepath\myspreadsheet.xls;" & _
                   "defaultdir=\somepath;"  

Подробнее см.:  microsoft excel driver programming considerations

  Драйвер odbc для oracle

Для текущего odbc-драйвера microsoft для oracle:

oconn.open "driver={ microsoft odbc for oracle};" & _
                   " server=oracleserver.world;" & _
                   "uid=demo;" & _
                   "pwd=demo;"

Для старого odbc-драйвера microsoft для oracle:

oconn.open "driver={ microsoft Драйвер odbc для oracle};" & _
                   " connectstring=oracleserver.world;" & _
                   "uid=demo;" & _
                   "pwd=demo;"

Подробнее см.:  connection string format and attributes

  Драйвер odbc для paradox

oconn.open "driver={ microsoft paradox driver (*.db)};" & _
                   "driverid=538;" & _
                   "fil=paradox  5.x;" & _
                   "defaultdir =c:\dbpath\;" & _
                   "dbq= c:\dbpath\;" & _
                   "collatingsequence=ascii;"  

Примечание: mdac 2.1 (и далее) требует borland database engine (bde) для обновления fdbf-файлов paradox isam. ( q230126).

Подробнее см.:  paradox driver programming considerations

  Драйвер odbc для sql server

Стандартная политика безопасности:

oconn.open "driver={ sql server};" & _
                   "server=carl2;" & _
                   "database=pubs;" & _
                   "uid=sa;" & _
                   "pwd=;"

trusted connection:

oconn.open "driver={ sql server};" & _
                   "server=carl2;" & _
                   "database=pubs;" & _
                   "uid=;" & _
                   "pwd=;"

Примечание: используйте пустой uid и pwd

С запросом имени пользователя и пароля

oconn.properties(" prompt") = adpromptalways
oconn.open "driver={ sql server};" & _
                   "server=carl2;" & _
                   "database=pubs;"
      

Подробнее см.:  sqldriverconnect (odbc)

  Драйвер odbc для sybase

Использование odbc-драйвера sybase system 11:

oconn.open "driver={ sybase system 11};" & _
                   " srvr= myservername;" & _
"uid=myusername;" & _
                   "pwd=mypassword;"

Использование odbc-драйвера intersolv 3.10 sybase:

oconn.open "driver={ intersolv 3.10 32-bit sybase};" & _
                   " srvr= myservername;" & _
"uid=myusername;" & _
                   "pwd=mypassword;"

Подробнее см.:  sybase system 10 odbc driver reference guide

  Драйвер odbc для sybase sql anywhere

oconn.open "odbc; driver= sybase sql anywhere 5.0;" & _
                   "defaultdir =c:\dbpath\;" & _
                   "dbf= c:\sqlany50\mydb.db;" & _
"uid=dbagroup;" & _
                   "pwd=;"
                   "dsn= """";"

Примечание: Необходимо включение тега dsn с нулевой строкой, иначе возникает ошибка 7778.

Подробнее см.:  sybase sql anywhere user guide

  Драйвер odbc для текста

oconn.open "driver={ microsoft text driver (*.txt; *.csv)};" & _
                   "dbq=\somepath\;" & _
                   "extensions=asc,csv,tab,txt;" & _
                   "persist security info=false"

Примечание: Задайте имя файла запросе sql. Например:

ors.open " select * from customer.csv", _
               oconn, adopenstatic, adlockreadonly, adcmdtext

Подробнее см.:  text file driver programming considerations

  Драйвер odbc для visual foxpro

Используя database container:

oconn.open "driver={ microsoft visual foxpro driver};" & _
                   "sourcetype= dbc;" & _
                   "sourcedb=\somepath\mysourcedb.dbc;" & _
                   "exclusive=no;"  

Без database container (free table directory):

oconn.open "driver={ microsoft visual foxpro driver};" & _
                   "sourcetype= dbf;" & _
                   "sourcedb=\somepath\mysourcedbfolder;" & _
                   "exclusive=no;"
  

Подробнее см.:  visual foxpro odbc driver and q165492

  ole db data link connections data link file (udl)

Для абсолютного пути:

oconn.open " file name=\somepath\pubs .udl;"

Для относительного пути:

oconn.open " file name=pubs .udl;"
  

Подробнее см.:  howto: use data link files with ado

Примечание: windows 2000 больше не поддерживает меню "new | microsoft data link". Поэтому для того, чтобы создать data link file, создайте текстовый файл, затем измените его расширение на ".udl".  Затем дважды щелкните по файлу и установите свойства data link.

ole db provider connections ole db provider for active directory service

oconn.open "provider= adsdsoobject;" & _
                   "user id=admin;" & _
                   "password=;"
      

Подробнее см.:  microsoft ole db provider for microsoft active directory service

  ole db provider for db2

oconn.open = "provider= db2oledb;" &
                     "network transport library=tcpip;" &
                     "network address=myserver;" & _
                     "package collection=mypackage;" &
                     "host ccsid=1142"
                     "initial catalog=mydb;" &
                     "user id=myusername;" & _
                     "password=mypassword;"

Подробнее см.:  inf: configuring data sources for the microsoft ole db provider for db2

  ole db provider for index server

oconn.open "provider= msidxs;" & _
                   "data source=mycatalog;"
    

Подробнее см.:  microsoft ole db provider for microsoft indexing service

  ole db provider for internet publishing

oconn.open "provider= msdaipp.dso;" & _
            "data source= http://mywebsite/mydir;" & _
            "user id=administrator;" & _
            "password=mypassword;"
  

Подробнее см.: microsoft ole db provider for internet publishing

  ole db provider for microsoft jet

for standard security:

oconn.open "provider= microsoft.jet.oledb.4.0;" & _
                   "data source=\somepath\mydb.mdb;" & _
                   "user id=admin;" & _
                   "password=;"

if you are using a workgroup (system database):

oconn.open "provider= microsoft.jet.oledb.4.0;" & _
                   "data source=\somepath\mydb.mdb;" & _
                   " jet oledb:system database=system.mdw;", _
                   "admin", ""

note, remember to convert both the mdb and the mdw to the 4.0 database format when using the 4.0 ole db provider.

if your mdb has a database password:

oconn.open "provider= microsoft.jet.oledb.4.0;" & _
                   "data source=\somepath\mydb.mdb;" & _
                   " jet oledb:database password=mydbpassword;", _
                   "admin", ""

Подробнее см.: ole db provider for microsoft jet and  q191754

  

Примечание: you can also open an excel spreadsheet using the "ole db provider for microsoft jet"

oconn.open "provider= microsoft.jet.oledb.4.0;" & _
                   "data source=\somepath\expenses.xls;" & _
                   "ext ended properties="" excel 8.0;hdr=yes;"";"

where "hdr=yes" means that there is a header row in the cell range
(or named range), so the provider will not include the first row of the
selection into the recordset.  if "hdr=no", then the provider will include
the first row of the cell range (or named ranged) into the recordset.

  ole db provider for odbc databases

for access (jet):

oconn.open "provider= msdasql;" & _
                   "driver={ microsoft access driver (*.mdb)};" & _
                   "dbq=\somepath\mydb.mdb;" & _
                   "uid=admin;" & _
                   "pwd=;"

for sql server:

oconn.open "provider= msdasql;" & _  
                   "driver={ sql server};" & _
                   "server=carl2;" & _
                   "database=pubs;" & _
                   "uid=sa;" & _
                   "pwd=;"

Подробнее см.:  microsoft ole db provider for odbc

  ole db provider for oracle (from microsoft)

oconn.open "provider= msdaora;" & _
                   "data source=oracleserver.world;" & _
                   "user id=carl;" & _
                   "password=;"

Подробнее см.: microsoft ole db provider for oracle

  ole db provider for oracle (from oracle)

for standard security:

oconn.open "provider= oraoledb.oracle;" & _
                   "data source=myoracledb;" & _
                   "user id=carl;" & _
                   "password=;"

for a trusted connection:

oconn.open "provider= oraoledb.oracle;" & _
                   "data source=myoracledb;" & _
                   "user id= /;" & _
                   "password=;"
' or

oconn.open "provider= oraoledb.oracle;" & _
                   "data source=myoracledb;" & _
                   " osau thent=1;"
  

Примечание: "data source=" must be set to the appropriate net8 name which is knownto the naming method in use. for example, for local naming, it is the aliasin the tnsnames.ora file; for oracle names, it is the net8 service name.  

Подробнее см.:  connecting to an oracle database
(note, if you get a logon dialog, then click cancel, then per form a one-time free signup with oracle's technet system)

ole db provider for sql server

for standard security:

oconn.open "provider= sqloledb;" & _
                   "data source=carl2;" & _
                   "initial catalog=pubs;" & _
                   " user id=sa;" & _
                   " password=;"

for a trusted connection:

oconn.open "provider= sqloledb;" & _
                   "data source=carl2;" & _
                   "initial catalog=pubs;" & _
                   " trusted_connection=yes;"

to connect to a "named instance" (sql server 2000)

oconn.open "provider= sqloledb;" & _
                   "data source=carl2 \inst2;" & _
                   "initial catalog=pubs;" & _
                   "user id=sa;" & _
                   "password=;"

to prompt user for username and password:

oconn.provider = " sqloledb"
oconn.properties(" prompt") = adpromptalways
oconn.open "data source=carl2;" & _
                   "initial catalog=pubs;"  

to connect via an ip address

oconn.open "provider= sqloledb;" & _
                   "data source= xxx.xxx.xxx.xxx,1433;" & _
                   "network library=dbmssocn;" & _
                   "initial catalog=pubs;" & _
                   "user id=sa;" & _
                   "password=;"

Примечание:
"network library=dbmssocn" tells ole db to use tcp/ip rather than named pipes ( q238949).  
and 1433 is the default port number for sql server

Подробнее см.: connecting to a sql server data source

  remote ole db provider connections

the following connections strings use microsoft's remote provider  (ms remote). the ms remote provider tells ado to communicate  with the remote server (via the rds datafactory) and to use the  remote provider that is installed on the remote server.
ms remote - access (jet)

if you want to use an odbc dsn on the remote machine:

oconn.open "provider= ms remote;" & _
                   "remote server=http://carl2;" & _
                   "remote provider= msdasql;" & _
                   " dsn=advworks;" & _
                   "uid=admin;" & _
                   "pwd=;"

if you want to use an ole db provider on the remote machine:

oconn.open "provider= ms remote;" & _
                   "remote server=http://carl2;" & _
                   "remote provider= microsoft.jet.oledb.4.0;" & _
                   "data source=\somepath\mydb.mdb;", _
                   "admin", ""

if you want to use an ole db provider on the remote machine with a rds datafactory handler:

oconn.open "provider= ms remote;" & _
                   "remote server=http://carl2;" & _
                   " handler=msdfmap.handler;" & _
                   "data source=myadvworksconn;"

the corresponding entry in the \winnt\msdfmap.ini file would be:

[ connect myadvworksconn]
access = readwrite
connect = "provider=microsoft.jet.oledb.4.0;" & _
                "data source=mydb.mdb;" & _
                "user id=admin;" & _
                "password=;"
   ms remote - sql server

if you want to use an odbc dsn on the remote machine:

oconn.open "provider= ms remote;" & _
                   "remote server=http://carl2;" & _
                   "remote provider= msdasql;" & _
                   "network library=dbmssocn;" & _
                   " dsn=pubs;" & _
                   "uid=sa;" & _
                   "pwd=;"

if you want to use an ole db provider on the remote machine:

oconn.open "provider= ms remote;" & _
                   "remote server=http://carl2;" & _
                   "remote provider= sqloledb;" & _
                   "network library=dbmssocn;" & _
                   "data source=carl2;" & _
                   "initial catalog=pubs;" & _
                   "user id=sa;" & _
                   "password=;"

if you want to use an ole db provider on the remote machine with a rds datafactory handler:

oconn.open "provider= ms remote;" & _
                   "remote server=http://carl2;" & _
                   " handler=msdfmap.handler;" & _
                   "data source=mypubsconn;"

the corresponding entry in the \winnt\msdfmap.ini file would be:

[ connect mypubsconn]
access = readwrite
connect = "provider= sqloledb;" & _
                "network library=dbmssocn;" & _
                "data source=carl2;" & _
                "initial catalog=pubs;" & _
                "user id=sa;" & _
                "password=;"

Примечание: "network library=dbmssocn" tells ole db to use tcp/ip rather than named pipes ( q238949).