ODBC Querying via VB

[bedowin]

I have an application on a network that is going to be distributed to a handful of clients. There are certain controls within my application (such as a combo box) that have certain values, which must be filled in from a table within a database on the central NT server. I have an ODBC connection setup on that NT to the database.

I can map a drive to that database internally, but what is the best method here? Let's just keep it simple.

I have a table that has two fields (NameID, Name) and I want to use these to populate a cmbBox, where NameID is the Index value for the Name that is listed in the cmbBox for the user to see and select from.

What should I do?

I hope this is simple enough to be provided with some sample code that is basic. I want all users to have read access to this database table at any time.

[jtm7699]

this is what i use to access data on a SQL server(think it is what you want)

i have this sub in a class module but you can put into a Global module

the first part sets up the connection

Public Sub OpenAllappointments(ByVal startdate As Date)
    ' returns appointments for the week that is passed in
    Set cnn1 = New ADODB.Connection
    Dim dteStart As Date
    Dim dteEnd As Date
    Dim strStart As String
    Dim strEnd As String
    dteStart = startdate
    dteEnd = DateAdd("d", 6, startdate)
    cnn1.ConnectionString = "driver={SQL Server};" & _
      "server=NISSRV0;database=HAS"
    cnn1.ConnectionTimeout = 30
    cnn1.Open
    Set rsAllAppointments = New ADODB.Recordset
    strSql = "SELECT Schedule.ASC_ApptDate, Schedule.ASC_ACL_ID, " & _
              "Schedule.ASC_AAU_ID, Schedule.ASC_QtrID, Audiologist.AAU_Initials, " & _
              "Schedule.ASC_Status, Audiologist.AAU_FirstName, Audiologist.AAU_LastName " & _
              "FROM Audiologist INNER JOIN Schedule ON Audiologist.AAU_ID = Schedule.ASC_AAU_ID " & _
              "WHERE schedule.asc_apptdate >= '" & dteStart & "' AND schedule.asc_apptdate <= '" & dteEnd & "'" & _
              " AND (Schedule.ASC_ACL_ID=' ' OR schedule.asc_acl_id IS NULL) " & _
              "AND Schedule.ASC_Status='C'"
    rsAllAppointments.Open strSql, cnn1, adOpenKeyset, adLockOptimistic
End Sub

any questions let me know

[bedowin]

Would this work if the database is an Access database on an NT server? My understanding is that the connection string would only be valid if SQL Server was running on the NT.

Thanks for the help.

[jtm7699]

i would believe so, have not tried it though. As long as the ODBC link is there.

just change the driver

the server

and of course the database

let me know