Access97 -> SQL Server - ODE tools

[Rebecca]

I've posted this in the middle of an older thread but it seems to have
gone unnoticed. I apologize for repeating myself.

We are converting an existing Access97 split database to an Access front
end with a SQL Server back end so that its users, who live all over the
state, can all use it without mailing the back end back and forth
throughout the year.

I am trying to set up the installation package with the ODE tools so
that the users can install the front end on their machines without
having to manually set the registry keys.

Here's my special trouble spot: the connection has to be made to
an SQL Server "named instance". These run on non-default port numbers;
I know the fixed port number and the server has an internet DNS
hostname. I've used cliconfg.exe running on the remote machine to
manually establish a working connection via TCP/IP by entering the
hostname and port number into the appropriate controls. This builds a
"server alias" on the client so that the ODBC Data Source can connect.
I've observed the registry keys this adds, and I found only two:

HKEY_LOCAL_MACH INE\Software\Mi crosoft\MSSQLSe rver\Client\Con nectTo
contains a <server alias> key with value "DBMSSOCN,<host name>,<port#>"

HKEY_LOCAL_MACH INE\Software\Mi crosoft\MSSQLSe rver\Client\TDS
contains a <server alias> key with value "7.0"
{it's SQL 2000, though - should I worry?)

I then use this <server alias> in place of the <server name> in the
ODBC.INI keys. I'm getting 99.44% of the connection effect. On the
positive side, the "SQL Server ODBC Data Source Test" sequence on such
clients PASSES!- the ODBC data source believes it exists, and I believe
it since it rejected bad passwords and allowed only good passwords -
which only the server could have told it. I'm assuming that this test
sequence also verified that the network library version numbers are
compatible. It reported them so it must have considered them as test
criteria.

Now if the .mdb or .mde file would work I probably wouldn't be posting.

It doesn't. When I run the .mde file, the SQL Server login dialogue
takes 30 seconds after I enter the password and then coughs up an error
string from the Socket library Connect() saying that the SQL Server
could not be found.

Reviewing I see the Linked Table has a Connect string:
"ODBC;DSN=<odbc source>;..." Which names my validated data source,
and also gives the right database name (same code works on the LAN).

The ODBC Data Source passes its connection test. There is no encryption
specified for the SQL server traffic. Do you pros have any idea why the
full client won't connect?

I'd dearly like to ship installation CDs that make these Registry keys
(and then clean them up when you uninstall). I believe they should
work. They don't.

The only small diff I've noted is that when I bring up cliconfg.exe on
a client whose keys were built by setup.exe, I don't see TCP/IP as
an enabled protocol for the SQL Network Client. The ODBC connection
test could only have used TCP/IP. It is happy, so why not Access 97?
The firewall lets both TCP and UDP get through on the port# in question.

The one time I got a good connection front to back was in early
experiments. I'd tried using "hostname:port# \SQLServerInsta nceName" as
the ODBC SQL Server name. It didn't know what to make of this. So I
used cliconfg to make an alias whose spelling exactly matched that
string. It worked! So I went home and wrote tidier looking aliases into
my keys and came back only to find egg on my face. Does cliconfg.exe do
something the key settings alone cannot do? If so, is there a command
line way to get cliconfg to do this during an installation sequence?

Thanks for any hand holding you can offer.

Greg (& Rebecca)