Connecting to SQL Server using non-default port

[Icarus70]

Hi,

I'm trying to set up Access 2002 to administer a SQL Server database on my web host. I understand that I need to create an Access Project, and set the Data Link Properties to connect to the server. However, no matter how I've tried setting the parameters in the properties dialog, I keep getting the following error:

"Test connection failed because of an error in initializing provider. [DBMSSOCN]General network error. Check your network documentation."

I've scoured the MS online resources, and found several references to related issues, none of which has helped. I suspect the problem is related to the SQL Server using a non-default port (2577 instead of 1433) but I am unsure how to specify this in the properties dialog. I've been trying to use the IPort for the Server Name. Is there something else that I'm missing? I also tried using CLICONFG.EXE to set the alias for my database, then use the alias as the server name, but still no go.

FYI, I was able to set up an ODBC system DSN for the database, with no trouble at all -- the setup process for that includes a place to specify the port, of course. I can actually link to the SQL Server using that DSN within Access -- but as I understand it, this will not allow me to define the database, I can only link to existing tables that way.

Any help you can provide will be GREATLY appreciated!

Thanks,

-- David

 

[Icarus70]

Hey, I answered my own question, but thought I should post the results here in case anybody else comes across the same situation.

Rather than specify IPort in the Server Name, I had to specify IP,Port instead. Also, under the "All" tab of the dialog box, I changed the "Data Source" value to match (IP,Port).

Once that was set, everything magically worked.

BTW, I inferred that answer from an online reference (not on the MS site) for establishing an ODBC ConnectString to SQL Server within ASP. Nowhere (and I mean *NOWHERE*) was there any useful description of the options for this dialog box in any of the related help topics for Access 2002, local, online or otherwise. :-(

Anyhow, hope this helps someone else after all that!
-- David