This is pretty weird......

I have a program, developed in VB6.0. Pretty simple realy, just connects to SQL Server 2000 running on Small Biz Server 2003. Using SQLOLE connection string as follows:

    cnWITS.Open "Provider=SQLOLEDB;" & _
           "Data Source=" & strServer & ";" & _
           "Initial Catalog=" & strDatabase & ";" & _
           "User Id=" & strUserID & ";" & _
           "Password=" & strPassword & ";Persist Security Info=False"

User Id is set to sa in this case so should have full permissions.

Connection works fine, no problems at all. I am then executing the following SQL statement:

    Set rsWITS = cnWITS.Execute("SELECT NYEAR FROM TableYear WHERE CYEAR = '" & Mid$(pstrCustNum, 2, 1) & "'")

Comes back everytime and tells me that the table TableYear can't be found. I know that the table is there as I have seen it... LOL.... and I ran exactly the same query from Query Analyzer on same machine to same server. I can change the SQL statement to the following:

"Select * From sysobjects" and it creates the recordset just fine. Tried this just to see if it would find system tables. And, it does.

Now the realy weird part..... I developed on a stand alone PC. XP Pro, VB 6.0, SQL Server 2000, all local. Had the same problem but found one reference on the internet saying similar problem fixed by resetting SQL Server sa password. Sure enough, I reset password to same as it was and it fixed the problem. It does not, however correct the problem in the server/clinet environment.

Bottom line: Connection works, can't see user tables, can see system tables fine.

ARGH!!!!!! :(

Anyone have any idea on this??? Any help appreciated......

Thanks Folks,

Rick

Oh well, sorry for wasting everyone's time with this.

I did what I guess was the obvious and converted the project to VB.NET/ADO.NET. Now everything runs fine.

Thanks anyway,
Rick