Forms and ODBC connections

[Guest]

I have a data form using a record source based on a linked table in an Oracle
database.
I would like to have a logon form where users input their username and
password and then to pass them to the data form, in order to avoid the ODBC
logon form.
I was suggested to do something like

Private Sub Form_Open()
Dim conn As New ADODB.Connection
conn.ConnectionString = "Provider=MSDASQL;DSN=hpsic;UID=hpdba;PWD=hpdba"
conn.Properties("PROMPT") = adPromptNever
conn.Open

but it dosen't works: when users open the data form, at first the ODBC logon
form is displayed, and only after that the form open event code is executed.
Has anyone any suggestion?
Thanks,
Marta

 

[Daniel Doyle]

I read about an idea for this on the Internet, I can't remember where,
basically you provide a login box that will execute SQL statement, which
will then pool the connection.
This is a function I've used in the past to initiate a connection to to SQL
Server. I had login form that passed username and password to this function
and the connection string mirrored that of my linked tables. The dummy table
is updated as this is a quick way of connecting rather than doing a select,
so you would also need to create this table and on the server with update
permission.
I was never 100% confident about this method and decided to have users log
into terminal server and then use windows authentication, but it may work
for you.

Dan.

Public Function LoginBackendSQLUser(username As String, Password As String)
_
As Boolean

Dim strCnn As String

On Error GoTo ErrHandler

DoCmd.Hourglass True

strCnn = "[ODBC;DRIVER=SQL
Server;SERVER=193.111.111.111;APP=appname;WSID=;DATABASE=mydatabase;" & _
"uid=" & username & ";pwd=" & Password & "].dummy"

CurrentProject.Connection.Execute "UPDATE " & strCnn & " SET DUMMY = 0"

LoginBackendSQLUser = True

ExitHere:
DoCmd.Hourglass False
Exit Function

ErrHandler:
LoginBackendSQLUser = False
Resume ExitHere

End Function

 

[Guest]

Thank you for your answer but it isn't clear to me the sintax you are using
for the connection string and the update statement.
I am trying with something like
strCnn = "Provider=MSDASQL;DSN=hpsic;UID=hpdba;PWD=hpdba"
CurrentProject.Connection.Execute "update " & strCnn & " dummy set dummy
=0"
but I get an error in the update sintax

(I get other errors if I write
strCnn = "[Provider=MSDASQL;DSN=hpsic;UID=hpdba;PWD=hpdba].dummy")
CurrentProject.Connection.Execute "UPDATE " & strCnn & " SET DUMMY = 0")
Thanks,
Marta

I read about an idea for this on the Internet, I can't remember where,
basically you provide a login box that will execute SQL statement, which
will then pool the connection.
This is a function I've used in the past to initiate a connection to to SQL
Server. I had login form that passed username and password to this function
and the connection string mirrored that of my linked tables. The dummy table
is updated as this is a quick way of connecting rather than doing a select,
so you would also need to create this table and on the server with update
permission.
I was never 100% confident about this method and decided to have users log
into terminal server and then use windows authentication, but it may work
for you.

Dan.

Public Function LoginBackendSQLUser(username As String, Password As String)
_
As Boolean

Dim strCnn As String

On Error GoTo ErrHandler

DoCmd.Hourglass True

strCnn = "[ODBC;DRIVER=SQL
Server;SERVER=193.111.111.111;APP=appname;WSID=;DATABASE=mydatabase;" & _
"uid=" & username & ";pwd=" & Password & "].dummy"

CurrentProject.Connection.Execute "UPDATE " & strCnn & " SET DUMMY = 0"

LoginBackendSQLUser = True

ExitHere:
DoCmd.Hourglass False
Exit Function

ErrHandler:
LoginBackendSQLUser = False
Resume ExitHere

End Function

I have a data form using a record source based on a linked table in an Oracle
database.
I would like to have a logon form where users input their username and
password and then to pass them to the data form, in order to avoid the ODBC
logon form.
I was suggested to do something like

Private Sub Form_Open()
Dim conn As New ADODB.Connection
conn.ConnectionString = "Provider=MSDASQL;DSN=hpsic;UID=hpdba;PWD=hpdba"
conn.Properties("PROMPT") = adPromptNever
conn.Open

but it dosen't works: when users open the data form, at first the ODBC logon
form is displayed, and only after that the form open event code is executed.
Has anyone any suggestion?
Thanks,
Marta