Link to home
Start Free TrialLog in
Avatar of tdove
tdoveFlag for United States of America

asked on

ADODC connections

I have hopefully a simple question.

I have a form that has an ADODC1 that connects to a datagrid.  It also has ADODC2,ADODC3,ADODC4 connectiong to 3 seperate data combo boxes.  That seems to me, a lot of connections to the same database (Different tables).  Is there a way to simplify the data connection?

TIA,
Todd
Avatar of Marine
Marine

Ofcourse there is. Tell me the data taht you populate the comboboxes with ? Is the data taken all from one table ? Then when populating just loop though a different fields and populate them.

rs.Movefirst
Do while not rs.Eof
    combo1.additem rs(0)
    combo2.additem rs(1)
    combo3.additem rs(2)    
    rs.movenext
loop

rs(0) and (1) (2) are field names taht you want to display in your comboboxes.
i meant it as answer.
Avatar of tdove

ASKER

The data is in different tables.

I thought of going to just comboboxes and using the AddItem."", but the list could change frequently so I thought it would be better to store the data in tables for easy changes.

Database=expense
datagrid=datagrid1 (shows past entries)
datacombo1=RegCatCombo Table=invoices (List all possible regions)
datacombo2=InvCatCombo Table=InvCat (shows all possible catogories)
datacombo3=InvReaCombo Table=ReaCat(Shows all possible reasons)

Avatar of tdove

ASKER

The data is in different tables.

I thought of going to just comboboxes and using the AddItem."", but the list could change frequently so I thought it would be better to store the data in tables for easy changes.

Database=expense
datagrid=datagrid1 (shows past entries)
datacombo1=RegCatCombo Table=invoices (List all possible regions)
datacombo2=InvCatCombo Table=InvCat (shows all possible catogories)
datacombo3=InvReaCombo Table=ReaCat(Shows all possible reasons)

Avatar of Éric Moreau
You can program a connection like this:
Option Explicit

Private mcn As ADODB.Connection

Private Sub Form_Load()
Dim rst1 As ADODB.Recordset

    'Create a connection
    Set mcn = New ADODB.Connection
    With mcn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Program Files\Microsoft Visual Studio\VB98\Nwind2000.mdb;Persist Security Info=False"
        .Open
    End With
   
    'Feed the data grid
    Set rst1 = New ADODB.Recordset
    With rst1
        .ActiveConnection = mcn
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
        .Open "Select * from Employees"
        Set DataGrid1.DataSource = rst1
        DataGrid1.Refresh
    End With
   
    'Feed one data combo
    Set rst1 = New ADODB.Recordset
    With rst1
        .ActiveConnection = mcn
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
        .Open "Select * from Shippers"
        Set DataCombo1.RowSource = rst1
        DataCombo1.ListField = "CompanyName"
        DataCombo1.Refresh
    End With
   
    'Feed another combo
    Set rst1 = New ADODB.Recordset
    With rst1
        .ActiveConnection = mcn
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
        .Open "Select * from Suppliers"
        Set DataCombo2.RowSource = rst1
        DataCombo2.ListField = "CompanyName"
        DataCombo2.Refresh
    End With
End Sub

Private Sub Form_Unload(Cancel As Integer)
    mcn.Close
    Set mcn = Nothing
End Sub
Avatar of tdove

ASKER

Adjusted points to 200
Avatar of tdove

ASKER

emoreau-

In the above scenerio isn't that actually doing the same thing as the ADODC connections are doing?  If so, are there advantages or disadvantages to each? Also instead of the connection string, is there a way I can have the info in the registry or ini file.

Forgive me all the questions, but I am learning as I go.  I will also raise the point level.
«In the above scenerio isn't that actually doing the same thing as the ADODC connections are doing?»

No. Creating a connection object will use only one connection to database. If you put ADODC on your form, you will have n+1 open connection to your database (where n is the number of ADODC). This is is real advantages when using a per-connection-database (like Oracle, SQL Server, Sybase, ...). Also, since there is less open connections, there is also less resources used on the server and it can go faster!

«Also instead of the connection string, is there a way I can have the info in the registry or ini file.»

