tdove
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
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
i meant it as answer.
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)
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)
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)
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)
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.OL EDB.4.0;Da ta 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
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.OL
.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
ASKER
Adjusted points to 200
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? 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(ps trSection, 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---")
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(ps
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.OL EDB.4.0;Da ta Source=C:\MyDir\My.mdb;Per sist 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 "WritePrivateProfileString A" (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(strRet urn, intSize)
' gIniLoc = UCase$(Trim$(Left$(strRetu rn, 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(st rSection, strKey, strDefault, strReturn, intSize, gIniLoc & PROFILE_FILE)
' I set a global variable with the info
CONNECTION_NAME = UCase$(Trim$(Left$(strRetu rn, 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(strSe ction, 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.
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.OL
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"
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 "WritePrivateProfileString
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(strRet
' gIniLoc = UCase$(Trim$(Left$(strRetu
' I create an INI file with a section called [ODBC]
strSection = "ODBC"
strKey = "DSNNAME"
strDefault = CONNECTION_NAME_DEFAULT
strReturn = String(255, 0)
nReturn = GetPrivateProfileString(st
' I set a global variable with the info
CONNECTION_NAME = UCase$(Trim$(Left$(strRetu
' 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(strSe
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(
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.