Peculiar Query

[ria_arora]

Dear Friends,

I have Module called ConnectDatabase and in this module we written all database related functions. We have couple of databases and all these database details we have defined in below table.

Format of the table (tbl_Supp_Databases) is below

DatabaseCodeDatabaseNameDatabasePathDatabasePasswordConnectDB_StringBADB TOOL.mdbY:\MIS Database ConnectDatabase.ConnectBaDatabase()BAUUtilities DB.mdbY:\MIS Database²¢xṲBAU Utilities DB.mdbÛГConnectDatabase.ConnectBauDatabase()TOSSUtilities TOSS DB.mdbC:\\MyDoc\MS Access\ALL DB³Ï…UTOSS Utilities DB.mdb̤ÛГConnectDatabase.ConnectTossDatabase()… so on… so on… so on… so on… so on



ConnectDB_String contains ModuleName [ConnectDatabase] + Function Name [ConnectBaDatabase()]. I'm trying to write generic code to check if database is available and password is correct. Below is the code:

I have written below code to extract ConnectDB_String value and
strSelectSQL = "SELECT ConnectDB_String FROM tbl_Supp_Databases where ConnectDB_String <> """" "
Set rsTmp = gsSecuredDatabase.OpenRecordset(strSelectSQL)
If Not rsTmp.EOF And rsTmp.BOF = False Then
Do While Not rsTmp.EOF
'Check the connectivity for MS Access Database
If rsTmp!ConnectDB_String() = False Then
iOk = MsgBox("Unable to open BAC Database", vbCritical, "BAC Database not found")
End If
rsTmp.MoveNext
Loop
End If

rsTmp!ConnectDB_String() is returning the value ConnectDatabase.ConnectBaDatabase() but this function is not called I think coz it's treated as a string. How to convert this string to call ConnectBaDatabase() function which is under ConnectDatabase Module.

Thanks
Ria

 

[Guus2005]

You might want to add some code tags, punctuations and/or spaces. Because you are not making much sense.

I'll try...

You are retrieving a single value from a table:

"SELECT ConnectDB_String FROM tbl_Supp_Databases where ConnectDB_String <> """" "

Why don't you use a dlookup?

strConnectionString = dlookup("ConnectDB_String", "tbl_Supp_Databases", "len(ConnectDB_String) > 0")

or you could get the connectionstring from a table which was connected by using the tabledef.connect property.

HTH

 

[ria_arora]

I got one work around for the above problem but that's not good solution.

To check the connection for each database I'm following code:

If ConnectBacDatabase.ConnectBacDatabase() = False Then
iOk = MsgBox("Unable to open BAC Database", vbCritical, "BAC Database not found")
End If

Same code I have used for all 15 different databases. I don't want to keep changing this part of the code whenever we have new database or we remove database. That's why I'm trying to write generic code to select the database from the tables using below SQL:

SELECT DatabaseCode, ConnectDB_String FROM tbl_Supp_Databases where ConnectDB_String <> ""

If I don't want to check the connictivity to particular database then I put blank in ConnectDB_String and if I want to add new database then I'll add in tbl_Supp_Databases table.

Below is the format of the table:

DatabaseCode DatabaseName DatabasePath DatabasePassword ConnectDB_String

DatabaseCode e.g. BAC, BACU, CNP, TOSS, BOSS etc.
DatabaseName e.g. BAC.mdb, BACU.mdb, CnP.mdb, TOSS.mdb, BOSS.mdb etc.
DatabasePath Y:\DBToss etc.
DatabasePassword If anypassword
ConnectDB_String ConnectDatabase.ConnectBacDatabase(), ConnectDatabase.ConnectBauDatabase() etc.

I have written the below code to check the connectivity:

