[dba-SQLServer]Access 2000 Adp Connection

For prompting a user I like the UDL Applet solution which is what Access is
using when it asks you which databsource you want to connect to (from the
adp).

you'll need the OLEdb32.dll refrenced in your ADP, but other than that it's
a simple

Dim dl As MSDASC.DataLinks
Dim cn As ADODB.Connection

    Set dl = New MSDASC.DataLinks
    Set cn = New ADODB.Connection

    Set cn = dl.PromptNew
    Me.txtConnectionString = Trim(cn.ConnectionString)

    cn.Close
    Set dl = Nothing
    Set cn = Nothing

where me.txtConnectionString is the connection string from the UDL that
connects you to the new data source.  The benefit is that you can re-connect
your ADE to *Any* Sql Server data source, whenever your user wants.

HTHs
-Francisco
http://rcm.netfirms.com
----- Original Message -----
From: "Arthur Fuller" <artful at rogers.com>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Tuesday, February 18, 2003 8:14 PM
Subject: RE: [dba-SQLServer]Access 2000 Adp Connection


I would like to think that the answer is yes, but have not discovered said
portable solution. Where does this list of connections come from? That's the
problem! Best I've come up with is a data-driven list of previous
connections, to which the user can add. But this is not equivalent to
intellisense!

Since I have several dev-boxes, I typically rename them to match the server
owned by the current client. Imperfect solution, I readily admit, but
no-brainer may be. Alternatively, sidestep any autoexec macros &c. and have
at least one box upon which all your dev-software is loaded, so that you can
build an ADE on-site and then distribute that.

Currently I have a need for some more elegant solution to said problem. I
want the app to open with the default connection, and failing said connect,
to prompt the user to select an appropriate db. Possible answers include
previously visited dbs on remote servers, local dbs with a name-pattern, and
new servers containing dbs corresponding to said naming pattern.

I am not at all sure how to provide this functionality. Any suggestions,
algorithms (Al Gore Rhythms) welcomed! Since I am just sketching this out,
you cannot hurt my feelings or my code-pride :-) I'm willing to read the
most unorthodox solutions.

I want my ADP to open on the previously declared database, but also to allow
the user to select any previously-declared compatible database (residing
anywhere on the accessible intranet) or to create a new connection to some
db residing on a hitherto-unknown server. In the absence of said
previously-declared db/server, I want the user to be able to define and
navigate to some new db of interest. (We just bought a company in DesMoines
and want to inhale its data; find me a server, link me to it, and apply said
cubes to said db. Is this possible?

A.

-----Original Message-----
From: dba-sqlserver-admin at databaseadvisors.com
[mailto:dba-sqlserver-admin at databaseadvisors.com] On Behalf Of Myke Myers
Sent: February 18, 2003 2:19 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer]Access 2000 Adp Connection


I am responsible for enhancing a frontend Access 2000 Adp that is used in
two locations by a corporate client.

The server I use for development does NOT have the same name as either of
the two production servers.

Is there any way to build in an "auto detect" feature so it looks for a
server name on start up and selects the appropriate server in the list of
connections?

TIA,
Myke