M
Mark
I'm using the following code to create a DSNLessLink to several tables in my
SQL 2005 DB. I have 2 UID's in the SQL 2005 DB, and I want to link the files
initially to the Read Only ID, and when I want to add a particular routine, I
want to relink the tables to the Read Write ID and once it's complete, relink
to the Read Only ID.
The linking works perfectly with 1 exception. The first time I run it in a
blank database, it links the files and the next time the routine runs, it
keeps the Read Only or Read Write access that it previously was linked as.
I've tried linking to the RW ID first, then the RO ID, and yet I can still
write/update the tables. I've set up a 2nd instance of the Access db, and
run the routine with the RO ID followed by the RW ID, and the tables can not
be updated.
I don't think it's a problem on the SQL 2005 database side, since I can get
it to work initially.
Thanks in advance.
Mark
Private Sub DSNLessLink(NewTableName As String, ServerName As String, _
DatabaseName As String, SourceTableName As String, UserID As String,
Password As String, _
Optional IsHidden As Boolean)
'Create links to an SQL database without requiring a DSN.
On Error GoTo Err_Handler
Dim strMsg As String
Dim DB As DAO.Database
Dim tdf As DAO.TableDef
If DCount("[Name]", "MSysObjects", "[Name]='" & NewTableName & "' AND
[Type] IN (1,4,6)") = 1 Then
DoCmd.DeleteObject acTable, NewTableName
End If
Application.RefreshDatabaseWindow
' Create a new TableDef object, using the DSN-less connection
Set DB = CurrentDb()
Set tdf = DB.CreateTableDef(NewTableName)
tdf.Connect = "ODBC;DRIVER={sql server};DATABASE=" _
& DatabaseName & ";SERVER=" & ServerName & ";UID=" & UserID & ";PWD=" &
Password & ";"
tdf.SourceTableName = SourceTableName
DB.TableDefs.Append tdf
If IsHidden Then
Application.SetHiddenAttribute acTable, NewTableName, True
End If
Exit_Routine:
Application.RefreshDatabaseWindow
Exit Sub
Err_Handler:
strMsg = "The following error occurred creating the link to " _
& ServerName & "." & DatabaseName & "." & SourceTableName & ":" & vbCrLf
& vbCrLf & Err.Description
MsgBox strMsg, vbCritical, "Error Creating Report"
GoTo Exit_Routine
End Sub
SQL 2005 DB. I have 2 UID's in the SQL 2005 DB, and I want to link the files
initially to the Read Only ID, and when I want to add a particular routine, I
want to relink the tables to the Read Write ID and once it's complete, relink
to the Read Only ID.
The linking works perfectly with 1 exception. The first time I run it in a
blank database, it links the files and the next time the routine runs, it
keeps the Read Only or Read Write access that it previously was linked as.
I've tried linking to the RW ID first, then the RO ID, and yet I can still
write/update the tables. I've set up a 2nd instance of the Access db, and
run the routine with the RO ID followed by the RW ID, and the tables can not
be updated.
I don't think it's a problem on the SQL 2005 database side, since I can get
it to work initially.
Thanks in advance.
Mark
Private Sub DSNLessLink(NewTableName As String, ServerName As String, _
DatabaseName As String, SourceTableName As String, UserID As String,
Password As String, _
Optional IsHidden As Boolean)
'Create links to an SQL database without requiring a DSN.
On Error GoTo Err_Handler
Dim strMsg As String
Dim DB As DAO.Database
Dim tdf As DAO.TableDef
If DCount("[Name]", "MSysObjects", "[Name]='" & NewTableName & "' AND
[Type] IN (1,4,6)") = 1 Then
DoCmd.DeleteObject acTable, NewTableName
End If
Application.RefreshDatabaseWindow
' Create a new TableDef object, using the DSN-less connection
Set DB = CurrentDb()
Set tdf = DB.CreateTableDef(NewTableName)
tdf.Connect = "ODBC;DRIVER={sql server};DATABASE=" _
& DatabaseName & ";SERVER=" & ServerName & ";UID=" & UserID & ";PWD=" &
Password & ";"
tdf.SourceTableName = SourceTableName
DB.TableDefs.Append tdf
If IsHidden Then
Application.SetHiddenAttribute acTable, NewTableName, True
End If
Exit_Routine:
Application.RefreshDatabaseWindow
Exit Sub
Err_Handler:
strMsg = "The following error occurred creating the link to " _
& ServerName & "." & DatabaseName & "." & SourceTableName & ":" & vbCrLf
& vbCrLf & Err.Description
MsgBox strMsg, vbCritical, "Error Creating Report"
GoTo Exit_Routine
End Sub