?
Solved

ODBC for beginners!

Posted on 2003-03-13
8
Medium Priority
?
676 Views
Last Modified: 2013-12-25
Hi,
I am trying to access a database and i want to do it through ODBC.
But i can't find any web pages that give a step by step introduction to it.
The program that i am making is to read the cells and then output them to a new file, so i will not be using controls, it all has to be done programically.
can anyone point me in the right direction?

Thanks
RobertParkinson
0
Comment
Question by:RobertParkinson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 1

Expert Comment

by:meyerc74
ID: 8127596
if i am understanding you correctly you actually want to write out the code.  to do so you would create an "adodb.connection" object.  something like this:

dim lconnString
lconnString = Provider='sqloledb';Data Source=[serverName or ip];uid=[sql userName];pwd=[sql pswd]
dbc = createObject("adodb.connection")
dbc.connectionString = lconnString
dbc.open

after that you could execute a query using
     dbc.execute( select * from [tableName]
0
 
LVL 1

Expert Comment

by:meyerc74
ID: 8127611
noticed a couple syntax problems after i posted that.

the value of lconnString should have quotes around it.

and

dbc.execute needs a ) after the query

sorry
0
 

Author Comment

by:RobertParkinson
ID: 8127661
Hu meyerc74,
Looks interesting but not what i am after.
i am trying to use just ODBC and from what i can find on the help there are function's from the recordset object to access individual feilds, and i was wondering how i would set up the connection to the database so i can access that function.

Thanks
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:bukko
ID: 8129361

What type of database is it? (e.g. Access, SQL Server, etc)
Granted we are talking ODBC, but that just relates to the middleware. What method of client data access are you familiar with, i.e.: DAO, RDO, ADO, etc.

Regards

bukko
0
 
LVL 8

Expert Comment

by:bukko
ID: 8129412

Just a thought...

You do know that you can access individual recordset fields without using ODBC?
ODBC is primarily for accessing different sources of data from a common environment.

Regards

bukko
0
 

Author Comment

by:RobertParkinson
ID: 8157974
Hi bukko,

I am trying to access different sources of data from my program, at first it will be excel and access but i will then be adding orcale or sql server or might even be postgres, have not decide yet.
As regard to method of client data access, which ever one is the most portable, as i have no experence with any of them. This is my first effort at ODBC, all that matters is that it can be do with a GUI as i need to run it as a dll.
all i need to know is how to set up the connection once i have that i am sure i can figure out how to do the rest.

Thanks
RobertParkinson
0
 
LVL 8

Accepted Solution

by:
bukko earned 200 total points
ID: 8162704

ok.
First step is to create the ODBC datasource. Do you need help with this?
Second, the best db communication method is ADO (IMHO), so I'll take you through the basics.
Also, in your VB project you'll need to add a reference to ADO (Project/References/...). Do you need help with this?

1) Create and open a connection to the database

' Create a connection object
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection

' Define a connection string
Dim strConn As String
strConn = "DSN=MyDatasource;" ' That's all you should need, unless there is login info you need to provide.

' Open connection using connection string
objConn.Open strConn

2) Now create a query using our open connection

' Declare a recordset and a field object
Dim objRS As ADODB.Recordset
Dim objField As ADODB.Field
' SQL query string
Dim strSQL As String
strSQL = "SELECT * FROM table"
' Send our query to the database using our open connection, and return a recordset
Set objRS = objConn.Execute( strSQL )

' Did our query return any rows? (EOF=End Of File)
If objRS.EOF Then
  ' No it didn't
  Debug.Print "No rows returned."
Else
  ' Yes, it did
  ' Loop through rows in recordset
  Do Until objRS.EOF
    ' Now loop through fields in row
    ' (if we knew how many there were, we could just
    ' use objRS(0), objRS(1), objRS(2), etc.)
    For Each objField In objRS.Fields
      Debug.Print objField.Name & " = " & objField.Value;
    Next
    Debug.Print
    ' Move to next recordset row, or we'll be looping forever!
    objRS.MoveNext
  Loop
  ' Close the recordset
  objRS.Close
End If
' Tidy up recordset
Set objRS = Nothing
' Close connection
objConn.Close
' Tidy up connection
Set objConn = Nothing


Usually I would declare a connection object with much broader scope and use a single connection for the duration of an application. This is more efficient than the way I've done it here.
Also, if you are using a database which requires user authentication, you will need to add UID and PASS to the connection string, e.g.:
strConn = "DSN=MyDatasource;UID=bukko;PASS=qwq43;"

I hope this helps. If you need any more help, just ask.

Regards

bukko

0
 

Author Comment

by:RobertParkinson
ID: 8165288
Thanks bukko,
that was just what i needed, i didn't know about the sql string though which will come in very handy.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month7 days, 20 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question