Connecting to my MS-SQL Database

[yisraelharris]

I have built MS-Access-based websites, and have had no problem establishing a connection to them. Here is typical code:

myConnection = Server.CreateObject("ADODB.Connection")
connectionString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ="Server.MapPath("company.mdb")
myConnection.Open(connectionString)

However, I am currently working on an MS-SQL-based website, and am unable to achieve the connection. I've been using different variations of this (with and without the single quotes, etc.):

myConnection = Server.CreateObject("ADODB.Connection")
connectionString="DRIVER={SQLServer};SERVER='mssql2.myserver.com';UID='mylogin';PWD='d3fbc3';DATABASE='company.mdb'"
myConnection.Open(connectionString)

I get this error message:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

/test.asp, line 6

I do not know what needs to be done. I would be grateful for assistance.

Thank you.

[defanjos]

It is possible the ODBC driver is not installed correctly. I just had the same exact problem yesterday with a mySQL connection - the problem was the ODBC driver. They reinstalled it and all was fine afterwards.

[mattglet]

instead of
DRIVER={SQLServer};

replace with
PROVIDER=SQLOLEDB.1;

see if that helps.

-Matt

[yisraelharris]

This is the reponse I got from another forum, and it works:

connectionString="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=myname;PWD=aeiouy;Initial Catalog=mydatabase;Data Source=mssql2.mycompany.com"

[mattglet]

www*connectionstrings*com

-Matt

[yisraelharris]

Very cool, Matt. Thank you.