EternalKnight

I am having a bizarre problem and I was just hoping that someone could give me a suggestion. Here goes:

I am running Microsoft's sql server on my home computer. At the office it is pretty simple to connect.

ADODB.Open _
"Driver=SQL Server;Server=(IP address);Database=(database);User ID=sa;Password=(password)"
[Edited because the forum removed my original enclosing thingies]
When I am on my home computer (the one with SQL Server on it) this code doesn't work. It says the server is unavailable or invalid user/pass. If I use local host, it works fine. Now this is the wierd part. If my friend uses the same code I do at the office, he can't connect either. He has the latest data access components so I think that is out. Any ideas at all?

Whom shall I fear?

anhmytran

1- ADODB is VB Reserved Word.
2- You should have been using the ADO Connection Object
3- You should have been using it Connection String property.
4- Your Connection String is incorrect.

The correct syntax is:

Dim yourCnn As ADODB.Connection
Set yourCnn = New ADODB.Connection

Dim strCnn As String
strCnn = "Provider=SQLOLEDB;Driver={SQL Server};"
strCnn = strCnn & "Server=YourSQLServerName;"
strCnn = strCnn & "Database=YourTableName;"
strCnn = strCnn & "uid=sa;pswd=;"

yourCnn.ConnectionString = strCnn

Most of the cases, when the user name is "sa" you may obmit its password.
I am surprised that is is identical as the code in OnLine Help.

I learn by heart the above code, and I have been applied it sucessfully
in several different SQL Server systems.
So, be careful not to use "sa" with null pwd in your SQL Server.
Otherwise, I may hark your SQL Server database.

AnhMy_Tran

EternalKnight

A) I purposefully did not post all of my code, only the most relevant part, the connection string. Since I stated that the program works just fine on my home computer and at the office, I obviously have been using the ADO Connection Object. If you are familiar with it, you know that you can specify the connection string along with the .Open statement. Same with recordsets.

B)My connection string is correct, at least to my knowledge. Considering it would be impossible for me to connect to my databases if it was incorrect.

The reason I edited my code was so that people could quickly realize I was using the ADO Object model. My real code includes the prefix ADO and the suffix DB. It makes for very quick and easy code reading. I have successfully used Dim ADODB as ADODB.Connection and then used that connection to retrieve recordsets.

I am not new to the ADO Object model. What I am new to is SQL Server and it's settings. I believe my problem lies with the user settings on the SQL Server and I was hoping someone could help me with that.

Whom shall I fear?

anhmytran

Open your SQL Server, and get to Database, User, Security
and assign UID and PSWD, with the OWNER previledge.
(This is at the bottom of the list of objects in the TreeView)

After that, goto Database, and Table, User, then check the
OWNER in the proper table.
(This is the branch above the Security one in SQL Server Tree.)

I do not have DBA role, and the above is just what I remember.
It may not accurate or correct, but when you see the TreeView
of the SQL Server, you will realize what I am talking about.
I have encountered this error before, and I know how to fix it.

The point is that you should do the job twice as if different tasks.
Otherwise, the proper user's role may not be checked, and your
UID-PSWD combination wouldn't not work.

Good luck

AnhMy_Tran

EternalKnight

Thanks for your suggestions :-) I poked my head around in there some more, created a new user login and voila... Now my home machine and office machine can access the database. My friend's computer cannot, but he may not have the needed data components installed. We shall see this weekend.

Whom shall I fear?

anhmytran

The following is the exact action to take.
I am sorry for not remembering it exactly.

Open SQL Server Enterprise Manager

Click SQL Server Group
Click Your Server Name

Click Security (Second Node from the bottom)
Click Logins
Right Click a UserName, Select Properties
Check Table in Database Access Tab,
Specify PassWord and Select TableName in General Tab.

Click Databases Node (first Node from the top)
Click the proper table
Click the Users
Right Click the UserName and check the db_owner

Good luck.

AnhMy_Tran