A
Al
I am using the following code to establish a DSN less connection:
**************************
Private Sub Form_Load()
Dim db As dao.Database
Dim tdf As dao.TableDef
Dim rst As dao.Recordset
Dim strServer As String
Dim strDB As String
Dim strTable As String
Dim strConnect As String
Dim strMsg As String, id As String, pw As String
id = "DR_RV"
pw = "drawingreview"
On Error GoTo HandleErr
strConnect = "ODBC;Driver={SQL Server};UID=" _
& id & ";PWD=" & pw & ";"
' Build base authentication strings
'Select Case Me!optAuthentication
' Windows/NT login
' Case 1
' strConnect = "ODBC;Driver={SQL Server};Trusted_Connection=Yes;"
' SQL server login
' Case 2
' strConnect = "ODBC;Driver={SQL Server};UID=" _
' & Me.txtUser & ";PWD=" & Me.txtPwd & ";"
'End Select
' Get rid of old links, if any
Call DeleteLinks
' Create recordset to obtain server, database and table names
Set db = CurrentDb
Set rst = db.OpenRecordset("tblSQLTables", dbOpenSnapshot)
If rst.EOF Then
strMsg = "There are no tables listed in tblSQLTables."
GoTo ExitHere
End If
' Walk through the recordset and create the links
Do Until rst.EOF
strServer = rst!SQLServer
strDB = rst!SQLDatabase
strTable = rst!SQLTable
' Create a new TableDef object
Set tdf = db.CreateTableDef(strTable)
' Set the Connect property to establish the link
tdf.Connect = strConnect & _
"Server=" & strServer & _
";Database=" & strDB & ";"
tdf.SourceTableName = strTable
' Append to the database's TableDefs collection
db.TableDefs.Append tdf
rst.MoveNext
Loop
'strMsg = "Tables loaded successfully."
rst.Close
Set rst = Nothing
Set tdf = Nothing
Set db = Nothing
ExitHere:
'MsgBox strMsg, , "Link SQL Tables"
Exit Sub
HandleErr:
Select Case Err
Case Else
strMsg = Err & ": " & Err.Description
Resume ExitHere
End Select
End Sub
********************************
The database in sql was originally an access 2003 database. I converted the
database to sql using the Microsoft SQL Server Migration Assistant for Access.
all tables have there pk and fk established and links are in effect. I
created an account on the sql server with ID and PW as stated above in the
code (IDR_RV, PW: drawingreview). I am able to link the tables but if I
tried to enter any data, it says that it is a read only db. I have checked
all the rights on the sql server end. I logged in as the DR_RV (SQL
Authentication) user and was able to insert and update. Any ideas?
thanks
Al
**************************
Private Sub Form_Load()
Dim db As dao.Database
Dim tdf As dao.TableDef
Dim rst As dao.Recordset
Dim strServer As String
Dim strDB As String
Dim strTable As String
Dim strConnect As String
Dim strMsg As String, id As String, pw As String
id = "DR_RV"
pw = "drawingreview"
On Error GoTo HandleErr
strConnect = "ODBC;Driver={SQL Server};UID=" _
& id & ";PWD=" & pw & ";"
' Build base authentication strings
'Select Case Me!optAuthentication
' Windows/NT login
' Case 1
' strConnect = "ODBC;Driver={SQL Server};Trusted_Connection=Yes;"
' SQL server login
' Case 2
' strConnect = "ODBC;Driver={SQL Server};UID=" _
' & Me.txtUser & ";PWD=" & Me.txtPwd & ";"
'End Select
' Get rid of old links, if any
Call DeleteLinks
' Create recordset to obtain server, database and table names
Set db = CurrentDb
Set rst = db.OpenRecordset("tblSQLTables", dbOpenSnapshot)
If rst.EOF Then
strMsg = "There are no tables listed in tblSQLTables."
GoTo ExitHere
End If
' Walk through the recordset and create the links
Do Until rst.EOF
strServer = rst!SQLServer
strDB = rst!SQLDatabase
strTable = rst!SQLTable
' Create a new TableDef object
Set tdf = db.CreateTableDef(strTable)
' Set the Connect property to establish the link
tdf.Connect = strConnect & _
"Server=" & strServer & _
";Database=" & strDB & ";"
tdf.SourceTableName = strTable
' Append to the database's TableDefs collection
db.TableDefs.Append tdf
rst.MoveNext
Loop
'strMsg = "Tables loaded successfully."
rst.Close
Set rst = Nothing
Set tdf = Nothing
Set db = Nothing
ExitHere:
'MsgBox strMsg, , "Link SQL Tables"
Exit Sub
HandleErr:
Select Case Err
Case Else
strMsg = Err & ": " & Err.Description
Resume ExitHere
End Select
End Sub
********************************
The database in sql was originally an access 2003 database. I converted the
database to sql using the Microsoft SQL Server Migration Assistant for Access.
all tables have there pk and fk established and links are in effect. I
created an account on the sql server with ID and PW as stated above in the
code (IDR_RV, PW: drawingreview). I am able to link the tables but if I
tried to enter any data, it says that it is a read only db. I have checked
all the rights on the sql server end. I logged in as the DR_RV (SQL
Authentication) user and was able to insert and update. Any ideas?
thanks
Al