Sauron wrote ...

I -know- I'm missing something here...

Try this:

1. In Excel, paste the following code into a standard module:

Public Function BuildConnString()
Dim oDLink As Object
Set oDLink = CreateObject("DataLinks")
On Error Resume Next
BuildConnString = oDLink.PromptNew
On Error GoTo 0
End Function

2. In the VBE Immediate Window, type

? BuildConnString

and hit enter to run the code.

3. The 'Data Link Properties' dialog should show.

4. On the 'Provider' tab, choose Microsoft OLE DB Provider for SQL
Server

5. Hit the 'Next' button

6. In the 'Select or enter a server name' doprdown, choose your server
from the list (I'll choose MYSERVER).

7. For 'Enter information to log on to the server', choose 'Use a
specific user name and password', and enter the details (I'll enter
'sa' with a blank password, natch <g).

8. Choose 'Select the database on the server' and select the database
in the dropdown list (I'll choose 'pubs').

9. Hit the 'Test Connection' button (I see a message, 'Test connection
succeeded' and I dismiss the message).

10. Hit OK to accept the details entered into the 'Data Link
Properties' dialog.

11. The corresponding connection string should now appear in the
Immediate Window. I get:

Provider=SQLOLEDB.1;
Persist Security Info=False;
User ID=sa;
Initial Catalog=pubs;
Data Source=MYSERVER

HTH,
Jamie.

--