strSelectSQL = "SELECT DatabaseCode, ConnectDB_String FROM tbl_Supp_Databases where ConnectDB_String <> """" "
Set rsTmp = gsSecuredDatabase.OpenRecordset(strSelectSQL)
If Not rsTmp.EOF And rsTmp.BOF = False Then
Do While Not rsTmp.EOF
If rsTmp!ConnectDB_String = False Then
iOk = MsgBox("Unable to open " & rsTmp!DatabaseCode & " Database", vbCritical, "Database Error")
End If
rsTmp.MoveNext
Loop
End If

Value retured by rsTmp!ConnectDB_String is ConnectDatabase.ConnectCnPDatabase() here ConnectDatabase is a modulename and ConnectCnPDatabase() is a Function. Using breakpoint I found this function is not called at all. May be because the value returned is a string. Do I need to convert this into something so this function is called?

Thanks a lot in advance for the help

Regards
Ria

 

[Guus2005]

If you are using modules, you don't need to preceed the functionname with the name of the module. The module is not an object. If on the other hand the module is a class then you are right more or less.

What type of database are you connecting to?
If it is Access, ever tried this:

select * from Table1 in c:\temp\database.mdb

Enjoy!

 

[ria_arora]

If don't use Module name then I'll get the following error:

Comple error: "Expected variable or procedure, not module."

If you use below code then function is called:

    If ConnectCnpDatabase.ConnectCnpDatabase() = False Then
        iOk = MsgBox("Unable to open CnP Utilities Database", vbCritical, "CnP Utilities Database not found")
    End If

Code for ConnectCnpDatabase

'Function is used to connect Secured ACCESS Database and extract CnP database information and assign True value to global variable
Public Function ConnectCnpDatabase() As Boolean
    Dim sCnPDatabaseName As String
    Dim sDatabasePassword As String
    
    On Error GoTo ErrConnectDB
    ConnectCnpDatabase = False
    sCnPDatabaseName = ExtractDatabaseDetails.GetDatabasePath(gsCnPDatabaseID) & "\" & ExtractDatabaseDetails.GetDatabaseName(gsCnPDatabaseID)
    sCnPDatabasePassword = GetDatabasePassword(gsCnPDatabaseID)
    
    ' Create Microsoft Jet Workspace object.
    Set gCurWS = DBEngine.Workspaces(0)
    Set gsCnPDatabase = gCurWS.OpenDatabase(sCnPDatabaseName, False, False, "MS Access;PWD=" & sCnPDatabasePassword)
    ConnectCnpDatabase = True
    gsCnPDatabaseOpened = True  'Assign TRUE once MS Access Secured Database is Initialised
    
    Exit Function
ErrConnectDB:
    gsDBErr = Err.Description
End Function

And when I'm using below code then above funcation is not called:

    strSelectSQL = "SELECT DatabaseCode, ConnectDB_String FROM tbl_Supp_Databases where ConnectDB_String <>  """" "
    Set rsTmp = gsSecuredDatabase.OpenRecordset(strSelectSQL)
    If Not rsTmp.EOF And rsTmp.BOF = False Then
        Do While Not rsTmp.EOF
 '           strDBString = "ConnectDatabase." & rsTmp!ConnectDB_String & "()"
            'Check the connectivity for BAC MS Access Database
            'If ConnectDatabase.ConnectBacDatabase() = False Then
 
[COLOR=red][B]'Here value returned for [COLOR=red]rsTmp!ConnectDB_String is [/COLOR]ConnectCnpDatabase.ConnectCnpDatabase()
[/B][/COLOR]
            If rsTmp!ConnectDB_String = False Then
'            If strDBString = False Then
                iOk = MsgBox("Unable to open " & rsTmp!DatabaseCode & " Database", vbCritical, "Database Error")
'               cmdLogin.Visible = False
'               Exit Sub
            End If
        rsTmp.MoveNext
        Loop
    End If

 

[Guus2005]

Then you have created a class and not a module. The symbol for a module is 5 colored squares. The symbol for a class is a grey filles square with white edges and three colored smaller squares.

Working with Classes compared to modules is slightly different. A class is an object. The functions and procedures are called methods. A class can have properties which are variables within the class. Books are written about the subject.

You have to determine what you want to use. Classes or modules. Mu suggestion would be create modules and copy and paste all code to modules.
You can loose the class name prefix and life is getting a little easier.

Perhaps you can post a sample database?

HTH

 

[ria_arora]

in frmLogin

