On 5 Oct 2003 23:12:24 -0700 in comp.databases.ms-access,
dr*********@hotmail.com (James) wrote:
Hello group:
I guessing there is no way to create an OLEDB linked table in Access
to SQL Server, am I correct? Basically, I've currently migrated one of
my Access database tables to SQL Server, and they are linked via an
ODBC connection. I'm am attempting to make the logon to SQL server
transparent so the users are never prompted to logon. I can easily
create an ADO connection object in a procedure to open a connection
with the proper credentials, and it works ok. However, when users open
the database and the main form opens, the underlying datasource is the
SQL server table, and at that point it prompts them to logon. Is there
anyone for me to accomplish what I want without having to create a
user, system, or file DSN? All users will be connecting via a common
login using SQL Server security; they will not be using a personal
logon id.
What I do is late bind an ADO object to test the credentials, then if
that function returns true then that's the credentials I stamp onto
all my linked tables' .Connect property.
Function CanOpenSQLDbLB(pstrServer As String, pstrDb As String,
pstrUser As String, pstrPassword As String, Optional pfReportError As
Boolean = True) As Boolean
' Error Trapped: 04/02/2003 09:50:00 Admin
On Error GoTo CanOpenSQLDbLB_Err
' try late binding
Dim objConn As Object
Dim strConn As String
Dim strError As String, lngErr As Long
Const cstrSQLErr = "[Microsoft][ODBC SQL Server Driver][SQL
Server]"
Set objConn = CreateObject("ADODB.Connection")
strConn = strConn & "DRIVER=SQL Server"
strConn = strConn & ";SERVER=" & pstrServer
strConn = strConn & ";APP=" & Application.Name
strConn = strConn & ";WSID=AWorkstation"
strConn = strConn & ";DATABASE=" & pstrDb
objConn.Open strConn, pstrUser, pstrPassword
' Q'PLAGH
CanOpenSQLDbLB = True
CanOpenSQLDbLB_Exit:
On Error Resume Next
objConn.Close
Set objConn = Nothing
Exit Function
CanOpenSQLDbLB_Err:
lngErr = Err.Number
strError = Err.Description
'MsgBox strError
If InStr(1, strError, cstrSQLErr) Then
strError = "Error reported by server" & vbCr & vbCr &
Replace(strError, cstrSQLErr, "")
End If
'Debug.Print strError
Select Case lngErr
Case Else
If pfReportError Then
MsgBox strError, 16, "Error #" & Err & " Attempting to
open server database"
End If
End Select
Resume CanOpenSQLDbLB_Exit
End Function
--
A)bort, R)etry, I)nfluence with large hammer.
