Attachments lost/reset once public db variable is set to ""

[Jozef]

Hello,

I have an Access XP database that has attachments to a BE database that has
a password. The attachments work fine, until I run some code that modifies
a BE table (adding a field). I do this by running code similar the
following;

Public varDb as DAO.database
sub ConnectBEDatabase(strLocation as string)
Set varDb = OpenDatabase(strLocation, False, False, ";pwd=" &
GetPassword())
end sub

sub DisconnectBEDatabase()
set varDB=nothing
end sub

function GetPassword()
getpassword="MyPassword"
end function

public Sub UpdateBE()
ConnectBEDatabase("C:\MyDatabase.mdb")
-----code to add a field to a table-----

-----end code to add a field-----
DisconnectBEDatabase
end sub

The problem is, once I run the disconnect, my table attachments all of a
sudden say "Not a valid password" or something of the like. I'm not
modifying the connectd strings or anything, just modifying the back end
tabledef.

Any ideas?

Thanks!

[Terry Kreft]

Firstly if your posting a problem where a particular error message is given
it's best to take the time to quote the full error rather than quoting
something you vaguely remember.

Are you refreshing the links to the BE tables that you are modifying? If you
don't do this you will get errors if the BE tables are changed.

--
Terry Kreft

"Jozef" <[e-mail]> wrote in message
news:c9qmf.142332$y_1.124960@edtnps89...

Hello,

I have an Access XP database that has attachments to a BE database that
has a password. The attachments work fine, until I run some code that
modifies a BE table (adding a field). I do this by running code similar
the following;

Public varDb as DAO.database
sub ConnectBEDatabase(strLocation as string)
Set varDb = OpenDatabase(strLocation, False, False, ";pwd=" &
GetPassword())
end sub

sub DisconnectBEDatabase()
set varDB=nothing
end sub

function GetPassword()
getpassword="MyPassword"
end function

public Sub UpdateBE()
ConnectBEDatabase("C:\MyDatabase.mdb")
-----code to add a field to a table-----

-----end code to add a field-----
DisconnectBEDatabase
end sub

The problem is, once I run the disconnect, my table attachments all of a
sudden say "Not a valid password" or something of the like. I'm not
modifying the connectd strings or anything, just modifying the back end
tabledef.

Any ideas?

Thanks!

[Jozef]

Pardon me, the EXACT error is "Not a valid password" rather than something
like "Not a valid password". Hope that clarifies things. I've tried
refreshing the attachments by doing Currentdb.TableDefs.Refresh, but still
get the same exact error message. Before the disconnect the attachments are
fine, and I don't get the message until the disconnect occurs. I could see
a possibility of this happening if I was manipulating the attachments with
the varDb variable, but theres no code that even touches the attachment
(other than the indirect source table modifications). Does that make sense?
"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:-Z********************@karoo.co.uk...

Firstly if your posting a problem where a particular error message is
given it's best to take the time to quote the full error rather than
quoting something you vaguely remember.

Are you refreshing the links to the BE tables that you are modifying? If
you don't do this you will get errors if the BE tables are changed.

--
Terry Kreft

"Jozef" <[e-mail]> wrote in message
news:c9qmf.142332$y_1.124960@edtnps89...

Hello,

I have an Access XP database that has attachments to a BE database that
has a password. The attachments work fine, until I run some code that
modifies a BE table (adding a field). I do this by running code similar
the following;

Public varDb as DAO.database
sub ConnectBEDatabase(strLocation as string)
Set varDb = OpenDatabase(strLocation, False, False, ";pwd=" &
GetPassword())
end sub

sub DisconnectBEDatabase()
set varDB=nothing
end sub

function GetPassword()
getpassword="MyPassword"
end function

public Sub UpdateBE()
ConnectBEDatabase("C:\MyDatabase.mdb")
-----code to add a field to a table-----

-----end code to add a field-----
DisconnectBEDatabase
end sub

The problem is, once I run the disconnect, my table attachments all of a
sudden say "Not a valid password" or something of the like. I'm not
modifying the connectd strings or anything, just modifying the back end
tabledef.

Any ideas?

Thanks!

[Steve Jorgensen]

I don't know if this relates, but when you obtain a DAO Database reference via
OpenDatabase, you should also call .Close on it before you let it go out of
scope.

On Sat, 10 Dec 2005 01:26:32 GMT, "Jozef" <[e-mail]> wrote:

Hello,

I have an Access XP database that has attachments to a BE database that has
a password. The attachments work fine, until I run some code that modifies
a BE table (adding a field). I do this by running code similar the
following;

Public varDb as DAO.database
sub ConnectBEDatabase(strLocation as string)
Set varDb = OpenDatabase(strLocation, False, False, ";pwd=" &
GetPassword())
end sub

sub DisconnectBEDatabase()
set varDB=nothing
end sub

function GetPassword()
getpassword="MyPassword"
end function

public Sub UpdateBE()
ConnectBEDatabase("C:\MyDatabase.mdb")
-----code to add a field to a table-----

