automatically connect to the default database while opening adp file.

[Indra]

Hi There,

i am using sql server 2000 as a backend and ms access adp as a front
end for our database with merge replication and pull subscriber.

As we have more than 50 user for one database, we write the script
(login script), to update the adp file for all users to the latest
version.

in my adp file i have a botton, when user click it change or connect
to there local database, which is bit inconvience for the user who are
not very good user of computer. I would like to connect to there local
database automatically when adp file open.

Write now i am using the click event procedure to connect to the
database.

wrote function in module:

Function ChangeADPConnection(strServerName As String, strDBName As _
String, Optional strUN As String, Optional strPW As String) As
Boolean
Dim strConnect As String
On Error GoTo EH:
Application.CurrentProject.CloseConnection
'The Provider, Data Source, and Initial Catalog arguments are
required.
strConnect = "Provider=SQLOLEDB.1" & _
";Data Source=" & strServerName & _
";Initial Catalog=" & strDBName
If strUN <> "" Then
strConnect = strConnect & ";user id=" & strUN
If strPW <> "" Then
strConnect = strConnect & ";password=" & strPW
End If
Else 'Try to use integrated security if no username is supplied.
strConnect = strConnect & ";integrated security=SSPI"
End If
Application.CurrentProject.OpenConnection strConnect
ChangeADPConnection = True
Exit Function
EH:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "Connection
Error"
ChangeADPConnection = False
End Function

and call this function from the form as:

Private Sub ChangeDatabase_Click()
Dim bCheckConnection As Boolean
'You must specify the correct parameters for your following server.
'username and password parameters are optional.
bCheckConnection = ChangeADPConnection(Environ("COMPUTERNAME"),
"Ultraline Sub", "UL-Tickets", "neutron")
MsgBox bCheckConnection

End Sub


Its wroking great, but user have to click the button. Is there any way
i can connect automatically without user input.



I try to run code in autoexe macro with this function:

ChangeADPConnection ("cal-itimilsina", "Ultralin Sub", "un", "pw")

which is working where"cal-itimilsina" is the name of the computer. If
i change it with environ("computername"), its not working. As we will
be rolling adp with script its does not work for us if we have to
specify the computer name.

Please let me know if there is any way to solve this problem.

Thanks in advance.

Indra.

 

[Norman Yuan]

You probably could try this:

Make form as the ADP's startup form, and place your db connection code in
the Form_Load event handler.

 

[Indra]

Thanks Norman,

Actually i come to my brain just after i post the massage and i am
using that methode now.

Thanks.

Indra.