DIM and Set db once per module

G

Guest

I've asked this in a previous thread but wanted to open it up to the whole
community.

I have a procedure that calls several other procedures. Each of the called
prcedure contain the following:

dim db as database
set db = OpenDb("filename.mdb")

What I'd like to do is declare this once for the entire module so that I
don't have to do it in every procedure.

Is this possible? I tried doing it in the declarations section but got an
invalid outside procedure error.

As always, thanks in advance for your assistance...
 
G

Guest

You can do the Dim in the Declarations, the Set has to be in a procedure.
If the module is a form or report module, do the set in the Load event. If
it is a standard module, do the set in the procedure that calls the other
procedures. All these procedures should be in the same module.
 
G

Guest

I use CodeDB to open data within the same database instead of a variable. It
seems to work fine and you don't have to close it. RE: CodeDB.OpenRecordset,
CodeDB.Execute
 
T

Tim Ferguson

dim db as database
set db = OpenDb("filename.mdb")

What I'd like to do is declare this once for the entire module so that
I don't have to do it in every procedure.

An alternative approach is to wrap the whole thing up in a Class module,
so you don't have to worry about reopeing the db if you accidentally
closed it etc etc.

Set o = New OtherDatabase

...

o.CreateNewField ("MyTable", "MyField", "INTEGER NOT NULL")


...

o.GetLastNamesByCountry("Albania", MyArray())


and the Class_Initialise and Class_Terminate methods can look after all
the dirty stuff and so on.

Just a thought


Tim F
 
G

Guest

Tim,
Thank you for the response.
I am using Visual basic 5.0 and I am working on the info you gave me but I
keep getting a compile error in the following line of your code
---------------------------------
Set dbe = CreateObject("DAO.DBEngine.36")
----------------------------------
The following code you sent is updated with my filepath
-----------------------------------------------------------------
' ***** start of script *****
Option Explicit

' VBS Script to look up upcoming birthday

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

' 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(qdfBirthdays, dbOpenSnapshot, dbForwardOnly)

' 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 displayText = "No birthdays due"

' tell the user
WScript.Echo displayText

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

' ***** End of script *****




------------------------------------------------------------------
 
T

TC

There's no compile error in the line that you show, as far as I can
see.

Are you sure you don't mean a runtime error? If so, what is the runtime
error message?

HTH,
TC
 
G

Guest

I put the code in a module then click run and it says
compile error
Invalid outside procedure
then it highlights the word set in the code where the following is located
 
T

TC

You must have put that code /outside/ the confines of a sub...end sub
or function...end function. It must go /inside/ one of those things.


<not ok here>
sub blah
<ok here>
end sub
<not ok here>
function blop()
<ok in here>
end function
<not ok here>

and so on.

HTH,
TC
 
G

Guest

I'm kinda lost, lol :)
The following is the code I have for my Birthday.frm and then my Module
below that
*****Start Birthday.frm ****
Private Sub Command1_Click()
A = MsgBox("Do you want to check for upcoming Birthdays", vbQuestion +
vbYesNo, "Birthday")
If A = vbYes Then
Shell """C:\Program Files\Microsoft Office\Office10\MSAccess.exe"" " & _
"""C:\Documents and Settings\Owner\Desktop\Family
Database\Family.mdb""", vbMaximizedFocus

End
Else
End
End If

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 Birthday.frm *****

*****Start Module ****
' ***** start of script *****
Option Explicit

' VBS Script to look up upcoming birthday

Const pathMDBFile = "c:\temp\birthdays.mdb"
Const qdfBirthdaysDue = "BirthdaysDue"

' 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(qdfBirthdaysDue, dbOpenSnapshot, dbForwardOnly)

' 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 displayText = "No birthdays due"

' tell the user
WScript.Echo displayText

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

' ***** End of script *****

*****End Module *****
 
G

Guest

Alright, I'm getting close. Compile errors are gone. Thanks for the help
The only thing now is I can't get it to search the query without opening up
the database. If I click yes on the msgbox it opens the database and wors
comes to worse this could do, But I would like it to possibly run the query
and if no birthdays close out database but if there are thin show the query.
Thanks for your time!!!
Here is what I have in my vb code and the the text of the "Birthdays" query
=========Start VBcode===========
Private Sub Command1_Click()
A = MsgBox("Do you want to check for upcoming Birthdays", vbQuestion +
vbYesNo, "Birthday")
If A = vbYes Then


' 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)

' 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 displayText = "No birthdays due"

' tell the user
' arrange for a neat closedown
ss.Close
db.Close
Shell """C:\Program Files\Microsoft Office\Office10\MSAccess.exe"" " & _
"""C:\Documents and Settings\Owner\Desktop\Family
Database\Family.mdb""", vbMaximizedFocus

End
Else
End
End If

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 VBcode===========

========Start "BirthdaysDue" query ========

