Connecting to SQL Server 2000 w/ VB6

[highrock]

Anyone know how to connect to SQL Server 2000 using Visual Basic 6?

[vass0922]

Use ADO
Either via code or the ADO control

If you want more info, you have to be more specific what you want to do...

Check around google with 'ado vb6' and I'm sure you'll get plenty of info
If you have specific questions we'll be here.

[highrock]

'form level variables
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset


Private Sub Form_Load()
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Set oConn = New ADODB.Connection
Set oRS = New ADODB.Recordset

oConn.Open "Provider=sqloledb;" & _
"Data Source=testcaelus;" & _
"Initial Catalog=cmstest;" & _
"Uid=sa;" & _
"Pwd="

End Sub

-----------
I get an error: -2147467259(80004005)
unspecified error

occurs on the oConn.Open section of code.

[vass0922]

You haven't initialized the connection object

Easiest way to do it is to dim your objects with New
Dim oConn As New ADODB.Connection

Or before you the open you can put

Set oConn = New ADODB.Connection

Its one or the other, not both
Putting it later can save some resources as it doesn't initilalize it until its required to be used. Otherwise the OS reserves the resources for it right away and it wastes resources until its used.

and yes you'll need to do that for bothof your recordsets as well

The 2nd connection I see you have declared should only be required IF its to a 2nd database. You can have multiple recordsets and command objects on one connection (actually highly suggested)

[omalleytrading]

Actually, yes, he did initialize the connection object. It's the 3rd line of the Form_Load

For one thing, you probably shouldn't do anything important in the form load. Put it in the Form Activate, or better yet in a Main subroutine.

For another, it's an EXTREMELY bad idea to have an sa account without a password and to use it in your code. Create a new account with only the permissions needed for your program. Saftely first!

Try this as your connection string:

Const CONNECTION_STRING = "Provider=sqloledb;" & _
"Data Source=testcaelus;" & _
"Initial Catalog=cmstest;" & _
"User Id=sa;" & _
"Password="

I'm not sure if the short-hands for userid and password are acceptable.

Also, it appears as though you're using an ODBC id as the Data Source. I've always used the server name there (sql.myserver.com, for example) -- try that.

If you just want to connect by ODBC, you can use something like:

oConn.Open "DSN=testcaelus;UID=sa;PWD="

[vass0922]

Whoops
Didn't see that at all

read too fast, thats what I was reading as his 2nd connection object

[omalleytrading]

Don't worry, Vass -- I didn't see it at first either. I almost clicked away thinking that you had nailed it, but something kept nagging me. Must have seen it subconciously.....

[highrock]

thanks, i'll try it monday time to leave work and relax at home. oh i know the account name is bad but I just need to get a connection to the sql server.

[highrock]

Const CONNECTION_STRING = "Provider=sqloledb;" & _
"Data Source=sql.testcaelus.com;" & _
"Initial Catalog=cmstest;" & _
"User Id=sa;" & _
"Password="
oConn.Open CONNECTION_STRING


I still get same error even with the sql.myserver.com
everything loads up fine until i put in the oConn.Open line.

[Creosote]

Wrong Order:

Dim con1 As New ADODB.Connection
    con1.ConnectionString = "Provider=SQLOLEDB.1;" & _
    "Password=YOUR_PASSWORD;" & _
    "Persist Security Info=True;" & _
    "User ID=sa;" & _
    "Initial Catalog=YOUR_CATALOG;" & _
    "Data Source=YOUR_SERVER_NAME"

BTW, cmstest? not as in casino is it?