This is what I want to do - Can you help

[Musicman]

Calling all bright sparks......

I have never used Access but I'm guessing it may be able to do what I want.


I am an entertainer who would like to have a database of lyrics/chords/sheet
music on a laptop instead of carrying several books, scraps of paper,
printed sheets....etc.

Ideally I need a 'program' to open up and have an alphabet and some sort of
cataloguing system where I can quickly access lyrics without hunting through
hard copy folders (ie: non-PC folders) and various books to find what I
need. My original plan with this was to convert everything to .pdf files but
I'm not sure if these can be imported into Access ? I guess Acrobat will do
this but that's unavailable to me and very expensive.


Any ideas ladies and gentlemen?


(e-mail address removed)


__________ Information from ESET Smart Security, version of virus signature database 4049 (20090501) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Mark Andrews]

Yes, Access would work well.

Just do a simple access db similar to:
http://www.mldb.org/
but not web-based, just an access table, maybe a few lookup tables and one
form

where you have one form that has the A thru Z and maybe a few other ways of
filtering.

decide if the actual lyrics can be stored in a memo field as text or if you
should actually have an attachment on each record.
Text would work better, if that's possible (not sure what format you have
the lyrics in). You can use a pdf printer driver to print any document to
pdf.
You don't need acrobat. www.win2pdf.com is a good one, but I know there are
some free ones out there too.

If you have never used Access you may want to get some help or just find a
db for purchase you could use.
It's probably a one hour job, to build something basic (for someone who
knows what they are doing).

I have a CRM template that has the A thru Z (to search for clients etc...):
http://www.rptsoftware.com/products/crmtemplate/
It probably has way too much code for what you need, but you could delete
most of it and go from there?

HTH,
Mark Andrews
RPT Software
http://www.rptsoftware.com

 

[ken]

You don't need Acrobat; the free Adobe Reader will be enough. To
create the .pdf files, if you don't already have the means of doing
this, you can install one of the many free pdf printer drivers such as
CutePDF Writer from:

http://www.cutepdf.com/

What you need to do is store the path to each .pdf file in a text
field in a row in a table in Access. Also give the table an
autonumber column as its primary key, and you might also want to
include a column such as DocumentType with values such as lyrics,
chords, sheet music etc to identify the type of document in each case.

If you do include a DocumentType column then you should also create a
DocumentTypes table with one column DocumentType. This table will
have one row per type and be related on-to-many to your main Documents
table, making sure that referential integrity and cascade updates are
enforced in the relationship. This protects the integrity of the data
as it ensures that only valid types can be entered in the main
Documents table.

Data entry would in the main be by a form based on the main Documents
table. Never enter data directly into a table in datasheet view,
always via forms. To find a document you'll need some means of
navigating to the correct record in this form. This will depend how
you catalogue your documents, but it can be done either by unbound
controls in the form, or by a separate unbound dialogue form.

To enter the path to a .pdf file in the form you don't need to type it
all in; you can open a dialogue to browse to the file. I normally use
Bill Wilson's class module for this, available from:

http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=22415

The code for a Browse button on the form would be:

On Error GoTo Err_Handler

Dim OpenDlg As New BrowseForFileClass
Dim strPath As String

OpenDlg.DialogTitle = "Select File"
OpenDlg.AdditionalTypes = _
"PDF Files (*.pdf) |*.pdf"
strPath = OpenDlg.GetFileSpec
Set OpenDlg = Nothing

If Len(strPath) > 0 Then
Me.txtPath = strPath
End If

Exit_here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_here

where txtPdfPath is the name of the control on the form bound to the
text field containing the path.

To open the .pdf file from your form there are a variety of methods
you can use. The simplest s to follow a hyperlink, but I normally
call the Windows API using the following module:

Option Compare Database
Option Explicit

Declare Function ShellExecute& Lib "shell32.dll" Alias
"ShellExecuteA" (ByVal _
hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String,
ByVal _
lpParameters As String, ByVal lpDirectory As String, ByVal nshowcm As
Long)

Sub ShellToFile(strPath As String, ByVal lngHwnd As Long)

Dim lngRetVal As Long

lngRetVal = ShellExecute(lngHwnd, "open", strPath, _
vbNullString, CurDir, 1)

If lngRetVal < 32 Then
MsgBox "Unable to open file " & strPath, vbInformation,
"Warning"
End If

End Sub


Paste the above into a new module and save it as mdlShellExecute say.
You can then call the ShellToFile function from your form with a
button using code like this:

ShellToFile Me.txtPdfPath, Me.Hwnd

All of the above is easy to implement if you have sufficient
experience with Access. You do say you are new to it, however, so I'd
suggest you first take some time to learn a little more about it.
There are plenty of good general purpose books on Access available,
and if you Google 'Access + Tutorial' you should find a number of
online resources to use. Also take some time studying the same
Northwind data base which comes with Access to see how a relational
database is put together in terms of tables and relationships between
them, how the user interfaces with the data via forms and reports, and
how queries are used to bring together data from the various tables as
needed.

Ken Sheridan
Stafford, England

 

[Arvin Meyer MVP]

You don't need to use PDFs to store lyrics, you can use Word. I have a
sample app that will catalog all Word files in a folder and subfolders, even
creating the table to store them. With very little effort you can change to,
or include PDFs.

http://www.datastrat.com/Download/DocMgr_2K.zip

If you decide to go with PDFs, there are several free creators, and 1 very
excellent (in fact I prefer it to Acrobat) program: Win2PDF

http://www.win2pdf.com

 

[Musicman]

Wow. Thank you all so much for taking the time to give some really detailed
answers. Access is the tool - albeit it a complicated one!

With reference to the .pdf printer driver, I use PrimoPDF so I have that
sorted. All I have to do now is learn the basics of Access!
I wanted to use .pdf files as it is slightly easier to scroll down using the
'grab' hand rather than a scroll bar in a word processing doc
but maybe that won't work inside Access. All these options are now under the
microscope but a text box should work just fine.

All this said, I have a lot of detailed information contained within the
replies so once again, thank you all.

You have all been so helpful, I'm sure you'll be there for a few technical
tweaks.

Regards

Kevin
************************************************************************************************************

Calling all bright sparks......

I have never used Access but I'm guessing it may be able to do what I
want.


I am an entertainer who would like to have a database of
lyrics/chords/sheet
music on a laptop instead of carrying several books, scraps of paper,
printed sheets....etc.

Ideally I need a 'program' to open up and have an alphabet and some sort
of
cataloguing system where I can quickly access lyrics without hunting
through
hard copy folders (ie: non-PC folders) and various books to find what I
need. My original plan with this was to convert everything to .pdf files
but
I'm not sure if these can be imported into Access ? I guess Acrobat will
do
this but that's unavailable to me and very expensive.


Any ideas ladies and gentlemen?


(e-mail address removed)


__________ Information from ESET Smart Security, version of virus
signature database 4049 (20090501) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4049 (20090501) __________

The message was checked by ESET Smart Security.

http://www.eset.com