• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

Accessing Data Quickly

Let's see if I can explain what I want here.
I have a program that stores user settings in an access database table. When the program loads, all of the user settings are stored into variables by being read from the database. i.e. Below is how the settings are called- I also included the GetStoreSettings procedure below. I know there must be a faster way to retrieve the settings on a database end instead of opening a recordset... Secondly- is there a way to quesry the database to assign the values to the variables all at once? The variable name is the same as the name stored in the datbase. Let me know if clarification is needed. Thanls in advance!

Public LoadSettings()
    BName = GetStoreSettings("BName")
    BLocation = GetStoreSettings("BLocation")
    BAddress = GetStoreSettings("BAddress")
    BAddress2 = GetStoreSettings("BAddress2")
    BCity = GetStoreSettings("BCity")
   ....And on and on and on
End Sub




Public Function GetStoreSettings(WhatToGet As String) As Variant
On Error GoTo FixIt:


Dim TriedOnce As Boolean
Dim TranDet As New ADODB.Recordset
Dim strSQL As String

TryAgain:
ADOConTransfer = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DatSource & ";Persist Security Info=False"

strSQL = "Select * FROM StoreSettings WHERE (StoreSettings.Key)='" & WhatToGet & "' AND (StoreSettings.Reg)=" & RegNum

TranDet.Open strSQL, ADOConTransfer, adOpenStatic, adLockReadOnly
If TranDet.RecordCount <> 0 Then
    GetStoreSettings = TranDet!value
Else
    GetStoreSettings = CheckForDefault(WhatToGet)
End If
TranDet.Close

FixIt:
If Err <> 0 Then
    If RegNum = "" And TriedOnce = False Then
        RegNum = 1
        TriedOnce = True
        GoTo TryAgain:
    End If
    MsgBox "Error retrieving store setting- Key: " & WhatToGet
End If
End Function
0
kevman123
Asked:
kevman123
1 Solution
 
fadlounCommented:
helloooo

use SaveSettings , GetSettings functions

regards
0
 
fadlounCommented:

give your more explanation from VB object browser

----------------------------SaveSetting----------------------
Sub SaveSetting(AppName As String, Section As String, Key As String, Setting As String)
    Member of VBA.Interaction
    Saves a setting under the specified appname, section and key
-----------------------------------------------------------------

------------------------------GetSetting-------------------------
Function GetSetting(AppName As String, Section As String, Key As String, [Default]) As String
    Member of VBA.Interaction
    Returns a key setting value from an application's entry in the Windows registry
------------------------------------------------------------------
REGARDS
0
 
kevman123Author Commented:
That's what I'm doing! But it saves to a database. If you read the question, I'm not looking for you to tell me how to use the functions I've already created-- I need to know a fast data access method for the Access database and also a way to load all of the variables at once without hitting th database a thousand times.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
SRigneyCommented:
Ok. Calm down.  I think what fadloun is saying is you can write to the registry using the standard SaveSetting function.

What I do to improve performance is I cache the entire properties table one time, then only look within my local recordset.

Dim TranDet As New ADODB.Recordset
Public Function GetStoreSettings(WhatToGet As String) As Variant
On Error GoTo FixIt:

Dim TriedOnce As Boolean
Dim strSQL As String

TryAgain:
ADOConTransfer = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DatSource & ";Persist Security Info=False"

strSQL = "Select * FROM StoreSettings WHERE (StoreSettings.Reg)=" & RegNum

If TranDet.State = adStateClosed Then
   Dim cn As ADODB.Connection
   cn.Open ADOConTransfer
   TranDet.Connection = cn
   TranDet.CursorLocation = adUseClient
   TranDet.Open strSQL, , adOpenStatic, adLockReadOnly
    set TranDet.Connection = nothing
    cn.Close
   ' Now I have a Disconnected recordset and can jump through it anyway that I want.
   ' Note that this recordset is read only and you will have to write separate code to write to the dB.
End If

' Look within my recordset for the value wanted.
TranDet.Filter = "Key=" & WhatToGet
If TranDet.RecordCount > 0 Then
   TranDet.MoveFirst
    GetStoreSettings = TranDet!value
   TranDet.Filter = ""
End If

FixIt:
If Err <> 0 Then
    If RegNum = "" And TriedOnce = False Then
        RegNum = 1
        TriedOnce = True
        GoTo TryAgain:
    End If
    MsgBox "Error retrieving store setting- Key: " & WhatToGet
End If
End Function
0
 
kevman123Author Commented:
Ok- let me give this a try
0
 
kevman123Author Commented:
Ok- let me give this a try
0
 
shendr8086Commented:
You could also do the following in your database call to improve your speed, opening a recordset as static has more overhead than a forward only call, and since this is being repeated over and over should shave may CPU cycles:

TranDet.Open strSQL, ADOConTransfer, adOpenForwardOnly, adLockReadOnly
If Not TranDet.RecordCount.EOF Then
    GetStoreSettings = TranDet!value
Else
    GetStoreSettings = CheckForDefault(WhatToGet)
End If
TranDet.Close


0
 
kevman123Author Commented:
I will probably use something similar, to SRigneys suggestion, but use direct SQL statements instead of looping back and forth through the recordset . Still haven't had a chance to try it yet. Will later today.
0
 
fadlounCommented:
am I late ?
calm down ,,,,CALM DOWN
you are not waiting for me to tell you how to create those functions ,,,,, :D
DEAR
I am not telling you how to create ,,,, I am guiding you to USE functions already exsit in vb ,,,
SaveSetting and GetSetting is faster than storing data into database ,,, coz those functions store your settings into registry ,,,,,,
you told me to read your Q clearly ,,,, and I am telling you to read my answer CAREFULLY.
and remember we are a voulanteers here ,,,

regards
0
 
kevman123Author Commented:
I understand that- but it has to be read from a database not the registry. I would like to eliminate a loop if possible because the variable names are already stored in the database, but I don't think VB offers variable binding to a databse....
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now