Solved

Accessing Data Quickly

Posted on 2004-08-04
10
277 Views
Last Modified: 2013-12-25
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
Comment
Question by:kevman123
10 Comments
 
LVL 5

Expert Comment

by:fadloun
ID: 11718132
helloooo

use SaveSettings , GetSettings functions

regards
0
 
LVL 5

Expert Comment

by:fadloun
ID: 11718188

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
 

Author Comment

by:kevman123
ID: 11719276
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
 
LVL 15

Accepted Solution

by:
SRigney earned 500 total points
ID: 11719888
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
 

Author Comment

by:kevman123
ID: 11727275
Ok- let me give this a try
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:kevman123
ID: 11727482
Ok- let me give this a try
0
 
LVL 1

Expert Comment

by:shendr8086
ID: 11728788
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
 

Author Comment

by:kevman123
ID: 11728832
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
 
LVL 5

Expert Comment

by:fadloun
ID: 11732061
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
 

Author Comment

by:kevman123
ID: 11735881
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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

24 Experts available now in Live!

Get 1:1 Help Now