Connecting to SQL Server from VB

[astroboy77]

Hi,
I have a SQL Server Express 2005 installation and database on my PC. I want to connect to it from VB6 using DAO (i have to use DAO as the software i'm using is already written in it and i've just upgraded the database from Access to SQL SERVER that it connects to.)

I've tried a few connection variations but keep getting error that the "ODBC CALL FAILED"

I have set up an ODBC DSN also to try and connect through that, but it doesnt matter if the connection is ODBC or just directly to the Server, I just need to get it running.

SQL Server name: .\SQLEXPRESS
SQL Database name: workwise
Username: peter
Password:
ODBC datasource name: workwise

Any help on the connection string would be greatly appreciated.

@@@@@ This is what I've tried so far. @@@@@@

I went to the connectionstrings.com website but I still couldnt get much further with my connection.

I've intentionally left the code so that if the login fails it will show the login box. This gives me a chance to try different connection variables in the form.

When the connect box shows up, it shows my Server options to choose from as:

(local)
(local)
Main
Main

But in SQL Manager, it shows my server being named .\SQLEXPRESS .
So what is my SQL Server named? how do I know which one contains my database residing on it?

I've just started using SQL Server after coming from Access and am somewhat confused here.

I'm currently working with this connection string ;

cnStr = "ODBC;Driver=SQL Server;Server='.\SQLEXPRESS';UID='Peter';PWD=;Database='Workwise'"

'Open database
Set m_DB = OpenDatabase("", False, False, cnStr)

[vb5prgrmr]

It goes something like....

Set daoWS00 = DBEngine.CreateWorkspace("ODBCDirect", "admin", "", dbUseODBC)
Set daoDB00 = daoWS00.OpenDatabase("ODBCDirect", dbDriverNoPrompt, False)
Set daoRs00 = daoDB00.OpenRecordset("Select * From Table1", dbOpenDynamic, dbExecDirect, dbOptimistic)

where "ODBCDirect" is the actual name of the ODBC connection I made via the ODBC Sources dialog

Good Luck

[MarkT]

I made a connectionstring add-in a while back that does a pretty good job. It just brings up a UDL which allows you to select all the connection properties and then allows you to test the connection. When you close the UDL it copies the connection string to clipboard. ConnectionString Add-In

[GaryMazzone]

When SQL Express installs it installs as a named instance of SQL Server. So . means local machine \SQLExpres is the instance name for the SQL Server instance.

Your connection to this would then be Server=.\SQLExpress

[techgnome]

vb5 - that works for access... he's trying to connect to SQL Server, so that won't work.
MArk - that's pretty cool.

Gary beat me to the answer. Use ".\SQLEXPRESS" for the server.

-tg

[vb5prgrmr]

vb5 - that works for access... he's trying to connect to SQL Server, so that won't work.

Oh yes it does my little one. It works just fine using and ODBC connectioin to Microsoft SQL Server 6.5/7.0/2k as I just tested it against mine (granted instead of admin I used sa but it works just fine).

[astroboy77]

I made a connectionstring add-in a while back that does a pretty good job. It just brings up a UDL which allows you to select all the connection properties and then allows you to test the connection. When you close the UDL it copies the connection string to clipboard. ConnectionString Add-In

Thanks for all the advice
Mark that add-in is really awesome.

After a bit of trial and error I got a working connection string, that is it works via the test button:
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=Peter;Initial Catalog=Workwise;Data Source=.\SQLEXPRESS"

I'm still stuck on getting the code to work with it. I tried to use VB5Programmer suggestion I think that is ODBC sample, and the connection string is now not using ODBC.

This is what I got now:

Dim wrkJet As Workspace
Dim cnn As Connection

strConnect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=Peter;Initial Catalog=Workwise;Data Source=.\SQLEXPRESS"

Set wrkJet = CreateWorkspace("", "Admin", "") <--- that runs ok but if i use "Peter" it fails.

Set cnn = wrkJet.OpenConnection("", , False, strConnect) <--- fails with error: Operation is not supported for this type of object .

What am I doing wrong??

[techgnome]

Use either ODBC or don't.... VB5prgmr has now muddied the waters by introducing an older technology... while it MAY work, it's better suited for Access... not SQL Server.

Your best bet may be to follow this link: http://www.vbforums.com/showthread.php?t=337051 and follow the SQL Server turorials.

-tg

[vb5prgrmr]

techgnome,

The OP states...

>I want to connect to it from VB6 using DAO (i have to use DAO as the software i'm using is already written in it and i've just upgraded the database from Access to SQL SERVER that it connects to.)

Strait from the help files...

