Wednesday, April 01, 2009

Linking Access to SQL Server

I have an Access app that links to SQL Server, and when I tried to attach a new SQL table in code I ran into a snag - the linked table would forget the user name & password, so Access would pop up one of those login dialog boxes.

I compared the value of the table Attributes (a long integer) & discovered that my new table was different. A quick trip to Access Help was truly helpful - I was missing the attribute dbAttachSavePWD so basically here is what worked (the fix is in bold):

Sub SQL_AttachOne(TableName As String)
Dim db As DAO.Database, tdf As DAO.TableDef
Const s = "the connection string goes here"
Set db = CurrentDb
Set tdf = db.CreateTableDef
With tdf
.Name = TableName
.SourceTableName = TableName
.Connect = s
.Attributes = .Attributes Or dbAttachSavePWD
End With
db.TableDefs.Append tdf
End Sub

The Or operator sets the appropriate flag for Access to save the info.

No comments: