DSNLess Connection: VB 6 + SQL Server 2005

[Dököll]

Hiya Fans!

I hit a brick wall. I was attempting to make a DSNLess connection I seemed to have Goofed somewhere:

Private Sub SQLServer_Click()
Dim MyWorkspace As DAO.Workspace
Set MyWorkspace = CreateWorkspace("", "admin", "", dbUseODBC)
Dim MyConnection As DAO.Connection
Set MyConnection = MyWorkspace.OpenConnection("", , , "ODBC;DATABASE=FirstSQLDB")
Dim rec_set As DAO.Recordset
Dim MSSQL As String
MSSQL = "SELECT * FROM Email WHERE EmailAddress ='" & Text1(0).Text & "'"""
Set rec_set = MyWorkspace.OpenRecordset(MSSQL)
Do While Not rec_set.EOF  'this function will keep searching for fields matching each textbox
   'MsgBox ("got here")
        Text1(0).Text = rec_set.fields("EmailAddress")
        Text1(1).Text = rec_set.fields("IPAddress")
        Text1(2).Text = rec_set.fields("DateTimeStamp")
        Text1(3).Text = rec_set.fields("EmailID")       
   rec_set.MoveNext
   Loop
   MyWorkspace.Close
   rec_set.Close
 
 

Trying to grab data out of SQL Server database using DAO with ODBC connection, new to ODBC, have made one fancy connection in the past, forgot it all.

I get a pop up requesting I add DSN name or other...

What do you see?

In a bit!

Dököll

[Killer42]

To be honest, Dököll, I think msquared, NeoPa and the other gurus in the Access forum are likely to be more helpful on this one.

[QVeen72]

Hi Dököll

ODBC Connection string for DAO is something like this ::

"ODBC;DSN=MYODBCNAME;UID=MYNAME;PWD=MYPASSWORD;DBQ =DATABASENAME;"

Not very sure, if you can connect SQL2005 using DAO in VB6..
use ADO, instead of DAO..

Regards
Veena

[Dököll]

Hello, Guys!

Funny you mentioned Killer42, got a similar post out there on this and msquared has come to aid, waiting to hear from her on a bit of info I stumbled upon. Thanks for your input. Truth is Veena and msquared are right on target, so thank you also, Veena.

Picked up some books at the library, seem to lead where you're at. Seems to be stearing to ADO instead also. I hope to nail it good with ODBC though:-)

Will tell you what comes out of it...

In a bit guys, thanks!

By the way, Killer42, looks like you got your avatar back. Good!

Dököll

[Killer42]

Yeah, I figured I would never find the time to update that radar one so went back to something more interesting.

[QVeen72]

Hi DoKoll,

Try This M$ Example

Regards
Veena

[debasisdas]

try the use the following sample code

This is for DSN less connection to Oracle using RDO.

Dim RS As rdoResultset
Dim Cn As rdoConnection
Dim En As rdoEnvironment
Dim Conn As String
 
Private Sub Form_Load()
Set En = rdoEnvironments(0)
Conn = "UID=SCOTT;PWD=TIGER;" _
   & "DATABASE=ORCL;" _
   & "SERVER=ORCL;" _
   & "DRIVER={ORACLE IN ORADB10G_HOME1};DSN='';"
Set Cn = En.OpenConnection(dsName:="", _
   Prompt:=rdDriverNoPrompt, Connect:=Conn)
Set RS = Cn.OpenResultset("SELECT * FROM EMP")
Call DISP
End Sub
 

[lotus18]

This would be helpful : ). Keep up the good work experts!

Rey Sean

[Dököll]

This would be helpful : ). Keep up the good work experts!

Rey Sean

Super appreciate it, truly...

Sorry to have been away from this post. I will try your examples. I believe msquared said should be too far from VBA. Will try and see:-)

What outpouring support guys, thanks!!!

Dököll