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

From: '69 Camaro (Black_hole.To.69Camaro_at_Spameater.org)
Date: 08/03/04 

Date: Tue, 3 Aug 2004 14:07:51 -0700

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.

<anonymous@discussions.microsoft.com> wrote in message
news:9c5c01c478d5$dbd23490$a301280a@phx.gbl...
> 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.

"JoJoFromCoCoMo" <anonymous@discussions.microsoft.com> wrote in message
news:9cef01c478d2$039ca7c0$a501280a@phx.gbl...
> 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

Relevant Pages

  • Re: Performance concerns in SPs and Tables
    ... would expect memory to be allocated on declaration; for a varchar, ... procedural code; database access code should be declarative. ...
    (microsoft.public.sqlserver.programming)
  • Re: Access 97 - global variables??
    ... you can define variables in the declaration ... You can use a database property to get them remembered ... my database makes an ODBC connection to a back-end DB2 database ... where on the query properties a ODBC connect ...
    (microsoft.public.access.queries)
  • RE: Compile Error: Variable Not Defined
    ... Take out Option Explicit and see if it runs without it. ... on the VBA input window General Declaration see if it requires Option ... Login form was part of a database that already existed before I started ... If bUsePasswords = False Then ...
    (microsoft.public.access.modulesdaovba)
  • Re: Relink question
    ... you should Dim dbBE as DAO.Database. ... BTW, EVERY variable you use should be declared. ... using implicit declaration ... there is no law that says an Access database must have the extension ...
    (microsoft.public.access.formscoding)
  • Re: Option Explicit doesnt seem reliable
    ... I've never found Option Explicit to be unreliable. ... recent versions of Access, though, is that the VBA editor doesn't always ... >>> copying the database to another location and renaming it. ... >>> without declaring them in the original database. ...
    (microsoft.public.access.gettingstarted)
Loading