[AccessD] Connecting to SQL from Access w/a single login?

Hi Brett

>>First, I think this is for attached Jet databases only.
> No, I have used this extensively for attaching to our SQL Server 2000
> databases.

It won't hurt, it's just that if I check the attributes of some tables
linked from SQL Server where the password is saved in the connect
string, this attribute is NOT set.

>>Also, this should not be necessary: <snip>
> A couple of points here:
> 1. The TableDef object doesn't have a Refresh method.  Perhaps
> RefreshLink?

Yes.

> 2. If you try this, omit the tdf.SourceTableName = rs!ForeignName line.
> It blows up.

Yes, sorry, it is only for creating a linked tabledef. It makes no
sense for an already linked table.

> 3. I can't remember the exact reason why I dropped and recreated the
> table.  I can recall trying the simple update and RefreshLink but had
> difficulties.  Can't remember much more than that.  Seems to work OK
> now, though.

Well, after 10 years with Access, I've have yet to see this fail. I
believe you safely can use RefreshLink for reattaching a table when
server or file has been moved. It is way faster too.

/gustav


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
> Sent: Sunday, September 19, 2004 9:56 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Connecting to SQL from Access w/a single login?


> Hi Brett

>> Try setting tdAttach.Attributes = dbAttachSavePWD

> First, I think this is for attached Jet databases only.
> Second, I guess this should be:

>   tdAttach.Attributes = tdAttach.Attributes Or dbAttachSavePWD

> or you would kill any other attribute (or raise an error).

> Also, this should not be necessary:

>>    Do Until rs.EOF
>>        Debug.Print rs!Name
>>        Set tdf = db.CreateTableDef(rs!Name)
>>        
>>        tdf.Connect = "ODBC;" & _
>>            ExtractArg(rs!Connect, "DRIVER") & ";" & _
>>            "SERVER=" & strServer & ";" & _
>>            ExtractArg(rs!Connect, "DATABASE") & ";" & _
>>            "UID=" & strUID & ";" & _
>>            "PWD=" & strPWD
>>            
>>        tdf.Attributes = dbAttachSavePWD
>>        tdf.SourceTableName = rs!ForeignName
>>        db.TableDefs.Delete rs!Name
>>        db.TableDefs.Append tdf
>>        
>>        rs.MoveNext
>>    Loop

> A Refresh is normally fine (and faster):

>        Set tdf = db.TableDefs(rs!Name)
>        tdf.Connect = "ODBC;" & _ ...
>        tdf.Attributes = dbAttachSavePWD
>        tdf.SourceTableName = rs!ForeignName
>        tdf.Refresh

> /gustav


>> I'm using a similar routine currently.  I have a metadata table full 
>> of information about the data tables.  It contains the localname, path

>> and sourceobject name of the table I'm linking to in SQL Server.
>> The actual linking is fine, but as you said remembering the password 
>> is an issue.