Referencing a field name 1

[MissyEd]

Hi

Im creating a function that takes a value, searches for it in a database and returns either the value, if found or an empty string if not found.

Im trying to whittle down my code by creating a single function to search any database / recordset.

The inputs are: database name & location, table name, field name, value to search for and criteria e.g
"c:\database\mydb.mdb", "LOGIN", "USERNAME", "TestName", "WHERE USERNAME = '" & txtValue "'" (txtValue = TestName).

This would search the database c:\database\mydb.mdb for a table called LOGIN. Once the tabledef has been found, a recordset would be opened using the criteria.

At the moment, if it finds the value, it returns the value to search for field e.g fQueryDB=txtValue. What Im trying to acheive is instead of returning the value to search for field, simply pass it the criteria and get in return the resulting value from the field, if any.

Example: I would pass it criteria "WHERE USERNAME = '" & txtSearchValue & "'" (txtSearchValue = "TestName" and set the field as FULLNAME, then get in return the value of the value of FULLNAME in the calling procedure, e.g "The Test User Name".

Any ideas ?
Missy Ed
Looking to exchange ideas and tips on VB and MS Access. Drop me a line: msedbbw@hotmail.com

 

[MichaelRed]

I think this is 'quite ambitious'. Some databases may not provide the same resources that Ms. Access (*.MDB) does (and which you are already referencing). If it were CLEARLY restricted to a specific subset of file/table types I htink it is 'doable', however you will need to find some way to distinguish between tables where the field properties are 'known' in various ways.

Consider, for instance, that I can (actually do a lot of programming in this) access a Pervasive BTrieve file directly from VB, where even the database does not 'know' the field definitions of the file. No where within this system is there any filed definition of the file - except what I have set up within the Class module used to interface with the datbase table. In a general sense, these tables are indistinguishable from a standard fixed width text file. In attempting to deal with the gereral process which you describe, you would need to accomodate a large number of ideosyncratic 'database' definitions, such as this.

If - you are able to settle for the mundane (*.MDB) files, then you realy only need to construct a query based on the database/table/field. You already have identified the [Table Name] and [Field Name] and [Value], so all you need to do is 'declare' the database and table in your code (Dynamically), construct the query, execute it - and check the recordcount is non-zero.

If this accomplishes what you are trying to achieve, let me know and I'll do some work on it. It could be interesting, but I think it is actually fairly simple.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[MissyEd]

Hi Micheal

I've figured out what I want to do. It was quite simple as you said. I just needed to add a field to the parameters. Ive stuck the code on below. Thanks for your reply, maybe when I get stuck again, I'll pop you over an email
If anyone has a better way to do this, please let me know. Im still learning and want to get this down to the most efficient methods. Thanks!

' Function fQueryDatabase
' Inputs: The field name, the database name, the tablename , the
' criteria which is optional
' Outputs: Either an empty variant if not found or the field's data if found
Public Function fQueryDatabase(ByVal strField As String, _
ByVal strDBname As String, _
ByVal strTableName As String, _
ByVal strCriteria As String) _
As Variant
Dim priDBLoc As String, strFound As String
Dim tdfLoop As TableDef, varReturn As Variant, fldLoop As Field

' Cross-check information provided
If strField = "" Or strDBname = "" Or strTableName = "" Then
' Quit early
fQueryDatabase = ""
GoTo exit_fQueryDatabase
End If

' Continue with search
' Find database
priDBLoc = fReadIniFile("File Locations", strDBname, "c:\cashsheet\database\" & strDBname)
strFound = Dir(priDBLoc, vbHidden)

If strFound = "" Then
' Quit early
fQueryDatabase = ""
GoTo exit_fQueryDatabase
End If

' Open database
Dim wrkjet As Workspace, dbTemp As Database, rstTemp As Recordset
Set wrkjet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbTemp = OpenDatabase(priDBLoc, False, False, "MS ACCESS;pwd=cartman!&quot

' Find table
strFound = ""
For Each tdfLoop In dbTemp.TableDefs
If strTableName = tdfLoop.Name Then
strFound = tdfLoop.Name
Debug.Print "Tabledef found: " & tdfLoop.Name
Exit For
End If
Next tdfLoop

If Len(strFound) = 0 Then
' Table not found, quit early
fQueryDatabase = ""
GoTo exit_fQueryDatabase_CloseDB
End If

' Find field
strFound = ""
For Each fldLoop In dbTemp.TableDefs(strTableName).Fields
If strField = fldLoop.Name Then
strFound = fldLoop.Name
Debug.Print "Field found: " & fldLoop.Name
Exit For
End If
Next fldLoop

If Len(strFound) = 0 Then
' Field not found, quit early
fQueryDatabase = ""
GoTo exit_fQueryDatabase_CloseDB
End If

' Open table using criteria if available
If Len(strCriteria) = 0 Then
Debug.Print "SELECT " & strField & " FROM " & strTableName & ";"
Set rstTemp = dbTemp.OpenRecordset _
("SELECT " & strField & " FROM " & strTableName & ";", dbOpenDynaset)
Else
Debug.Print "SELECT " & strField & " FROM " & strTableName & " " & strCriteria & ";"
Set rstTemp = dbTemp.OpenRecordset _
("SELECT " & strField & " FROM " & strTableName & " " & strCriteria & ";", _
dbOpenDynaset)
End If

' Activate records
If Not rstTemp.EOF Then
rstTemp.MoveLast
End If

' Count records
If rstTemp.RecordCount = 0 Then
' The value does not exist
fQueryDatabase = ""
varReturn = ""
Debug.Print "The value does not exist"
Else
rstTemp.MoveFirst
varReturn = rstTemp.Fields(strField)
fQueryDatabase = varReturn
Debug.Print "The value exists: " & varReturn
End If

' Close recordset
rstTemp.Close

exit_fQueryDatabase_CloseDB:
' Close the database
dbTemp.Close
wrkjet.Close

exit_fQueryDatabase:
' Destroy string
strDBname = ""
strTableName = ""
strCriteria = ""
priDBLoc = ""
strFound = ""
Exit Function
End Function



Missy Ed
Looking to exchange ideas and tips on VB and MS Access. Drop me a line: msedbbw@hotmail.com

 

[MichaelRed]

Missy,

This probably needs some modification to get it really right. In particular, the qryFind would need to exist in the data base or be created within the database or handled in some manner which did NOT require it to exist as a permenant object in the data base. Otherwise, it returns the count of the number of occurances of the value in the field specified.

Public Function basFindVal(dbPathName As String, _
                           rsName As String, _
                           FldName As String, _
                           MyVal As Variant) _
                           As Integer
                        
    Dim Wks As Workspace
    Dim dbs As DAO.Database
    Dim qdf As QueryDef
    Dim rst As DAO.Recordset

    Dim strSQL As String

    On Error GoTo ErrExit
    Set Wks = DBEngine.Workspaces(0)
    Set dbs = Wks.OpenDatabase(dbPathName)

    strSQL = "SELECT Count(" & rsName & "." & FldName & ") AS Num "
    strSQL = strSQL & "FROM " & rsName & " "
    strSQL = strSQL & "WHERE (((" & rsName & "." & FldName & ")= " & Chr(34) & MyVal & Chr(34) & "));"
    Set qdf = dbs.QueryDefs("qryFind")
    qdf.SQL = strSQL
    Set rst = dbs.OpenRecordset("qryFind", dbOpenDynaset)
    basFindVal = rst!NUM

    GoTo NormExit
    
ErrExit:
    basFindVal = 0

NormExit:

End Function

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[MichaelRed]

O.K., O.K., O.K., O.K., O.K.

I didn't test it thoroughly - and I should have finished getting rid of the need for the query Object in the database BEFORE posting, but NOW, I think these are "fixed".

It still returns the Count of the items within the field as opposed to the value found in the field however, since we already know what we were looking for, it seems to me to provide all of the functionality requested.

Public Function basFindVal(dbPathName As String, _
                           rsName As String, _
                           FldName As String, _
                           MyVal As Variant) _
                           As Integer
                        
    Dim Wks As Workspace
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Dim strSQL As String

    On Error GoTo ErrExit
    Set Wks = DBEngine.Workspaces(0)
    Set dbs = Wks.OpenDatabase(dbPathName)

    strSQL = "SELECT Count(" & rsName & "." & FldName & ") AS Num "
    strSQL = strSQL & "FROM " & rsName & " "
    strSQL = strSQL & "WHERE (((TRim(Str(" & rsName & "." & FldName & ")))= " & Chr(34) & MyVal & Chr(34) & "));"
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    basFindVal = rst!NUM

    GoTo NormExit
    
ErrExit:
    basFindVal = 0

NormExit:

End Function

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[MissyEd]

Thanks Michael. It works far more effectively. You're a gent

Missy Ed
Looking to exchange ideas and tips on VB and MS Access. Drop me a line: msedbbw@hotmail.com