Private Sub Form_Load()
 
    strSelectSQL = "SELECT DatabaseCode, ConnectDB_String FROM tbl_Supp_Databases where ConnectDB_String <>  """" "
    Set rsTmp = gsSecuredDatabase.OpenRecordset(strSelectSQL)
    If Not rsTmp.EOF And rsTmp.BOF = False Then
        Do While Not rsTmp.EOF
            'Check the connectivity for BAC MS Access Database
            If rsTmp!ConnectDB_String() = False Then
                iOk = MsgBox("Unable to open " & rsTmp!DatabaseCode & " Database", vbCritical, "Database Error")
            End If
        rsTmp.MoveNext
        Loop
    End If

Value retured by rsTmp!ConnectDB_String is ConnectDatabase.ConnectCnPDatabase() here ConnectDatabase is a modulename and ConnectCnPDatabase() is a Function. Using breakpoint I found this function is not called at all. May be because the value returned is a string. Do I need to convert this into something so this function is called?

Please find here attached code. You can set the break point at Private Sub Form_Load(). It should throw the message "Unable to open BACU Database"

You can copy all database files in C:\CnPDB

Press Shift Key and then open the database to open the code.


Thanks and regards
Ria

 

[Guus2005]

You have send in quite a lot of databases, few of which are empty. Not sure where to look for a frmLogin form. The frmLogin is found had much more code than you describe, or did you remove some lines?
Which is the right database?

 

[ria_arora]

Code is in CnP Utilities20110211.mdb

As other database contains huge data so I created empty databases just for reference.

Main Class is:
frmLogin

Routine is:
Private Sub Form_Load()

Issue:

Below code will extract / fetch the value DatabaseCode and ConnectDB_String from tbl_Supp_Databases table (Database Name: CnPSystemDB.mdb and Table Name: tbl_Supp_Databases).

Values for ConnectDB_String are
ConnectDatabase.ConnectBacDatabase()
ConnectDatabase.ConnectBauDatabase()
ConnectDatabase.ConnectCnPDatabase()

ConnectDatabase is defined as a Module in CnP Utilities20110211.mdb database and ConnectBacDatabase(), ConnectCnPDatabase() are functions of this module.

I was expecting
If rsTmp!ConnectDB_String() = False Then

which is after fetching the data will have a value like below:

If ConnectDatabase.ConnectCnPDatabase() = False Then

should call function ConnectCnPDatabase() in ConnectDatabase module. But this function is not called.

    strSelectSQL = "SELECT DatabaseCode, ConnectDB_String FROM tbl_Supp_Databases where ConnectDB_String <>  """" "
    Set rsTmp = gsSecuredDatabase.OpenRecordset(strSelectSQL)
    If Not rsTmp.EOF And rsTmp.BOF = False Then
        Do While Not rsTmp.EOF
 '           strDBString = "ConnectDatabase." & rsTmp!ConnectDB_String & "()"
            'Check the connectivity for BAC MS Access Database
            'If ConnectDatabase.ConnectBacDatabase() = False Then
            If rsTmp!ConnectDB_String() = False Then
'            If strDBString = False Then
                iOk = MsgBox("Unable to open " & rsTmp!DatabaseCode & " Database", vbCritical, "Database Error")
'               cmdLogin.Visible = False
'               Exit Sub
            End If
        rsTmp.MoveNext
        Loop
    End If

Please let me know what is wrong?

Thanks a lot for the help.

Regards
Ria

 

[Guus2005]

The code is not run because it is never called.
The only place where i can find the ConnectCnPDatabase function is in the frmLogin Onload event.

... 'a lot of code here...

'    'Check the connectivity for CnP MS Access Database
'    If ConnectDatabase.ConnectCnpDatabase() = False Then
'        iOk = MsgBox("Unable to open CnP Utilities Database", vbCritical, "CnP Utilities Database not found")
''        cmdLogin.Visible = False
''        Exit Sub
'    End If
'
End Sub

The code is not run because the code is commented out. Using the '

But perhaps i am looking at an older version of your database.

HTH

 

[ria_arora]

I commented out because it was not working. If you take out comments and put a breakpoint to test. It'll not call that function.

Thanks
Ria

 

[Guus2005]

If you put a breakpoint in the beginning of the procedure you can step through the code using the F8 key. Use ctrl-F8 to step over functions and shift-F8 to let the run run to where your cursor is.

Let me know what you find.

 

[ria_arora]

Dear Friends,

