Loosing Db connection?!?!?!

[Jozef]

Hello,

I'm having the following issue;

I'm having trouble referencing these public variables after they are being
set, and I'm not sure why. I use the code below to set open a connection to
a database. The variables are public variables and I hadn't had any
problems previously, but I seem to be having trouble now. I could have
modified it between the time it last worked and now (while I was cleaning up
some of the code) but I don't recall touching anything.

First I run this code;
Public wksp As DAO.Workspace
Public db As DAO.Database

Function connectMyDb(Optional strlocation As String) As Boolean
On Error GoTo connectMyDb_Err

If strlocation = "" Then strlocation = getAttachment() ' If there is no
location specified, then get an attachment from existing attachments

DBEngine.SystemDB = "system.mdw"

Set wksp = CreateWorkspace("ModifyDb", "Admin", "", dbUseJet)

Set db = wksp.OpenDatabase(strlocation, False, False, ";pwd=" &
GetPassword())

connectMyDb = True

connectMyDb_Exit:
Exit Function

connectMyDb_Err:
If Err.Number = 3031 Then
'If this error is thrown, the user has entered a password on his or her
own database
SetCustomPassword
Resume
ElseIf Err.Number = 3051 Then
'MsgBox "Data file is read only. Cannot continue", vbCritical, "Read
Only Data file"
Resume connectMyDb_Exit
Else
MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error
Connecting"
Resume connectMyDb_Exit
End Function


Right after I call this code, I do a
Debug.Print db.Name

and that throws the error
"3420 Object invalid or no longer set".

I've been beating my head against the wall wondering why this is. Any
ideas?

Thanks!

[Lyle Fairfield]

Jozef wrote:

Hello,

I'm having the following issue;

I'm having trouble referencing these public variables after they are being
set, and I'm not sure why. I use the code below to set open a connection to
a database. The variables are public variables and I hadn't had any
problems previously, but I seem to be having trouble now. I could have
modified it between the time it last worked and now (while I was cleaning up
some of the code) but I don't recall touching anything.

First I run this code;
Public wksp As DAO.Workspace
Public db As DAO.Database

Function connectMyDb(Optional strlocation As String) As Boolean
On Error GoTo connectMyDb_Err

If strlocation = "" Then strlocation = getAttachment() ' If there is no
location specified, then get an attachment from existing attachments

DBEngine.SystemDB = "system.mdw"

Set wksp = CreateWorkspace("ModifyDb", "Admin", "", dbUseJet)

Set db = wksp.OpenDatabase(strlocation, False, False, ";pwd=" &
GetPassword())

connectMyDb = True

connectMyDb_Exit:
Exit Function

connectMyDb_Err:
If Err.Number = 3031 Then
'If this error is thrown, the user has entered a password on his or her
own database
SetCustomPassword
Resume
ElseIf Err.Number = 3051 Then
'MsgBox "Data file is read only. Cannot continue", vbCritical, "Read
Only Data file"
Resume connectMyDb_Exit
Else
MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error
Connecting"
Resume connectMyDb_Exit
End Function

Right after I call this code, I do a
Debug.Print db.Name

and that throws the error
"3420 Object invalid or no longer set".

I've been beating my head against the wall wondering why this is. Any
ideas?

Thanks!

This code should not run as it is missing an End If.
If it does run it should raise an error; that would explain why the
public variable is released. Public Variables are released when an error
occurs.

Jozef:

It's time someone said this:

Your code is convoluted, unusual and puzzling. It is not styled as much
of the better code written by experienced developers is. Perhaps, it's
effective; perhaps it's suited to your unique needs but ...

My evil twin, Kyle, would say that the code is crap. He would also say,
Short simple code is usually good code; long involved code is often bad
code.
Fortunately he is not writing this or you might be offended, but he told
me that after he cleaned out all the kludge from your code it worked
fine. He just couldn't understand what it was for.

I would suggest that you tell us what you want to do, clearly, and ask
for suggestions.

--
Lyle Fairfield

[Steve]

I'm likin' this Kyle dude.

[Jozef]

Hi Lyle,

If I was as artistic as you and your evil twin, I likely wouldn't be here
requesting assistance. ;)

The end if does exist in the original code, and was likely snipped out when
I edited some of the extraneous comments out.

The problem as I can see is not with the code, it's in retaining the public
variables after. I have stepped through the code and there are no errors
thrown, but the second the code is finished, something is lost, because as I
stated in the orignal post;

Right after I call this code, I do a
Debug.Print db.Name

and that throws the error
"3420 Object invalid or no longer set".

What I'm attempting to do is open the database connection in a central
location for use elsewhere. So when the routine falls out of scope, the
database object is still live and the connection is still open until I call
a second routine that closes the connection and sets the database variable
to nothing.

If you need any further information please let me know. If you have a
shorter cleaner way to do this, then it would likely cost less keystrokes
than critism.

Thanks!

This code should not run as it is missing an End If.
If it does run it should raise an error; that would explain why the public
variable is released. Public Variables are released when an error occurs.

Jozef:

It's time someone said this:

Your code is convoluted, unusual and puzzling. It is not styled as much of
the better code written by experienced developers is. Perhaps, it's
effective; perhaps it's suited to your unique needs but ...

My evil twin, Kyle, would say that the code is crap. He would also say,
Short simple code is usually good code; long involved code is often bad
code.
Fortunately he is not writing this or you might be offended, but he told
me that after he cleaned out all the kludge from your code it worked fine.
He just couldn't understand what it was for.

I would suggest that you tell us what you want to do, clearly, and ask for
suggestions.

--
Lyle Fairfield

[Lyle Fairfield]

next try

if I call this to open a read only file then i also get 3420 Object
invalid or no longer set.

when I look at your code I see that you commented out the message box
for a read only file error; so's I get no error message in the original
function, but the db is not opened.

ElseIf Err.Number = 3051 Then
'MsgBox "Data file is read only. Cannot continue", vbCritical,
"Read Only Data file"
Resume connectMyDb_Exit
Else

[David W. Fenton]

"Jozef" <[e-mail]> wrote in news:Cvjof.1762$wg4.1654@edtnps84:

What I'm attempting to do is open the database connection in a
central location for use elsewhere. So when the routine falls out
of scope, the database object is still live and the connection is
still open until I call a second routine that closes the
connection and sets the database variable to nothing.

Why are you using a separate workspace? Why not just use the default
workspace?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Jozef]

Damn....that must have been it. It doesn't explain the step through, and I
don't have any test dbs that are read only, but I can't seem to duplicate
the issue. Too much time in front of the computer screen maybe?

What would you suggest for "best practices" reading material so my code
isn't so convoluted, puzzling and crappy?

Thanks!
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

next try

if I call this to open a read only file then i also get 3420 Object
invalid or no longer set.

when I look at your code I see that you commented out the message box
for a read only file error; so's I get no error message in the original
function, but the db is not opened.

ElseIf Err.Number = 3051 Then
'MsgBox "Data file is read only. Cannot continue", vbCritical,
"Read Only Data file"
Resume connectMyDb_Exit
Else

[Jozef]

Hi David,

The separate workspace was somewhat of a "desperate times call for desperate
measures" entry. I was having trouble (still am...I think) having trouble
maintaining a valid connection to a passworded database after this
connection to the database was destroyed. I will still be plugging away at
trying to resolve that issue, but will try and remove it once I get back to
that issue after some testing.
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@216.196. 97.142...

"Jozef" <[e-mail]> wrote in news:Cvjof.1762$wg4.1654@edtnps84:

What I'm attempting to do is open the database connection in a
central location for use elsewhere. So when the routine falls out
of scope, the database object is still live and the connection is
still open until I call a second routine that closes the
connection and sets the database variable to nothing.

Why are you using a separate workspace? Why not just use the default
workspace?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Jozef]

Scratch that, I was able to duplicate it, but it doesn't look like it's the
db variable at all. What was throwing me off (and still has me scratching
my head) was that referencing debug.print db.name still raised an exception.
The problem was referencing a document after that debug which strangely
enough, corrected the preceding issue. I love bumping my head, it feels so
good when I stop.

"Jozef" <[e-mail]> wrote in message news:8olof.2383$wg4.2029@edtnps84...

Damn....that must have been it. It doesn't explain the step through, and
I don't have any test dbs that are read only, but I can't seem to
duplicate the issue. Too much time in front of the computer screen maybe?

What would you suggest for "best practices" reading material so my code
isn't so convoluted, puzzling and crappy?

Thanks!
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

next try

if I call this to open a read only file then i also get 3420 Object
invalid or no longer set.

when I look at your code I see that you commented out the message box
for a read only file error; so's I get no error message in the original
function, but the db is not opened.

ElseIf Err.Number = 3051 Then
'MsgBox "Data file is read only. Cannot continue", vbCritical,
"Read Only Data file"
Resume connectMyDb_Exit
Else

[Lyle Fairfield]

Jozef wrote:

What would you suggest for "best practices" reading material so my code
isn't so convoluted, puzzling and crappy?

Do a Google Newsgroup search on "Terry Kreft" and study his code.
"Dimitri Furman" is another good model.
From the olden days there is "Dev Ashish".
And I've neglected or forgotten other good ones; apologies.

There's an ancient book that I love so well I can't remember the name of
but it sure is great! LOL!

