By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,946 Members | 745 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,946 IT Pros & Developers. It's quick & easy.

DSNLess Connection: VB 6 + SQL Server 2005

Expert 100+
P: 2,364
Hiya Fans!

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub SQLServer_Click()
  2. Dim MyWorkspace As DAO.Workspace
  3. Set MyWorkspace = CreateWorkspace("", "admin", "", dbUseODBC)
  4. Dim MyConnection As DAO.Connection
  5. Set MyConnection = MyWorkspace.OpenConnection("", , , "ODBC;DATABASE=FirstSQLDB")
  6. Dim rec_set As DAO.Recordset
  7. Dim MSSQL As String
  8. MSSQL = "SELECT * FROM Email WHERE EmailAddress ='" & Text1(0).Text & "'"""
  9. Set rec_set = MyWorkspace.OpenRecordset(MSSQL)
  10. Do While Not rec_set.EOF  'this function will keep searching for fields matching each textbox
  11.    'MsgBox ("got here")
  12.         Text1(0).Text = rec_set.fields("EmailAddress")
  13.         Text1(1).Text = rec_set.fields("IPAddress")
  14.         Text1(2).Text = rec_set.fields("DateTimeStamp")
  15.         Text1(3).Text = rec_set.fields("EmailID")       
  16.    rec_set.MoveNext
  17.    Loop
  18.    MyWorkspace.Close
  19.    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!

Jan 13 '08 #1
Share this Question
Share on Google+
8 Replies

Expert 5K+
P: 8,434
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.
Jan 15 '08 #2

Expert 100+
P: 1,445
Hi Dököll

ODBC Connection string for DAO is something like this ::


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

Jan 15 '08 #3

Expert 100+
P: 2,364
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!

Jan 22 '08 #4

Expert 5K+
P: 8,434
Yeah, I figured I would never find the time to update that radar one so went back to something more interesting.
Jan 22 '08 #5

Expert 100+
P: 1,445
Hi DoKoll,

Try This M$ Example

Jan 22 '08 #6

Expert 5K+
P: 8,127
try the use the following sample code

This is for DSN less connection to Oracle using RDO.

Expand|Select|Wrap|Line Numbers
  1. Dim RS As rdoResultset
  2. Dim Cn As rdoConnection
  3. Dim En As rdoEnvironment
  4. Dim Conn As String
  6. Private Sub Form_Load()
  7. Set En = rdoEnvironments(0)
  8. Conn = "UID=SCOTT;PWD=TIGER;" _
  9.    & "DATABASE=ORCL;" _
  10.    & "SERVER=ORCL;" _
  11.    & "DRIVER={ORACLE IN ORADB10G_HOME1};DSN='';"
  12. Set Cn = En.OpenConnection(dsName:="", _
  13.    Prompt:=rdDriverNoPrompt, Connect:=Conn)
  14. Set RS = Cn.OpenResultset("SELECT * FROM EMP")
  15. Call DISP
  16. End Sub
Jan 22 '08 #7

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

Rey Sean
Jan 22 '08 #8

Expert 100+
P: 2,364
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!!!

Jan 28 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.