Solved

ADODC connections

Posted on 2000-03-12
11
438 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:tdove
[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
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 6

Expert Comment

by:Marine
ID: 2609575
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.
0
 
LVL 6

Expert Comment

by:Marine
ID: 2609576
i meant it as answer.
0
 

Author Comment

by:tdove
ID: 2609597
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)

0
Independent Software Vendors: 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!

 

Author Comment

by:tdove
ID: 2609637
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)

0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2609645
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
0
 

Author Comment

by:tdove
ID: 2609667
Adjusted points to 200
0
 

Author Comment

by:tdove
ID: 2609668
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.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2609718
«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---")


0
 
LVL 18

Expert Comment

by:mdougan
ID: 2609753
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.
0
 

Author Comment

by:tdove
ID: 2609788
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.



0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 200 total points
ID: 2609846
Your ini file can contain something like:
[ENVIRONMENT]
Environment=Eric

[BD]
ConnectionString=PROVIDER=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=D:\Projets VB\Concept S2i\SITQ\Sitq-bd\SITQ.mdb;


In your application, you can use:
..ConnectionString = ReadFromINI("BD", "ConnectionString", "---ERROR---")
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

761 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