Solved

ADODC connections

Posted on 2000-03-12
11
403 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
  • 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
 

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 69

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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 69

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 69

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now