Still same issue. Function is not called at all in below code.

    strSelectSQL = "SELECT DatabaseCode, ConnectDB_String FROM tbl_Supp_Databases where ConnectDB_String <>  """" "
    Set rsTmp = gsSecuredDatabase.OpenRecordset(strSelectSQL)
    If Not rsTmp.EOF And rsTmp.BOF = False Then
        Do While Not rsTmp.EOF
            'Check the connectivity for BAC MS Access Database
            If rsTmp!ConnectDB_String = False Then
                iOk = MsgBox("Unable to open " & rsTmp!DatabaseCode & " Database", vbCritical, "Database Error")
            End If
        rsTmp.MoveNext
        Loop
    End If[\code]

 

[Guus2005]

When to code you mention is not reached using the method i described should give you a clue as to why that code was not reached.

When you open a recordset EOF is false and BOF is true.
This test:

If Not rsTmp.EOF And rsTmp.BOF = False Then

will always be false when opening a recordset.
If you want to test if the recordset is empty try

If rsTmp.EOF then

or

If Not rsTmp.recordcount = 0 then

HTH

 

[ria_arora]

Dear Friend,

Below statement does not have any issue. That is working fine.

If Not rsTmp.EOF And rsTmp.BOF = False Then

Please see comments in below code which is not working / called.

    strSelectSQL = "SELECT DatabaseCode, ConnectDB_String FROM tbl_Supp_Databases where ConnectDB_String <>  """" " [COLOR=magenta]'Working[/COLOR]
    Set rsTmp = gsSecuredDatabase.OpenRecordset(strSelectSQL) [COLOR=#ff00ff]'Working[/COLOR]
    If Not rsTmp.EOF And rsTmp.BOF = False Then [COLOR=#ff00ff]'Working[/COLOR]
        Do While Not rsTmp.EOF [COLOR=#ff00ff]'Working[/COLOR]
            'Check the connectivity for BAC MS Access Database
            If rsTmp!ConnectDB_String = False Then [COLOR=#ff00ff][COLOR=red]'Not Calling the function[/COLOR] :mad:[/COLOR]
                iOk = MsgBox("Unable to open " & rsTmp!DatabaseCode & " Database", vbCritical, "Database Error")
            End If
        rsTmp.MoveNext [COLOR=#ff00ff]'Working[/COLOR]
        Loop [COLOR=#ff00ff]'Working[/COLOR]
    End If[\code]

Thanks
Ria

 

[Guus2005]

There is no function in

If rsTmp!ConnectDB_String = False Then 'Not Calling the function

rsTmp is a recordset.
ConnectDB_String is a field in that recordset. ConnectDB_String is a field in the tbl_Supp_Databases table.
It is a string and you are checking for a boolean variable. A boolean is True or False.
If you are checking for zero-length try

If Len(rsTmp!ConnectDB_String) = 0 Then

This is a bogus test because the select query shows that only records are selected where that field must be filled.

Perhaps we nailed the problem now?

 

[ria_arora]

Values returned by result set rsTmp!ConnectDB_String are:
ConnectDatabase.ConnectBacDatabase()
ConnectDatabase.ConnectBauDatabase()
ConnectDatabase.ConnectCnPDatabase()

So

[B]If rsTmp!ConnectDB_String() = False Then[/B]

will look like after returning the value

[B]If ConnectDatabase.ConnectCnPDatabase() = False Then[/B]

But above value is string so I want to convert this into function so that defined function is called. In other words returned value should call function ConnectCnPDatabase() in ConnectDatabase module. But this function is not called.

ConnectDatabase is defined as a Module in CnP Utilities20110211.mdb database and ConnectBacDatabase(), ConnectCnPDatabase() are functions of this module.

Regards
Ria

 

[Guus2005]

I think i get what you mean. Finally...

rsTmp!ConnectDB_String will return the string "ConnectDatabase.ConnectBacDatabase()" after which you want the result of the function ConnectDatabase.ConnectBacDatabase(). Correct?

You could try combinations of the EVAL() statement. Something like

If Eval(rsTmp!ConnectDB_String) = False Then

But i am not sure if that will work.

What will work is the following:

dim blnRetVal as boolean
Select case rsTmp!ConnectDB_String
case "ConnectDatabase.ConnectBacDatabase()"
    blnRetVal = ConnectDatabase.ConnectBacDatabase()
case "ConnectDatabase.ConnectBauDatabase()"
    blnRetVal = ConnectDatabase.ConnectBauDatabase()
case "ConnectDatabase.ConnectCnPDatabase()"
    blnRetVal = ConnectDatabase.ConnectCnPDatabase()
end select

If blnRetVal = False then 'If not blnRetVal Then
...

I very much hope this helps.

 

[ria_arora]

Great... You got the point. Eval is not working but second work aroud is working fine.

Thanks a lot.

Regards
Ria