connecting to sybase with ado in vba

Posted on 2003-03-10
Medium Priority
Last Modified: 2011-04-14
I'm trying to figure out how to connect to sybase with ado. So far I haven't been able to find many good resources online, as most tutorials seem to be for Access databases. I'm using Sybase ASE 12.x, and vba for Excel 2000. I've got the ADO 2.7 library. I know my driver and OLE DB provider. I also know the db name, port, host. But I keep getting error when trying to create and access the recordset.  

Dim conn
Set conn = CreateObject("ADODB.Connection")

conn.Provider = "the sybase provider"
conn.Open "Driver={the driver};" & _
          "Database=dbName;" & _
           "Srvr=someServer;" & _
           "Uid=someID;" & _

Set rs = CreateObject("ADODB.recordset")
rs.Open "select userID from table1, conn"

For Each x In rs.fields
   MsgBox x

I dont think I set up the Open connection correctly, I'm not even sure what kind of string is supposed to go in there (what are all the connection properties that need to be set). Is there even an ADO reference library anywhere online??
Thanks in advance for any insights provided.
Question by:otiemot
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

Accepted Solution

cmurugavel earned 120 total points
ID: 8163956

Follow the below coding, it should work since it's working on my production system :

'database variables
Dim dbSAP As New ADODB.Connection
Dim rsSAP As New ADODB.Recordset
Dim sProc As String
Public wsSAP As Worksheet

    sPass = "passwd"
    sUserId = "login"
    sDataSourceName = "database name"
Sub ConnectToDatabase()
    'set logon details:
    Debug.Print "Connecting to database"
    On Error GoTo Login_Error
    If dbSAP.State = 0 Then
        dbSAP.Open sDataSourceName, sUserId, sPass, 0
    End If

    Exit Sub
     MsgBox "Could not connect to the database. Please contact the System Administrator."
End Sub

LVL 29

Expert Comment

ID: 11208918

Do you still need help with this, or can it be closed?

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Summer 2017 Scholarship Winners have been announced!
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month10 days, 21 hours left to enroll

770 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