Linking tables via VBA

[J_Orrell]

Hi Guys...

Long time, no post...sorry, sadly I only tend to post on here when I get stuck

I'm trying to link tables in two databases (ie a front end and a back-end holding the data) using VBA. I've already had success with this using in my front end:

DoCmd.TransferDatabase acLink ,"Microsoft Access",<path of back-end database>,acTable,<source table in back-end>,<destination table in front-end>

...and this has worked fine. However in my latest release of my software I want to password-protect the back-end database using the standard "Set Database Password" function so that the back-end database cannot be manually opened. Obviously if I do this, the above command as it stands will require the user to type in the database's password, which somewhat defeats the objective. Any ideas how I pass-across the database's password in my link instructions?

Cheers

John

 

[Moniker]

The very last option in TransferDatabase is "StoreLogin". Straight from Access help:

StoreLogin
Optional Variant. Use True to store the login identification (ID) and password for an ODBC database in the connection string for a linked table from the database. If you do this, you don't have to log in each time you open the table. Use False if you don't want to store the login ID and password. If you leave this argument blank, the default (False) is assumed. This argument is available only in Visual Basic.

I'd imagine you can play around with that and get it to pass the password for you.

 

[J_Orrell]

Yeah I did see that, but it stops short of telling you where or how to pass-on the password, and that's where I'm stuck.

 

[Moniker]

I believe it's going to look something like this:

DoCmd.TransferDatabase acLink ,"Microsoft Access",<path of back-end database>,acTable,<source table in back-end>,<destination table in front-end>,,"PWD=YourPassword;"

I know that PWD=YourPassword is the right format for that parameter, but I'm not 100% positive on the implementation. I think playing around with that and perhaps searching a little more will dig up all the details.

 

[J_Orrell]

Thanks for that.

I've been having a play around but I still can't get it to work. I think the problem is that the last field of the TransferDatabase method is expecting either True or False rather than the password preceeded with the "PWD=" identifier. You'd think that there *should* be a parameter in the TransferDatabase method to specify a password, but I just can't get it to work. Very frustrating!

 

[Moniker]

I have code that does this in code at home, but I won't be home for several hours. (I'll be home around 5pm US Central time.) If no one's come up with it by then, I'll post it.

 

[DJkarl]

Thanks for that.

I've been having a play around but I still can't get it to work. I think the problem is that the last field of the TransferDatabase method is expecting either True or False rather than the password preceeded with the "PWD=" identifier. You'd think that there *should* be a parameter in the TransferDatabase method to specify a password, but I just can't get it to work. Very frustrating!

I never could get transferdatabase to work with a password protected db, I had to use tabledefs to link the tables.

To add a new link

Set dbs = CurrentDb
Set tblDef = dbs.CreateTableDef(LocalTableName)
tblDef.Connect = "MS Access;DATABASE=" & RemoteDBName & ";" & "PWD=YourPassword;"
tblDef.SourceTableName = TableInRemoteDB
dbs.TableDefs.Append tblDef
Set tblDef = Nothing
Set dbs = Nothing

 

[Moniker]

I made it home a little early and looked at my code. I did essentially the same thing DJKarl did (used a TableDef), so it looks like that will be the way to move forward from here. I thought I had it in a TransferDatabase, but apparently I thought wrong.

 

[J_Orrell]

Cheers guys. This is yet another one of those scenarios in VBA where, to me as reasonably-competent programmer (no expert by any means, but I can get by), VBA doesn't quite do what I expect and I end up thinking ""so how come Microsoft never thought of that?". 'TransferDatabase' so obviously should have an optional parameter which passes on a database password. DJkarl's code looks promising as a work-around so I'll give that a shot and let you know how I get on .

 

[J_Orrell]

It works!!

Thanks guys. The only side-effect seems to be that the front-end accesses the linked tables about 25% slower, for some reason. I'm not sure that the users will really notice this though.

 

[rstekeur]

I've been tring to do the same thing and haven't been able to get it to work

Function ReLinkTable(RTTable As String, RTDataPath As String)
Dim RTDB As Database

Dim RTLinkedTable As TableDef
Dim RTConnect As String
Dim rp As Variant
Set RTDB = DBEngine.Workspaces(0).databases(0)
For rp = 0 To RTDB.TableDefs.Count - 1
If UCase$(RTDB.TableDefs(rp).name) = UCase$(RTTable) Then
RTDB.TableDefs.Delete RTTable
Exit For
End If
Next rp
Set RTLinkedTable = RTDB.CreateTableDef(RTTable)

RTConnect = ";DATABASE=" & Left$(RTDataPath, 50) & ";" & "PWD=tms;"
'RTConnect = ";DATABASE=" & RTDataPath
RTLinkedTable.Connect = RTConnect
RTLinkedTable.SourceTableName = RTTable
RTDB.TableDefs.Append RTLinkedTable
RTDB.Close

End Function


comes back with invalid password, any ideas