Microsoft SQL Server ODBC Driver connection strings

SQL Server 2000

Using a non-standard port

If your SQL Server listens on a non-default port you can specify that using the servername,xxxx syntax (note the comma, it's not a colon).

Driver={SQL Server};Server=myServerName,myPortNumber;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

The default SQL Server port is 1433 and there is no need to specify that in the connection string.

Prompt for username and password

This one is a bit tricky. First you need to set the connection object's Prompt property to adPromptAlways. Then use the connection string to connect to the database.

oConn.Properties("Prompt") = adPromptAlways

oConn.Open "Driver={SQL Server};Server=myServerAddress;Database=myDataBase;"

SQL Server 7.0

Using a non-standard port

If your SQL Server listens on a non-default port you can specify that using the servername,xxxx syntax (note the comma, it's not a colon).

Driver={SQL Server};Server=myServerName,myPortNumber;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

The default SQL Server port is 1433 and there is no need to specify that in the connection string.

Prompt for username and password

This one is a bit tricky. First you need to set the connection object's Prompt property to adPromptAlways. Then use the connection string to connect to the database.

oConn.Properties("Prompt") = adPromptAlways

oConn.Open "Driver={SQL Server};Server=myServerAddress;Database=myDataBase;"