-----end code to add a field-----
DisconnectBEDatabase
end sub

The problem is, once I run the disconnect, my table attachments all of a
sudden say "Not a valid password" or something of the like. I'm not
modifying the connectd strings or anything, just modifying the back end
tabledef.

Any ideas?

Thanks!

[Terry Kreft]

Yes, it makes sense now, you nearly got the right method, what you need to
do is use the RefreshLink method of the tabledef object.

So if the table you've modified is called tblMyTable (this is it's name in
the local database, i.e. the linked table not the source table in the BE
db), you would do something like

Currentdb.Tabledefs("tblMyTable ").Refreshlink

If you wanted to do all the linked tables you can do something like:-

Dim loDB as DAO.Database
Dim loTab as as DAO.Tabledef

set loDb = CurrentDB

For Each lotab in loDB.Tabledefs
with loTab
if Len(.Connect)>0 then
.Refreshlink
end if
End with
next


--
Terry Kreft

"Jozef" <[e-mail]> wrote in message news:uiJmf.145841$S4.61975@edtnps84...
<SNIP>

refreshing the attachments by doing Currentdb.TableDefs.Refresh, but still <SNIP> (other than the indirect source table modifications). Does that make
sense?
"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:-Z********************@karoo.co.uk...

Firstly if your posting a problem where a particular error message is
given it's best to take the time to quote the full error rather than
quoting something you vaguely remember.

Are you refreshing the links to the BE tables that you are modifying? If
you don't do this you will get errors if the BE tables are changed.

--
Terry Kreft

"Jozef" <[e-mail]> wrote in message
news:c9qmf.142332$y_1.124960@edtnps89...

Hello,

I have an Access XP database that has attachments to a BE database that
has a password. The attachments work fine, until I run some code that
modifies a BE table (adding a field). I do this by running code similar
the following;

Public varDb as DAO.database
sub ConnectBEDatabase(strLocation as string)
Set varDb = OpenDatabase(strLocation, False, False, ";pwd=" &
GetPassword())
end sub

sub DisconnectBEDatabase()
set varDB=nothing
end sub

function GetPassword()
getpassword="MyPassword"
end function

public Sub UpdateBE()
ConnectBEDatabase("C:\MyDatabase.mdb")
-----code to add a field to a table-----

-----end code to add a field-----
DisconnectBEDatabase
end sub

The problem is, once I run the disconnect, my table attachments all of a
sudden say "Not a valid password" or something of the like. I'm not
modifying the connectd strings or anything, just modifying the back end
tabledef.

Any ideas?

Thanks!

[Jozef]

Thanks Terry,

I did add that and also created a separate workspace for the db, and it
seems to work fine now.

Thanks for your help!

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:pO********************@karoo.co.uk...

Yes, it makes sense now, you nearly got the right method, what you need to
do is use the RefreshLink method of the tabledef object.

So if the table you've modified is called tblMyTable (this is it's name in
the local database, i.e. the linked table not the source table in the BE
db), you would do something like

Currentdb.Tabledefs("tblMyTable ").Refreshlink

If you wanted to do all the linked tables you can do something like:-

Dim loDB as DAO.Database
Dim loTab as as DAO.Tabledef

set loDb = CurrentDB

For Each lotab in loDB.Tabledefs
with loTab
if Len(.Connect)>0 then
.Refreshlink
end if
End with
next


--
Terry Kreft

"Jozef" <[e-mail]> wrote in message
news:uiJmf.145841$S4.61975@edtnps84...
<SNIP>

refreshing the attachments by doing Currentdb.TableDefs.Refresh, but
still

<SNIP>

(other than the indirect source table modifications). Does that make
sense?
"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:-Z********************@karoo.co.uk...

Firstly if your posting a problem where a particular error message is
given it's best to take the time to quote the full error rather than
quoting something you vaguely remember.

Are you refreshing the links to the BE tables that you are modifying? If
you don't do this you will get errors if the BE tables are changed.

--
Terry Kreft

"Jozef" <[e-mail]> wrote in message
news:c9qmf.142332$y_1.124960@edtnps89...
Hello,

I have an Access XP database that has attachments to a BE database that
has a password. The attachments work fine, until I run some code that
modifies a BE table (adding a field). I do this by running code
similar the following;

Public varDb as DAO.database
sub ConnectBEDatabase(strLocation as string)
Set varDb = OpenDatabase(strLocation, False, False, ";pwd=" &
GetPassword())
end sub

sub DisconnectBEDatabase()
set varDB=nothing
end sub

function GetPassword()
getpassword="MyPassword"
end function

public Sub UpdateBE()
ConnectBEDatabase("C:\MyDatabase.mdb")
-----code to add a field to a table-----

-----end code to add a field-----
DisconnectBEDatabase
end sub

The problem is, once I run the disconnect, my table attachments all of
a sudden say "Not a valid password" or something of the like. I'm not
modifying the connectd strings or anything, just modifying the back end
tabledef.

Any ideas?

Thanks!