VB4 Connection to SQL2000

luxcs · June 4th, 2004, 10:46 AM

Hopefully someone can help me here.

I'm trying to write a small VB4 program that connects to an SQL DB on another machine on the network.
I've managed to get another project (Excel VBA to connect to SQL via ADO) working fine. But cannot get this project to work.

Can anyone help me with some tips/pointers as to any extras/addins required or some code examples to get a connection and maybe even running the SQL.

VB4 doesn't like the code below (this is how it was done with VBA). It says "argument not optional" at the highlighted section. How can it be done with VB4?

Code:

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

' Provide the connection string.
Dim strConn As String
'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB; DATA SOURCE=ops-serv;INITIAL CATALOG=testDB; INTEGRATED SECURITY=sspi;"

'Now open the connection.
cnPubs.Open  strConn

Thanks

gbianchi · June 4th, 2004, 10:53 AM

hi there..

i dont know why your code is falling. maybe something with vb4???
all the arguments for open are optionals... and your code looks fine...

can you try something like

Code:

cnpubs.connectionstring = strconn
cnpubs.open

HTH

Gonzalo

luxcs · June 4th, 2004, 11:07 AM

It says "Invalid use of property" for the .ConnectionString section

gbianchi · June 4th, 2004, 11:39 AM

wich references do you have on the project?? are you sure that are you using ado???

HTH

Gonzalo

luxcs · June 5th, 2004, 03:56 AM

All the general stuff, plus Standard OLE Types, Microsoft DAO 3.0 Object Library and Microsoft ActiveX Data Objects 2.6 Library

luxcs · June 7th, 2004, 05:07 AM

Anyone even know of any other sites they can refer me to? I still have had no success with this. What is it I'm missing?

tbriers · June 7th, 2004, 01:05 PM

You can also try and place an ADODC control (ADO Database Control) on your form and right click on that one to get the properties.

There you can create a connection using a wizard and you get the connection string.
After that you can compare the connectionstrings.

Hope it helps

Greetz

Tom.

luxcs · June 8th, 2004, 04:32 AM

connection string it produces is the same - at least I know the string is correct.
still doesn't like the cnPubs.Open strConn

tbriers · June 8th, 2004, 08:32 AM

I may have another suggestion.

When I have to connect to an SQL Server using VB I do not use:
PROVIDER=SQLOLEDB

But i use
PROVIDER=SQLOLEDB.1

Although I use VB6 and to be honnest I do not know what the difference is between the two, it still may be worth a try.

Maybe this one helps.

Regards

Tom.

gbianchi · June 8th, 2004, 09:00 AM

hi there...
another idea..

do you know what is a .udl file??

try to make one and copy the connection string to your app...

HTH

Gonzalo