To Prompt user for username and passwordoConn.Properties("Prompt") = adPromptAlways oConn.Open "Driver={SQL Server};" & _ "Server=MyServerName;" & _ "DataBase=myDatabaseName"
To connect to SQL Server running on the same computeroConn.Open "Driver={SQL Server};" & _ "Server=(local);" & _ "Database=myDatabaseName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword"
To connect to SQL Server running on a remote computer (via an IP address)oConn.Open "Driver={SQL Server};" & _ "Server=xxx.xxx.xxx.xxx;" & _ "Address=xxx.xxx.xxx.xxx,1433;" & _ "Network=DBMSSOCN;" & _ "Database=myDatabaseName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" Where: - xxx.xxx.xxx.xxx is an IP address - 1433 is the default port number for SQL Server. - "Network=DBMSSOCN" tells ODBC to use TCP/IP rather than Named Pipes (Q238949)
Declare Function SQLAllocEnv Lib "odbc32.dll" (phenv&) As Integer Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal henv&, phdbc&) As Integer Declare Function SQLAllocStmt Lib "odbc32.dll" (ByVal hdbc&, phstmt&) AsInteger Declare Function SQLConnect Lib "odbc32.dll" (ByVal hdbc&, ByVal szDSN$,ByVal cbDSN%, ByVal szUID$, ByVal cbUID%, ByVal szAuthStr$, ByVal cbAuthStr%) As Integer Declare Function SQLColAttributesString Lib "odbc32.dll" Alias "SQLColAttributes" (ByVal hstmt&, ByVal icol%, ByVal fDescType%, ByVal rgbDesc As String, ByVal cbDescMax%, pcbDesc%, pfDesc&) As Integer Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal hdbc&) As Integer Declare Function SQLExecDirect Lib "odbc32.dll" (ByVal hstmt&, ByVal szSqlStr$, ByVal cbSqlStr&) As Integer Declare Function SQLFetch Lib "odbc32.dll" (ByVal hstmt&) As Integer Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal hdbc&) As Integer Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal henv&) As Integer Declare Function SQLFreeStmt Lib "odbc32.dll" (ByVal hstmt&, ByVal fOption%) As Integer Declare Function SQLGetData Lib "odbc32.dll" (ByVal hstmt&, ByVal icol%,ByVal fCType%, ByVal rgbValue As String, ByVal cbValueMax&, pcbValue&) As Integer Declare Function SQLNumResultCols Lib "odbc32.dll" (ByVal hstmt&, pccol%) As Integer
For Standard Security:
Conn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\somepath\mydb.mdb;" & _
"Uid=Admin;" & _
"Pwd=;"
If you are using a Workgroup (System database):
Conn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\somepath\mydb.mdb;" & _
"SystemDB=c:\somepath\mydb.mdw;", _
"admin", ""
If MDB is located on a network share:
Conn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=\\myServer\myShare\myPath\myDb.mdb;" ODBC Driver for SQL Server
For Standard SecurityoConn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
For Trusted Connection securityoConn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"Database=myDatabaseName;" & _
"Uid=;" & _
"Pwd="
' Or
oConn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"Database=myDatabaseName;" & _
"Trusted_Connection=yes"
To Prompt user for username and passwordoConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"DataBase=myDatabaseName"
To connect to SQL Server running on the same computeroConn.Open "Driver={SQL Server};" & _
"Server=(local);" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
To connect to SQL Server running on a remote computer (via an IP address)oConn.Open "Driver={SQL Server};" & _
"Server=xxx.xxx.xxx.xxx;" & _
"Address=xxx.xxx.xxx.xxx,1433;" & _
"Network=DBMSSOCN;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
Where:
- xxx.xxx.xxx.xxx is an IP address
- 1433 is the default port number for SQL Server.
- "Network=DBMSSOCN" tells ODBC to use TCP/IP rather than Named
Pipes (Q238949)
Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal henv&, phdbc&) As Integer
Declare Function SQLAllocStmt Lib "odbc32.dll" (ByVal hdbc&, phstmt&) AsInteger
Declare Function SQLConnect Lib "odbc32.dll" (ByVal hdbc&, ByVal szDSN$,ByVal cbDSN%, ByVal szUID$, ByVal cbUID%, ByVal szAuthStr$, ByVal cbAuthStr%) As Integer
Declare Function SQLColAttributesString Lib "odbc32.dll" Alias "SQLColAttributes" (ByVal hstmt&, ByVal icol%, ByVal fDescType%, ByVal rgbDesc As String, ByVal cbDescMax%, pcbDesc%, pfDesc&) As Integer
Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal hdbc&) As Integer
Declare Function SQLExecDirect Lib "odbc32.dll" (ByVal hstmt&, ByVal szSqlStr$, ByVal cbSqlStr&) As Integer
Declare Function SQLFetch Lib "odbc32.dll" (ByVal hstmt&) As Integer
Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal hdbc&) As Integer
Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal henv&) As Integer
Declare Function SQLFreeStmt Lib "odbc32.dll" (ByVal hstmt&, ByVal fOption%) As Integer
Declare Function SQLGetData Lib "odbc32.dll" (ByVal hstmt&, ByVal icol%,ByVal fCType%, ByVal rgbValue As String, ByVal cbValueMax&, pcbValue&) As Integer
Declare Function SQLNumResultCols Lib "odbc32.dll" (ByVal hstmt&, pccol%) As Integer
"Uid=Admin;" & _
"Pwd=;"