Error -2147467259 ???

[Elysium]

I have a command button in Excel that sends data from a spreadsheet to our SQL Server. I have tested this over and over on my machine where I developed it and I have had no problems. My environment is such:

01) Windows XP
02) Office XP
03) IBM T23 Laptop

I use the following references:

01)Visual Basic for Applications
02)Microsoft Excel 10.0 Object Library
03)OLE Automation
04)Microsoft Forms 2.0 Object Library
05)Microsoft Office 10.0 Object Library
06)Microsoft ActiveX Data Objects 2.0 Library

Now, the systems that I am getting an error on use basically the same references, but the difference is the version of the Excel Library and the Office Library. They are both 8.0.

After some research on the web, the error number represents a number of possible problems, but I think it has something to do with the connection syntax I use. Here it is:

'Open the connection
oConn.Open "Provider=sqloledb;" & _
"Data Source=[IP Address];" & _
"Initial Catalog=[DB Name];" & _
"User Id=[UID];" & _
"Password=[PWD]"

The actual error message is a little cryptic, but it basically says this: ConnectionOpen (CreateFile). This is where it fails.

Any ideas?

RS

 

[redgate]

This may not be of any help but I think the previous versions of Office may have a problen with your connection method.

I would try

a) using Microsoft ActiveX Data Objects 2.6 Library rather than 2.0 and install the MDAC 2.6 updates on all the work stations you are trying to work from.

b) if it is not to much of a probelm try useing an ODBC connection instead.

Good luck.

 

[Elysium]

I tried to connect through ODBC and now I am receiving a different error:

"Client unable to establish connection"

Also, I can't have these people update their MDAC because of corporate restrictions and the fact that the sales people don't have admin rights to the PC.

RS

 

[Elysium]

Okay, I went through the process of adding a System DSN to our SQL Server on a machine where I was getting the connection error. Once I did this, I could communicate just fine. Now, I went back to one of the other problem machines and it still doesn't work, even with going through the process of creating a system DSN. Any ideas?

RS