Syntax to connect to A2K DB with a DB password using VBA

  • Thread starter Thread starter JoJoFromCoCoMo
  • Start date Start date
J

JoJoFromCoCoMo

I have a A2K DB with a DB password assigned PW="test" and
I want to relink tables using code. I need help witht he
syntax. I currently have code to work without a password
as follows:

Dim tdf As TableDef
Dim strDBName, strDBTable As String
Set dbsCurrDB = CurrentDb()
Set rst = dbsCurrDB.OpenRecordset("SELECT DISTINCT
LocalTable.DatabaseName, LocalTable.DatabasePath,
LocalTable.TableName FROM LocalTable;", dbOpenSnapshot)

With rst
rst.MoveFirst
For Each tdf In dbsCurrDB.TableDefs
If Not tdf.Name Like "*sys*"
And Not Len(tdf.Connect) = 0 _
And Not tdf.Name Like "LocalMaster" Then
tdf.Connect = ";DATABASE=" & rst!
DatabasePath & rst!DatabaseName
tdf.RefreshLink
End If
Next tdf
 
Sorry-Additional description:
A2k front end and A2k back end. I'm linking from the A2k
front to the back end DB having the password.
 
You stated that your code below works for a database without a password, but
there are a few lines of code missing from the code snippet you posted
(besides the obvious last line getting cut off). Specifically, a few
variable declarations are missing, like "dbsCurrDB" and "rst." If these
variables are also not declared in your own code module, then I urge you to
include "Option Explicit" in your current code modules and to turn this
feature on for all of your databases. This will save you from future wild
goose chases when logic errors are found. See the following Web page for
Tom Wickerath's explanation of why you should _always_ use "Option Explicit"
in your VBA code modules:

http://www.Access.QBuilt.com/html/gem_tips.html

I'd also like to point out that your declaration of "strDBName" (which, by
common convention, implies to other programmers that it is probably a String
data type) is declared as a Variant, because you didn't explicitly specify
any other data type in the declaration of this variable.

The syntax you need to relink tables in the other database is (watch out for
the word wrap):

tdf.Connect = ";DATABASE=" & rst!DatabasePath & rst!DatabaseName &
";PWD=test"

Bear in mind that this password will be stored in the system table for
anyone (who can open the current database, that is) to read.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
 
Back
Top