SELECT FirstName & " " & LastName AS FullName, CVDate(NextBDay([BirthDate]))
AS [Upcoming Birthday]
FROM Contacts
WHERE (((CVDate(NextBDay([BirthDate])))<=Date()+30));

========End "BirthdaysDue" query ========
 
T

TC

The code you quote - is that inside the database file, or is it a
seperate VBScript file? It looks like a VBScript file, to me. But if it
is a VBScript file, the two Sub procedures do not make sense -
VBScripts do not have buttons. /They/ make it look like Visual Basic
for Applications (VBA) code from within a database file! So I am
cofused where you have put this code.

Apart from that, the code looks fine, until you get to here:

Loop
' an empty MsgBox is so unhelpful: use some default text instead!
If Len(displayText) = 0 Then displayText = "No birthdays due"
' tell the user
' arrange for a neat closedown
ss.Close
db.Close
Shell etc.

Having carefully collected all the information tht you wan to display,
in the displayText variabnle, you then /ignore/ that variable totally,
and shell-open the database /whatever/ the value of displayText is!

This is probably what you intended:

Loop
If Len(displayText) = 0 Then
msgbox "No birthdays due"
end
endif
msgbox "Birthdays due are " & displayText

Finally, I'm not sure why you want to shell-open the database. You
already have the list of birthdays - you got it wihout opening Access.
So why do you then open Access?

HTH,
TC
 
J

John Nurick

Tim supplied a VBScript, which needs to go into a text file, not a
module.

If you want to use the code in VB, start by creating a Sub procedure ina
module and then paste the entire script into the procedure.
 
G

Guest

The reason for opening the database would be that the database has all
addresses and phone numbers for those contacts and I only want it to open
through this code if there are Birthdays due. This is the only reason for
this. Please Remember I am new to vbcode. So far the code works except it is
checking my contacts table instead of my birthdays query.
I think the following part of the code checks the table instead of my query?
Const qdfContacts = "Contacts"

If I can get it to check the query and open database only if there are
birthdays listed in query I think I might be all set. The query is set to
check dates to show only the ones within 30 days
Here is all the updated code
Thank you guys so much for the help.

*********Start code**************


Private Sub Form_Load()
A = MsgBox("Do you want to check for upcoming Birthdays", vbQuestion +
vbYesNo, "Birthday")
If A = vbYes Then


' 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)

' 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

MsgBox "Birthdays due are " & displayText


' tell the user
Shell """C:\Program Files\Microsoft Office\Office10\MSAccess.exe"" " & _
"""C:\Documents and Settings\Owner\Desktop\Family
Database\Family.mdb""", vbMaximizedFocus


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




End
Else
End
End If

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 code***************
 
T

TC

You haven't answered my question about whether your code is (a) VBA
code running from within Access, or (b) VBScript code in an external
file. At present, the whole thing does not make any sense to me.
There's no point asking /new/ questions, as you have done, until you
clarify this fundamental point.

HTH,
TC
 
G

Guest

I'm sorry,
All of the code is in an external Birthday.exe
Also the following is my sql for the query.
************Query***********

SELECT FirstName & " " & LastName AS FullName, CVDate(NextBDay([BirthDate]))
AS [Upcoming Birthday]
FROM Contacts
WHERE (((CVDate(NextBDay([BirthDate])))<=Date()+30));

******End Query*****
 
T

Tim Ferguson

You haven't answered my question about whether your code is (a) VBA
code running from within Access, or (b) VBScript code in an external
file. At present, the whole thing does not make any sense to me.
There's no point asking /new/ questions, as you have done, until you
clarify this fundamental point.

The original post seemed to want some code that would run on starting up
the PC, so I suggested a short VBS script that avoided Access altogether
and could be run straight from the StartUp programs folder.

Somewhere along the line it seems to have changed into some VBA that is to
run inside a VB 5 project. In this situation I would have thought it's
easiest to set the project References to the DAO library (which I think is
a default anyway, or was that the 3.5 Compatibility layer?) and just call
the querydef.OpenRecordset method.

Each to his own.


All the best


Tim F
 
T

TC

I agree with the script approach! But then, I don't see where the
command button & label comes in. Hopefully we can sort this out with
the OP.

Cheers,
TC
 
T

TC

Alvin said:
All of the code is in an external Birthday.exe

You mean a Visual Basic (VB) exe?

If so, I'm more confused than before! Why use VB, when you could do it
directly in Access?

Maybe you misunderstood what Tim Ferguson suggested to you initially.
That was, to put the code in a *** VBScript file ***. That is a simple
text file, that you could create with Notepad, with file extension VBS.
Is that what you understood him to suggest?

HTH,
TC
 
G

Guest

You are correct,
I did misunderstand. Sorry for the confusion. I am new to this. but it is
working now. I have set the task schedualer to run Birthday.exe every 2 weeks
and it searches the query and returns the results. The only other thing I
think might be helpful is if the results are null it will show that there are
no Birthdays and then close the database from the Birthday.exe
Thank you all for the help and again I'm sorry for the misunderstanding
Alvin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top