Linking Tables in Password protected databases using VBA 
Author Message
 Linking Tables in Password protected databases using VBA

sorry to cross post - but I messed this NG earlier.

I'm developing, in Access 97 a 'front end' app linked to a
test database, also Access 97. There are 41 tables. When
testing, the front end is linked to the tst data. When it
goes to UAT I dlete the links manually, and re-create the
links to the UAT database - which naturally has the same
structure.  Manually, this means a lot of deletes, but the
links only require a password when the database is
selected.
I want to do this in VBA as we're moveing to several
databases as source data. I Have a table which tells me
the name of the table, its database, location and
password. What I'd like is the VBA code that cretaes the
link...

    DoCmd.TransferDatabase acLink, _
             "Microsoft Access", _
             "C:\TCM Database\Treasury.mdb", _
              acTable, "Agents", "Agents", False

This does work, BUT I need to enter the database password,
which is the same for each table.

thank you
Patrick Molloy



Sat, 10 Jan 2004 00:29:51 GMT  
 Linking Tables in Password protected databases using VBA
Try manipulating the Connect property of a tabledef.  Something like
follows:

...
dim db as dao.database
dim newdbname as string
dim i as long

newdbname="Your new data database location"
set db=currentdb
for i=0 to db.tabledefs.count-1
    if  tdf(i).connect<>"" then
        tdf(i).connect="DATABASE=" & newdbname
        tdf(i).refreshlink
    end if
next i
...

Dev Ashish has this more fully explained on his site at http://www.mvps.org
so you might care to stop by there.
_____________________________________


Quote:
> sorry to cross post - but I messed this NG earlier.

> I'm developing, in Access 97 a 'front end' app linked to a
> test database, also Access 97. There are 41 tables. When
> testing, the front end is linked to the tst data. When it
> goes to UAT I dlete the links manually, and re-create the
> links to the UAT database - which naturally has the same
> structure.  Manually, this means a lot of deletes, but the
> links only require a password when the database is
> selected.
> I want to do this in VBA as we're moveing to several
> databases as source data. I Have a table which tells me
> the name of the table, its database, location and
> password. What I'd like is the VBA code that cretaes the
> link...

>     DoCmd.TransferDatabase acLink, _
>              "Microsoft Access", _
>              "C:\TCM Database\Treasury.mdb", _
>               acTable, "Agents", "Agents", False

> This does work, BUT I need to enter the database password,
> which is the same for each table.

> thank you
> Patrick Molloy



Sun, 11 Jan 2004 05:15:17 GMT  
 Linking Tables in Password protected databases using VBA
Thanks Matt, I'll give it a try!

Patrick

Quote:
>-----Original Message-----
>Try manipulating the Connect property of a tabledef.  
Something like
>follows:

>....
>dim db as dao.database
>dim newdbname as string
>dim i as long

>newdbname="Your new data database location"
>set db=currentdb
>for i=0 to db.tabledefs.count-1
>    if  tdf(i).connect<>"" then
>        tdf(i).connect="DATABASE=" & newdbname
>        tdf(i).refreshlink
>    end if
>next i
>....

>Dev Ashish has this more fully explained on his site at
http://www.mvps.org
>so you might care to stop by there.
>_____________________________________



>> sorry to cross post - but I messed this NG earlier.

>> I'm developing, in Access 97 a 'front end' app linked
to a
>> test database, also Access 97. There are 41 tables. When
>> testing, the front end is linked to the tst data. When
it
>> goes to UAT I dlete the links manually, and re-create
the
>> links to the UAT database - which naturally has the same
>> structure.  Manually, this means a lot of deletes, but
the
>> links only require a password when the database is
>> selected.
>> I want to do this in VBA as we're moveing to several
>> databases as source data. I Have a table which tells me
>> the name of the table, its database, location and
>> password. What I'd like is the VBA code that cretaes the
>> link...

>>     DoCmd.TransferDatabase acLink, _
>>              "Microsoft Access", _
>>              "C:\TCM Database\Treasury.mdb", _
>>               acTable, "Agents", "Agents", False

>> This does work, BUT I need to enter the database
password,
>> which is the same for each table.

>> thank you
>> Patrick Molloy

>.



Fri, 16 Jan 2004 21:51:39 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. using data link with password protected access database

2. exporting tables between password protected MDB databases

3. exporting tables between password protected MDB databases

4. Join 2 tables which protected with password using SQL

5. Using MS Data Control DAO to access a password protected database

6. Using Access Reports with a password protected database

7. Accessing a password protected Access 97 database using the Jet Engine

8. Using Access Reports with a password protected database

9. Problem using Access 97 Password protected Database in VB

10. using crystal reports objectmodel and password protected Access database

11. Using CR8 with Access password protected Database

12. Howto connect to a password protected access-database by using ADO

 

 
Powered by phpBB® Forum Software