It is a very good practice to put your connection string to an INI file. I don't find the registry usable in this case (What do you do the first time the app start and the registry is not set yet? What do you do if one of the parameters change?) I always use a registry (easy workaround for both previous question).

To read from ini file, you can use:

Declare Function GetPrivateProfileString Lib "kernel32" _
                 Alias "GetPrivateProfileStringA" _
                 (ByVal lpApplicationName As String, _
                  ByVal lpKeyName As Any, _
                  ByVal lpDefault As String, _
                  ByVal lpReturnedString As String, _
                  ByVal nSize As Long, _
                  ByVal lpFileName As String _
                 ) As Long

' *****************************************************************
' ***
' *** Lecture de la clef désirée du fichier .INI
' ***
' *****************************************************************
Public Function ReadFromINI(ByVal pstrSection As String, _
                            ByVal pstrKey As String, _
                            ByVal pstrDefault As String _
                           ) As String
Dim lngReturn As Long
Dim strReturn As String

    strReturn = String$(200, 0)
    lngReturn = GetPrivateProfileString(pstrSection, pstrKey, pstrDefault, strReturn, Len(strReturn), GetINIPath)
    ReadFromINI = Left$(strReturn, lngReturn)
End Function


Later in your code, you can call the function like this:
    strTemp = ReadFromINI("BD", "ConnectionString", "---ERROR---")


Here are a few suggestions.  As you suspect, having multiple connections to the database is not a good design.  As emoreau suggest, use the one connection to open up several recordsets.  However, I'd differ slightly from emoreau's suggestion in that I'd have you open, and keep open, multiple recordsets.  That way, you can keep your data bound controls open but still only use one connection.

Option Explicit
Public CN as ADODB.Connection
Public RS1 as ADODB.RecordSet
Public RS2 as ADODB.RecordSet
Public RS3 as ADODB.RecordSet

Private Form_Load()

    'Create a connection
    Set CN = New ADODB.Connection
    CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDir\My.mdb;Persist Security Info=False"
    CN.Open

    'Open the first data grid
    Set RS1 = New ADODB.Recordset
    With RS1
        .ActiveConnection = CN
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
'        .LockType = adLockReadOnly
        .Open "Select * from Employees"
        Set DataGrid1.DataSource = RS2
        DataGrid1.Refresh
    End With

    'Open the Second data grid
    Set RS2 = New ADODB.Recordset
    With RS2
        .ActiveConnection = CN
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
'        .LockType = adLockReadOnly
        .Open "Select * from Companies"
        Set DataGrid2.DataSource = RS2
        DataGrid2.Refresh
    End With
     
    'Open the Second data grid
    Set RS3 = New ADODB.Recordset
    With RS3
        .ActiveConnection = CN
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
'        .LockType = adLockReadOnly
        .Open "Select * from Orders"
        Set DataGrid3.DataSource = RS3
        DataGrid3.Refresh
    End With
End Sub

The difference here is that you're opening 3 different recordsets, and keeping them open, so, if you want to update the tables through the data grid, you'll be able to do so.

I'd forgotten the proper locktype for an updatable dynaset, so I've commented it out above, but you'd probably want to pick an appropriate setting.  Also, if the CursorType is set to include the table (I think it's something like adOpenTable), then as you move through the grid, I believe that you will pick up new rows that other users have added to the table without having to "refresh" the recordset from time to time.  I think that this only works if your underlying database is an MDB file though (at least that used to be the case in VB 3, perhaps ADO doesn't have that limitation).

You have a good idea to store your connect string info in either an INI file or the Registry.  That allows you to change a setting outside of your program to get it to point to different databases.

If you are doing it the old fashioned way, by using INI files, then you should use the Windows API calls of:

GetPrivateProfileString or
GetPrivateProfileInt

and

WritePrivateProfileString

Option Explicit
Const PROFILE_FILE = "MYAPP.INI"

' Note, I think I've changed one of the declares below slightly because
' there was a error in the WINAPI definition they shipped with VB
Declare Function GetWindowsDirectory Lib "kernel32" Alias "GetWindowsDirectoryA" (ByVal lpBuffer As String, ByVal nSize As Long) As Long
Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long
Declare Function GetPrivateProfileInt Lib "kernel32" Alias "GetPrivateProfileIntA" (ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal nDefault As Long, ByVal lpFileName As String) As Long
Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpString As Any, ByVal lpFileName As String) As Long