--
Lyle Fairfield

[Lyle Fairfield]

Lyle Fairfield wrote:

Jozef wrote:

What would you suggest for "best practices" reading material so my
code isn't so convoluted, puzzling and crappy?

Do a Google Newsgroup search on "Terry Kreft" and study his code.
"Dimitri Furman" is another good model.
From the olden days there is "Dev Ashish".
And I've neglected or forgotten other good ones; apologies.

There's an ancient book that I love so well I can't remember the name of
but it sure is great! LOL!

I was looking at some pictures and I remembered:

It's

Hardcore Visual Basic
by Bruce McKinney

and its freely available at

http://vb.mvps.org/hardcore/

It's old and it's not got huge amounts of code (I know that's what you
asked about) but it sure is fascinating.
--
Lyle Fairfield

[Randy Harris]

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:n_****************@read1.cgocable.net...

Jozef wrote:

What would you suggest for "best practices" reading material so my code
isn't so convoluted, puzzling and crappy?
Do a Google Newsgroup search on "Terry Kreft" and study his code.
"Dimitri Furman" is another good model.
From the olden days there is "Dev Ashish".
And I've neglected or forgotten other good ones; apologies.

And don't overlook Kyle Fairfield's twin brother Lyle.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

There's an ancient book that I love so well I can't remember the name of
but it sure is great! LOL!

--
Lyle Fairfield

[Jozef]

Thanks Lyle.

Any idea why my attachments would "disconnect" after disposing of the db
variable? What I'm trying to do with that code is open the db connection
for modification, modify a table, then set that db varaible to nothing, so
the calling routine would look something like this

Public Sub ModifyTable()

ConnectMyDb "C:\My Database"

---Code to modify the table---

-------- goes here --------

DisconnectMyDb

End Sub.

My attachments are fine prior to connecting, and during the connect. After
modification, (even if modification doesn't occur) the tabledefs in are
refreshed in both databases, yet after the "DisconnectMyDb" is run (which
simply closes the db and sets the variable to nothing) the attached tables
return the error "Invalid Password" when there is an attempt to access
them. I've tried refreshing the tabledefs after the disconnect, but still
get the same error.

Any ideas? Or should I troll somewhere else? The DAO NG maybe?

Thanks!
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:gb***************@read2.cgocable.net...

Lyle Fairfield wrote:

Jozef wrote:

What would you suggest for "best practices" reading material so my code
isn't so convoluted, puzzling and crappy?

Do a Google Newsgroup search on "Terry Kreft" and study his code.
"Dimitri Furman" is another good model.
From the olden days there is "Dev Ashish".
And I've neglected or forgotten other good ones; apologies.

There's an ancient book that I love so well I can't remember the name of
but it sure is great! LOL!

I was looking at some pictures and I remembered:

It's

Hardcore Visual Basic
by Bruce McKinney

and its freely available at

http://vb.mvps.org/hardcore/

It's old and it's not got huge amounts of code (I know that's what you
asked about) but it sure is fascinating.
--
Lyle Fairfield

[Lyle Fairfield]

I tried but I can't duplicate this problem. My links are golden.

My suggestions are:

1. Show us the code that modifies the table
and
2. Show us the DisconnectMyDB code

or

tell us in general what you are trying to do and why. I think it's
unusual to open a new workspace to connect to an mdb whose tables we
are already linked to. Possibly we could do whatever it is that you
want to do with a JET SQL call.

[David W. Fenton]

"Jozef" <[e-mail]> wrote in news:%qlof.2389$wg4.681@edtnps84:

The separate workspace was somewhat of a "desperate times call for
desperate measures" entry. I was having trouble (still am...I
think) having trouble maintaining a valid connection to a
passworded database after this connection to the database was
destroyed. I will still be plugging away at trying to resolve
that issue, but will try and remove it once I get back to that
issue after some testing.

Password protecting a back end is, in my opinion, not a helpful
thing.

To be honest, I would normally use less polite language to describe
my opinion there.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Jozef]

Hi Lyle,

Sorry for the delay. I think I have figured out not really the source of
the problem, but a solution to it anyway. If I put the code here in it's
entirety, it would be quite a lot of code, since there are a lot of little
generic procedures called throughout. I'll fight through it and strip out
piece by piece and see if I can isolate it first, and if not then I'll use
the work around.

Thanks!
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I tried but I can't duplicate this problem. My links are golden.

My suggestions are:

1. Show us the code that modifies the table
and
2. Show us the DisconnectMyDB code

or

tell us in general what you are trying to do and why. I think it's
unusual to open a new workspace to connect to an mdb whose tables we
are already linked to. Possibly we could do whatever it is that you
want to do with a JET SQL call.

[Lyle Fairfield]

Enjoy!