problem in vb6 database application

[adonis]

the program i created requires me to search from a database and display the results.for this i added a separate find for where i included an API call to display the available records as i type.
heres the code....

this is the API call..

Public Declare Function sendMessageByStrings& Lib "user32" _
Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, _
                      ByVal lParam As String)
Public Const LB_SELECTSTRING = &H18C
Public gFindString As String
Public Const gDataBaseName = "C:\Documents and Settings\Apollo\My Documents\New Folder (2)\db12.mdb"

this is the code for the find form
Code:

Private Sub Form_Activate()
List1.Enabled = False
dtafind.DatabaseName = gDataBaseName
dtafind.Refresh
If (dtafind.Recordset.RecordCount > 0) Then
    Screen.MousePointer = vbHourglass
    dtafind.Recordset.MoveFirst
    While Not dtafind.Recordset.EOF
        List1.AddItem dtafind.Recordset.Fields(0) & ""
        dtafind.Recordset.MoveNext
    Wend
    List1.Enabled = True
    DoEvents
End If
lblcount = "There are " & dtafind.Recordset.RecordCount & " records"
Screen.MousePointer = vbDefault

End Sub

Public Property Let recordsource(ByVal sNewValue As String)
dtafind.recordsource = sNewValue
End Property

Private Sub Form_Unload(Cancel As Integer)
Set frmfind = Nothing
End Sub

Private Sub List1_DblClick()
gFindString = List1
Unload frmfind

End Sub

Private Sub txtfind_Change()
Dim entryNum As Long
Dim txttofind As String
txttofind = txtfind.Text
entryNum = sendMessageByStrings(List1.hwnd, LB_SELECTSTRING, 0, txttofind)

End Sub

and this is the code for linking the find form with the main program..this code is the event of clicking the "Find" button

Code:

Case cmdfind
Dim ireturn As Integer
gFindString = ""
With frmfind
    .recordsource = "SELECT term FROM Table1 ORDER BY term"
    .Show vbModal
End With
If (Len(gFindString) > 0) Then
    With Data1.Recordset
        .FindFirst "term = '" & gFindString & "' "
            If (.NoMatch) Then
                ireturn = MsgBox("term " & gFindString & _
                                 " was not found.", vbCritical, "term")
            Else
                ireturn = MsgBox("term " & gFindString & " was retrieved.", _
                              vbInformation, "term")
            End If
    End With
End If

now the problem is..when i run the program from within vb..it runs fine..
but when i make the .exe and run it..the search part does not work.the API call and the find form responds just fine..it even says "the ' ' term was retrieved" as i had instructed i to say..but the record that is returned by the find form is not displayed in the main form


another questoin...how do i make the loacation of the database as indicated in the code independant of where the setup file install the program..i mean..here i have given the location of gDataBasename as

Public Const gDataBaseName = "C:\Documents and Settings\Apollo\My Documents\New Folder (2)\db12.mdb"

but the user may not install the program in that manner...in fact there is little chance he will.so how what code do i need to include or modify to make this program run?

i know this was a very long post..thanks for bearing with it..
waiting for ur replies..

[Joel Egghead]

Ok the problem with the Database directory…if you don’t have a server to work from…that would be the most ideal because the location wont change and all of the network users would be able to connect to it, but if you don’t have a server and only have one user who is changing the data, I would do this by putting a subfolder in the program folder named “Database” or “Data” and I put the Access database in that folder. Now to get connected to that data during the debugging process I would put a “dummy” folder on your C: drive that’s named the same as your program folder and put the data folder with a copy of your database in that dummy folder and you can do your programming and when you have made your EXE place your real database folder in the real Program folder and ether delete the dummy one or save over it. This will allow you to test your work in the location it really will exist and Im guessing that’s why your debugging works but your exe file wont because its trying to get to a folder it has no way to get to. One key to this is having it install into a folder every one has…for example you may have the folder :
Public Const gDataBaseName = "C:\Documents and Settings\Apollo\My Documents
But when you install it to someone elses machine they will have a something similar to:
C:\ Documents and Settings\Joel\My Documents
Your best way to avoid this problem is to have your program install on the base level of C:
For example -
Your program Directory would be : C:\YourProgramName\Name.exe
And you would have a directory : C:\ YourProgramName\Database\MyData.mbd
The code would look like this to connect to the database:
Public Const gDataBaseName = " C:\ YourProgramName\Database\MyData.mbd"
I hope this helps!