Sub GetProfiles()
'*************************************************************************************************************************
'   Populates the various Database variables from the INI file
'   Example call:
'       GetProfiles
'*************************************************************************************************************************
Dim sErrors       As String
Dim lError        As Variant
Dim nReturn       As Integer
Dim Pos           As Integer
Dim strTemp       As String
Dim strSection    As String
Dim strKey        As String
Dim strDefault    As String
Dim strReturn     As String * 255
Dim intDefault    As Integer
Dim intSize       As Integer

    On Error GoTo GetProfilesErr
   
    intSize = 255

' if you want the INI file in the application directory
        gIniLoc = App.Path & "\"

' if you want the INI file in the Windows Directory
'       strReturn = String(255, 0)
'       nReturn = GetWindowsDirectory(strReturn, intSize)
'       gIniLoc = UCase$(Trim$(Left$(strReturn, nReturn))) & "\"
   
' I create an INI file with a section called [ODBC]
    strSection = "ODBC"
    strKey = "DSNNAME"
    strDefault = CONNECTION_NAME_DEFAULT
    strReturn = String(255, 0)
    nReturn = GetPrivateProfileString(strSection, strKey, strDefault, strReturn, intSize, gIniLoc & PROFILE_FILE)
' I set a global variable with the info
    CONNECTION_NAME = UCase$(Trim$(Left$(strReturn, nReturn)))
   

' here is an example of using GetPrivateProfileInt to get the value of
' the window's state (maximized, normal etc, saved the last time the
' user exited
    strSection = "USER"
    strKey = "WINDOWSTATE"
    intDefault = WINDOWSTATE_DEFAULT
    gWindowState = GetPrivateProfileInt(strSection, strKey, intDefault, gIniLoc & PROFILE_FILE)
       
GetProfilesExit:
    Exit Sub
GetProfilesErr:
    Screen.MousePointer = DEFAULT
    sErrors = "GetProfiles " & Err.Description
    For Each lError In Errors
        sErrors = sErrors & vbCrLf & lError.Description
    Next
    MsgBox sErrors, vbCritical, Err & ""
    Resume GetProfilesExit

End Sub

Sub WriteUserProfiles()
'*************************************************************************************************************************
'   Populates the various Database variables from the INI file
'   Example call:
'       GetProfiles
'*************************************************************************************************************************
Dim sErrors As String
Dim lError As Variant

Dim nReturn       As Long
Dim strSection    As String
Dim strKey        As String
Dim strReturn     As String

    On Error GoTo WriteUserProfilesErr
   
' the global gWindowState is set everytime the main window is resized
    strSection = "USER"
    strKey = "WINDOWSTATE"
    strReturn = CStr(gWindowState)
    nReturn = WritePrivateProfileString(strSection, strKey, strReturn, gIniLoc & PROFILE_FILE)
     

WriteUserProfilesExit:
    Exit Sub
WriteUserProfilesErr:
    Screen.MousePointer = DEFAULT
    sErrors = "WriteUserProfiles " & Err.Description
    For Each lError In Errors
        sErrors = sErrors & vbCrLf & lError.Description
    Next
    MsgBox sErrors, vbCritical, Err & ""
    Resume WriteUserProfilesExit

End Sub


To store the info in the registry, you can use the GetSetting and SaveSetting VB function calls.  They work much like the GetPrivateProfileString and WritePrivateProfileString listed above, where you give a key name and a key value, and you can specify a default value too.  This will store your info in the HK Current User section of the reg, under Software\VB and VBA something or the other\MyApp\MyKey.
Avatar of tdove

ASKER

I understand why I don't want the 4 ADODC controls.  I think I have that part covered.

I am still a little unclear on the .ini file though. In both examples above, how would I do the ".connectstring=" line to reflect the ini file? (connectstring=MyApp.ini?)

mdougan-  I understand why you would want to keep the rs open, but in my case the datagrid is just for read-only purpose to show what has already been entered into the dbase.



ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial