locate file

[Alvin]

I have a database I want to distribute and the user places it anywhere on
their pc they want. I need the following script to locat and open that
database "Family.mdb"
How can I do that with the following code? Right now the file path is set
but I would like the Birthdays.exe to locate and open it no matter where it
is saved on the PC.
Thanks in advance.
Alvin
Here is the code I have

*********start*************


Private Sub Form_Load()


' VBS Script to look up upcoming birthday

Const pathMDBFile = "C:\Documents and Settings\Owner\Desktop\Family
Database\Family.mdb"
Const qdfContacts = "Contacts"

' dao constants
Const dbOpenSnapshot = 4
Const dbForwardOnly = 8

Dim dbe, db, ss ' as DBEngine, as Database, as Recordset
Dim displayText ' as string


' create a db engine
Set dbe = CreateObject("DAO.DBEngine.36")

' open the database readonly
Set db = dbe.OpenDatabase(pathMDBFile, False, True)

' get a snapshot of the data
Set ss = db.OpenRecordset(qdfContacts, dbOpenSnapshot, dbForwardOnly)
Shell """C:\Program Files\Microsoft Office\Office10\MSAccess.exe"" " & _
"""C:\Documents and Settings\Owner\Desktop\Family
Database\Family.mdb""", vbMaximizedFocus

' run through any records that may have been returned
Do While Not ss.EOF

' create a simple string, just concatenate the names
' blank lines are ugly
If Len(displayText) > 0 Then displayText = displayText + vbNewLine
displayText = displayText & ss.Fields(0).Value

' next record
ss.MoveNext

Loop

' an empty MsgBox is so unhelpful: use some default text instead!
If Len(displayText) = 0 Then
MsgBox "No birthdays due"
End
End If



' arrange for a neat closedown
ss.Close
db.Close




End




End Sub

Private Sub Label1_Click()
A = MsgBox("This Program was built to help" & Chr(13) & Chr(10) & Chr(10) &
" you keep up with your Birthday list", vbInformation, "Birthday")
End Sub



*********end*************

 

[TC]

Just put the script in the same folder as the database. Then the script
can use the appropriate VBScript methods (which I can't recall for the
moment) to find the current directory. Then you just look for the
database there. You can still have a shortcut on the desktop, taskbar,
start menu, or where-ever, to point to the script.

HTH,
TC

 

[TC]

PS. I've given up asking you (in another thread) whether this is a VBS
script, a VB program, or MS Access VBA. It looks like VBA, but you keep
calling it a VBScript, and referring to it as an EXE .... all of which
are inonsistent. :-(

TC

 

[Alvin]

I have been testing both of them
The one with "Private Sub Form_Load()" is in .vbp which I convert to .exe
and the other I edit with notepad it is .vbs I did reply to this in a
previous post
Thanks TC for your continued help
Alvin

 

[Tim Ferguson]

The one with "Private Sub Form_Load()" is in .vbp which I convert to
.exe and the other I edit with notepad it is .vbs I did reply to this
in a previous post

I'm also a teeny-tiny bit pissed off with the number of new threads this
is spawning on the same question. Can we go back to basics:-

You have a mdb file somewhere with a bunch of birthdays in it. You can
read these birthdays in a number of ways:

1) use code inside the MDB itself; just call MSACCESS.EXE with /m
command line switch to run a macro that calls the code itself. The
advantage of this is that the whole thing remains atomic: there is only
one file path to consider, and developing and debugging is considerably
easier and quicker, especially since the data access components are
(obviously) built in. The main disadvantage is that loading a megalith
the size of MSACCESS just in order to display one MsgBox is a monstrous
waste of time and resources.

2) write a VB or VB.NET program to do it. Either of these has the
components built-in, so again you don't need any of this CreateObject
nonsense. The advantage of using one of these is the hugely flexible UI
you can build around it. Obviously your application will have to have
some way of finding its mdb file -- in the old days this would have been
an INI file; nowadays you have to mess about with the system registry.

3) using a scripting language like vbs is a "quick and dirty" method to
accomplish a small job -- read that as "light and fast" if you prefer
the sales pitch. You could use Perl or PHP instead if they are installed
and you feel more comfortable with them.

To be honest, the way I would recommend people with little experience
would be (1) first because it's the obvious way; (3) next because I've
already proved the principle; and (2) last -- but it's your application
and you can do it any way you want. But it's a bit hard for us when we
keep getting the same bit of code thrown back at the NG without any clue
about how it's being used or why.

All the best


Tim F

 

[Alvin]

And I said in those threads twice that
"I have been testing both of them
The one with "Private Sub Form_Load()" is in .vbp which I convert to .exe
and the other I edit with notepad it is .vbs"
So just forget it and don't get pissed off
Thanks anyway
Happy new year

 

[TC]

Gosh, I think you meant: "Tim, thanks for all your time & effort
helping with this - especially over the new year break. I apologize for
posting several threads on the same question, I will try to avoid that
in future."

TC

 

[Alvin]

No that's not it. The program isn't worth anyone getting pissed of at me. You
guys have been a lot of help to me in the past. I am only working with the
program so I can learn. I am still learning the different terminolagy for
different parts of programs, I am sorry for the confusion and I don't Know
of anyother way to let you guys know where the code is. Tim is right there is
to much confusion in this thread and it is probrably my lack of Knowledge of
the scripts or the way I have tried to explain it.
I do have " .vbs , .exe, .mdb " I have been working with on the program and
I have only worked with .mdb files in the past so I don't know how to explain
it any differently.
I am sorry for wasting your time and I am not upset in anyway. Thank you for
all the help
You have all been a big help so please don't get upset
Good day
Alvin