• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 682
  • Last Modified:

ODBC for beginners!

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
RobertParkinson
Asked:
RobertParkinson
  • 3
  • 3
  • 2
1 Solution
 
meyerc74Commented:
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
 
meyerc74Commented:
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
 
RobertParkinsonAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
bukkoCommented:

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
 
bukkoCommented:

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
 
RobertParkinsonAuthor Commented:
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
 
bukkoCommented:

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
 
RobertParkinsonAuthor Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now