Sub NewDatabaseCode()

	Dim wrkMain As Workspace
	Dim conPubs As Connection
	Dim dbsPubs As Database
	Dim prpLoop As Property

	' Create ODBCDirect Workspace object instead of Microsoft 
	' Jet Workspace object.
	Set wrkMain = CreateWorkspace("", "admin", "", dbUseODBC)

	' Open Connection object based on information in
	' the connect string.
	Set conPubs = wrkMain.OpenConnection("Publishers", _
abDriverNoPrompt, False, _
		"ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")
	' Assign the Database property to the same object 
	' variable as in the old code.
	Set dbsPubs = conPubs.Database

	' Enumerate the Properties collection of the Database 
	' object. From this point on, the code is the same as the 
	' old example.
	With dbsPubs
		Debug.Print "Database properties for " & _
			.Name & ":"

On Error Resume Next
		For Each prpLoop In .Properties
			If prpLoop.Name = "Connection" Then
				' Property actually returns a Connection object.
				Debug.Print "    Connection[.Name] = " & _
					.Connection.Name
			Else
				Debug.Print "    " & prpLoop.Name & " = " & _
					prpLoop
			End If
		Next prpLoop
		On Error GoTo 0

End With

	dbsPubs.Close
	wrkMain.Close

End Sub

so you would need to change this line...

	Set conPubs = wrkMain.OpenConnection("", dbDriverNoPrompt, False, _
		"ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")

to

	Set conPubs = wrkMain.OpenConnection("", dbDriverNoPrompt, False, _
		"ODBC;DATABASE=Workwise;UID=Peter;PWD=;DSN=Your DSN Name Goes Here")

(or something along those lines)...

and you also may have to change this line also...

	Set wrkMain = CreateWorkspace("", "Peter", "", dbUseODBC)

Which means, that while the utility does just fine for ADO because ADO interprets connection strings and that ADO sits above DAO (i.e. ADO is a wrapper for DAO and thus is/is not an older technology since it is still supported via JET by MS but MS is trying to drop this tech but can't seem to get away from it (see note below)), does not necessarily mean that DAO will support a DNS Less connection string (Sorry I have never tried with DAO). Which means you will need an ODBC DSN.

However, if you can get DAO to work via a DSN Less connection string, I would love to see the code.

Good Luck

NOTE: Intellisence in vb6 is DAO driven. Look hard enough on your system and you will find a database with all those little .(dot) popup words contained within.

[si_the_geek]

Thread moved to Database Development forum (the "VB6" forum is meant for questions which don't fit in more specific forums)

i.e. ADO is a wrapper for DAO and thus is/is not an older technology since it is still supported via JET

ADO is not a wrapper for DAO, it is a complete replacement for it.

DAO is older, and has long been unsupported - for a few years it has officially been "obsolete"!

Both of the above are explained by the VB6 help files, and many other articles.


There are many threads on the forums where a project has needed a re-write to eliminate DAO, due to finding circumstances where it fails (and cannot be fixed).

Given the choice I would recommend leaving DAO behind, but whether that is worthwhile depends on the circumstances.

[techgnome]

vb5 - all I said was use ODBC or don't.

Dim wrkJet As Workspace
Dim cnn As Connection

strConnect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=Peter;Initial Catalog=Workwise;Data Source=.\SQLEXPRESS"

Set wrkJet = CreateWorkspace("", "Admin", "") <--- that runs ok but if i use "Peter" it fails.

Set cnn = wrkJet.OpenConnection("", , False, strConnect) <--- fails with error: Operation is not supported for this type of object .

Right there, the OP is mixing DAO while trying to use OLEDB provider... THAT doesn't work... using DAO means using ODBC provider.... Using the OLEDB provider means moving up to the ADO... which I highly recommend. All I'm saying is that you can't mix technologies like that. Plus, as Si noted here DAO is beyond dead: http://www.vbforums.com/showpost.php...31&postcount=4

All I'm saying though, is use the right technology consistently and don't try to mix them.

-tg

[astroboy77]

Thanks for the help. It took me quite a while to get a working connection string but I have it working and understand whats going on better.

I had to set the Authentication mode for the Sql server to "windows authentication" to get it working. The server name is Main, and my PC name is Peter.

Here it is for anyone else who might have the same problems as me;

[CODE]
Dim cs As String
  
Dim cn As DAO.Database
  
Dim ws As DAO.Workspace
  
Set ws = DBEngine.Workspaces(0)
  
cs = "ODBC;DRIVER=SQL Server;SERVER=Main\SQLEXPRESS;DATABASE=Workwise;APP=Visual Basic" 'UID=Peter;PWD="
 
Set cn = ws.OpenDatabase("Workwise", dbDriverNoPrompt, True, cs)
[/CODE]

When I try to open the recordset using the usual methods as I used when running an Access database, I get errors about invalid data type, but havent figured out why that is.

I tried both of these lines;

Set RS = cn.OpenRecordset("SELECT * FROM EMPLOYEE")

------------

Set RS = cn.OpenRecordset("SELECT * FROM EMPLOYEE", dbOpenDynamic, dbExecDirect, dbOptimistic)

Thanks for any assistance.