Suggestion needed about the functions in a module

[kavya]

Hi all,

I am working on the project which is split to front end and back end databse. There are some public functions in the general module section, which I see are mentioned no where except in the module they are created. My question is since they are not called anywhere will the functions do what they have to when just the database is run, for eg if you run a report or work on a query.
Please help me in this regard.

Thanks a lot in advance

 

[PaulF]

usually the only Objects in the Back End MDB are the tables and they are linked to the Front End MDB. In this case the answer is yes, "EXCEPT" when it has code that will perform a "Seek" on a table. You cannot use "Seek" on a Linked table.

PaulF

 

[kavya]

Hi Paul,

Thanks for your suggestions. There is no seek function in the code, and I dont see the functions are working as they should. Any advice on that.

Thanks a lot

 

[PaulF]

Depends on what the functions do... could you provide an example, and what problem you noticed?

PaulF

 

[MichaelRed]

Kavya,

When doing split db, I almost NEVER use 'Links' to tables, but simply declare the databse/recordset(s) and assign them to the "BackEnd" (e.g. Tables AND QUERIES). This appears to speed up the various queries amd decrease the network traffic - as well as permitting teh use of the "Seek" method.

It is some additional effort fro the database designer/programmer but gives some additional flexability.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[kavya]

Hi Paul,
I have some functions to link the frontend with the backend, and to check whether linking already exists. If not display a message box for the user to input the full path of the backend. If the user inputs the correct information it is not a problem, but when they dont, in the function a warning message has to be displayed saying the path is not correct and backend not found. Instead it brings the userinterface as though everything is correct to run the reports, but after that when you try to run a specific report it gives a unspecified error, not the same error message which the function has to display.

Hope I gave you enough information.

Thanks a lot in advance

 

[PaulF]

post the lines of code that doesn't seem to work

PaulF

 

[kavya]

Hi Paul,

Thanks a lot. I am sending you the copy of the code, the function stepone is the main function from which it calls other functions in the module and this function stepone is triggered by a autoexec macro.

Option Compare Database
Option Explicit
Global bkmBookMarkp As String
Global bkmBookMarks As Long
Global bkmBookMarkc As Long
Global Const GblBackEndDBName = "MasDB02132001.mdb"
Dim fpath As String

Public Function fctnLinkTables()
On Error Resume Next

Dim cdb As Database
Dim tbl As tabledef
Dim tbls As TableDefs
Dim flds As Fields
Dim fld As Field
Dim rst As Recordset

Set rst = CurrentDb().OpenRecordset("TblLinkTheseTables", dbOpenTable)
With rst
.MoveFirst
Do Until .EOF
DoCmd.DeleteObject acTable, rst!TableName
DoEvents
DoCmd.TransferDatabase acLink, "Microsoft Access", fpath & GblBackEndDBName, acTable, !TableName, !TableName
.MoveNext
Loop
End With

Set cdb = CurrentDb
Set tbls = cdb.TableDefs
DoCmd.SetWarnings False

Set tbl = CurrentDb.CreateTableDef("TblLinked&quot
Set flds = tbl.Fields
Set fld = tbl.CreateField("TableName", dbText, 250)
flds.Append fld
tbls.Append tbl
DoEvents
End Function


Public Function XXFindInitialTable()
On Error Resume Next
Dim tbl As tabledef

XXFindInitialTable = False
Set tbl = CurrentDb().TableDefs("TblLinked&quot
If Err = 0 Then
XXFindInitialTable = True
End If

Set tbl = Nothing
End Function


Function PopulateTable_TblLinkTheseTables()
Dim rst As Recordset
Dim tbl As tabledef

Set rst = CurrentDb.OpenRecordset("TblLinkTheseTables", dbOpenTable)
For Each tbl In CurrentDb.TableDefs
If UCase(Left(tbl.Name, 4)) <> &quot;MSYS&quot; And (tbl.Name <> &quot;TblLinkTheseTables&quot; And tbl.Name <> &quot;TblLinked&quot Then
rst.AddNew
rst!TableName = tbl.Name
rst.Update
End If
Next
rst.Close
Set rst = Nothing
Set tbl = Nothing
End Function

Public Function StepOne()
Dim rv As Variant
StepOne = False

If Not XXFindInitialTable() Then
rv = MsgBox(&quot;You need to connect to the Back-End prior to using this application. Continue?&quot;, vbYesNo, &quot;Connect Application&quot
If rv = vbNo Then
DoCmd.Quit
End If
rv = InputBox(&quot;Please enter the (UNC) complete path to the Back-End Application.&quot; & Chr$(13) & Chr$(13) & Chr$(13) & Chr$(13) & &quot;UNC Example: \\<servername>\<share>\&quot;, &quot;Enter Path to File&quot;, &quot;&quot
If rv = &quot;&quot; Then
fpath = CurDir
If Right$(fpath, 1) <> &quot;\&quot; Then
fpath = fpath & &quot;\&quot;
End If
rv = Dir(fpath & GblBackEndDBName, vbNormal)
If rv = &quot;&quot; Then
MsgBox &quot;The Back-End Application cannot be found at the location specified.&quot;, vbCritical, &quot;Terminating Application&quot;
DoCmd.Quit acQuitSaveNone
Else

End If

End If
Else
StepOne = True
Exit Function
End If
StepOne = True

fpath = rv
rv = fctnLinkTables()
End Function


The code checks whether the user has input a '\' or not if not appends it and it should run fine, but it is not doing.
I am helpless in regard may be there is some problem in the code itself. Thanks a lot.

 

[MichaelRed]

Public Function StepOne()

    Dim rv As Variant
    StepOne = False
    Dim MyMsg As String
    Dim MyTitle As String

    If (XXFindInitialTable()) Then
    
        StepOne = True
        Exit Function

    End If

    
    MyMsg = "You need to connect to the Back-End prior to using "
    MyMsg = MyMsg & "this application.  Continue?"
    MyTitle = "Connect Application"

    rv = MsgBox(MyMsg, vbYesNo, MyTitle)

    If (rv = vbNo) Then
        DoCmd.Quit
    End If

    MyMsg = "Please enter the (UNC) complete path to the Back-End "
    MyMsg = MyMsg & "Application."
    MyMsg = MyMsg & vbCrLf & vbCrLf & vbCrLf
    MyMsg = MyMsg & &quot;UNC Example:  \\<servername>\<share>\&quot;
    rv = InputBox(MyMsg, &quot;Enter Path to File&quot;, &quot;&quot;)

    If (rv = &quot;&quot;) Then
        fpath = CurDir
    End If

    If (Right$(fpath, 1) <> &quot;\&quot;) Then
        fpath = fpath & &quot;\&quot;
    End If          'This is the IMPORTANT Change

    rv = Dir(fpath & GblBackEndDBName, vbNormal)
    If (rv = &quot;&quot;) Then

        MyMsg = &quot;The Back-End Application cannot be found at the &quot;
        MyMsg = MyMsg & &quot;location specified.&quot;
        MyTitle = &quot;Terminating Application&quot;
        MsgBox MyMsg, vbCritical, MyTitle
        DoCmd.Quit acQuitSaveNone

    End If

    StepOne = True

    fpath = rv
    rv = fctnLinkTables()

End Function

The problem is in that IF the user enters a path, the check for the &quot;\&quot; isn't done (it is - if they do not enter the path). I made some other cosmetic changes - mostly for my own readability, but the IMPORTANT one is to move the one &quot;End If&quot; from above &quot;Else&quot;
&quot;StepOne = True&quot;
to just below &quot;fpath = curdir&quot;


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over