Description

There are several ways to connect to datasources. This section shows a few samples of how you can create a connection to a datasource.

ODBC using DSN

oConn:Open("DSN=AdvWorks;" + ;

UID=Admin; + ;

PWD=;",NIL,NIL,NIL)

ODBC using File DSN

oConn:Open("FILEDSN=MyFileDsn.dsn;"+ ;

UID=Admin; + ;

PWD=;",NIL,NIL,NIL)

ODBC Access driver without DSN

Standard Security :

oConn:Open("Driver=Microsoft Access Driver (*.mdb);" + ;

"Dbq=.mdb;" + ;

"Uid=Admin;" + ;

"Pwd=;",NIL,NIL,NIL)

Using a Workgroup Database (system database):

oConn:Open("Driver=Microsoft Access Driver (*.mdb);" + ;

"Dbq=.mdb;" + ;

"SystemDB=.mdw;", + ;

,"Admin", "",NIL)

ODBC Driver for SQL Server

For Standard Security:

oConn:Open( "Driver=SQL Server;" + ;

"Server=robert;" + ;

"Database=pubs;" + ;

"Uid=sa;" + ;

"Pwd=;",NIL,NIL,NIL)

For Trusted Connection security:

oConn:Open("Driver=SQL Server;" + ;

"Server=robert;" + ;

"Database=pubs;" + ;

"Uid=;" + ;

"Pwd=;",NIL,NIL,NIL)

Note: use a blank UID and PWD

To Prompt user for username and password

oConn:Properties:[Item,"Prompt"]:Value := adPromptAlways

oConn:Open("Driver=SQL Server;" + ;

"Server=robert;" + ;

"DataBase=pubs;", NIL,NIL,NIL)

OLE DB Data Link Connections

For Absolute Path:

oConn:Open("File Name=C:\MyFolder\pubs.udl;",NIL,NIL,NIL)

For Relative Path:

oConn:Open("File Name=pubs.udl;",NIL,NIL,NIL)

OLE DB Provider for Index Server

oConn.Open "Provider=msidxs;" + ;

"Data source=MyCatalog;"

OLE DB Provider for Microsoft Jet (Access)

For standard security:

oConn:Open("Provider=Microsoft.Jet.OLEDB.4.0;" + ;

"Data Source=.mdb;" + ;

"User Id=admin;" + ;

"Password=;",NIL,NIL,NIL)

If you are using a workgroup (system database):

oConn:Open("Provider=Microsoft.Jet.OLEDB.4.0;" + ;

"Data Source=C:\MyDir\MyFile.mdb;" + ;

"Jet OLEDB:System Database=system.mdw;", ;

"admin", "" ,NIL)

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=.mdb;" + ;

"Jet OLEDB:Database Password=MyDbPassword;", ;

"admin", "" ,NIL)

OLE DB Provider for SQL Server

For Standard Security:

oConn:Open("Provider=sqloledb;" + ;

"Network Library=DBMSSOCN;" + ;

"Data Source=robert;" + ;

"Initial Catalog=pubs;" + ;

"User Id=sa;" + ;

"Password=;",NIL,NIL,NIL)

For a Trusted Connection security:

oConn:Open("Provider=sqloledb;" + ;

"Network Library=DBMSSOCN;" + ;

"Data Source=robert;" + ;

"Initial Catalog=pubs;" + ;

"Trusted_Connection=yes;",NIL,NIL,NIL)

To Prompt user for username and password:

oConn:Provider = "sqloledb"

oConn:Properties:[Item,"Prompt"]:Value := adPromptAlways

oConn:Open("Network Library=DBMSSOCN;" + ;

"Data Source=robert;" + ;

"Initial Catalog=pubs;"

Note: "Data Source" is the name of the server the SQL Server is located

Note: "Network Library=DBMSSOCN" tells OLE DB to use TCP/IP rather than Named Pipes.

OLE DB Provider for Oracle

oConn:Open("Provider=msdaora;" + ;

"Data Source=OracleServer.world;" + ;

"User Id=sa;" + ;

"Password=;" ,NIL,NIL,NIL)

MS Remote - SQL Server

If you want to use an ODBC DSN on the remote machine:

oConn:Open("Provider=MS Remote;" + ;

"Remote Server=http://robert;" + ;

"Remote Provider=MSDASQL;" + ;

"Network Library=DBMSSOCN;" + ;

"DSN=Pubs;" + ;

"Uid=sa;" + ;

"Pwd=;",NIL,NIL,NIL)

If you want to use an OLE DB Provider on the remote machine:

oConn:Open("Provider=MS Remote;" + ;

"Remote Server=http://robert;" + ;

"Remote Provider=SQLOLEDB;" + ;

"Network Library=DBMSSOCN;" + ;

"Data Source=robert;" + ;

"Initial Catalog=pubs;" + ;

"User ID=sa;" + ;

"Password=;",NIL,NIL,NIL)