General discussion

Locked

SQL 7 access using ODBC

By dean.held ·
In Access 2000 if I link to a table in SQL 7 database I can edit the data. Hovever, if I use ODBC to go directly at the SQL 7 database I get an error:
"datbase or OBJECT is read only."

The code is as follows:

Dim dbs, dbsAreaCode, dbsTables, dbsConv, dbsConsd As DataBase
' Dim dbCons As Connection

Dim wks, wksTables, wksConv As Workspace

Dim rstPhone, rstConfig, rstConv, rstTables As Recordset
Dim strConnect, sUser As String

Dim sPassword As String
Dim sServer As String
Dim sDatabase As String
Dim sPhone As String
Dim sTable As String


Set wks = CreateWorkspace("", "admin", "", dbUseODBC)
strConnect = "ODBC;DSN=" + sOdbc + ";UID=" + sUser _
+ ";PWD=" + sPassword + ";SERVER=" + sServer + ";DATABASE=" + sDatabase

Set dbCons = wks.OpenConnection("con1", , False, strConnect)
Set rstPhone = dbCons.OpenRecordset(sTable, dbOpenDynamic)

' Set wks = CreateWorkspace("", "admin", "", dbUseJet)
' Set dbConsd = wks.OpenDatabase(sDatabase, dbDriverNoPrompt, False, strConnect)

'
'Get first table to process Area code changes
'
With rstPhone
rstPhone.MoveFirst
While Not rstPhone.EOF
.Edit

WHen it hits the edit I get the error.

Any ideas?

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

SQL 7 access using ODBC

by roc180 In reply to SQL 7 access using ODBC

Ok, I'm not positive in Access 2000 but in Access 97 with your code rstPhone is a varient data type. Change your code to:
Dim rstPhone As Recordset. I would also recomment simplifying your code:

Sub phoneEdit()
Dim dbsConsd As Database Dim dbCons As Connection
Dim wks As Workspace, wksConv As Workspace
Dim rstPhone As Recordset, rstTables As Recordset
Dim strConnect As String, sUser As String
Dim sPassword As String
Dim sServer As String
Dim sDatabase As String
Dim sPhone As String
Dim sTable As String

Set wks = CreateWorkspace("", "admin", "", dbUseODBC)

'***(Old Code)****************
'strConnect = "ODBC;DSN=" + "rpdev" + ";UID=" + "martinen" _
+ ";PWD=" + "nm2468" + ";SERVER=" + "wsqls005" + ";DATABASE= rtlprc01"
'Set dbCons = wks.OpenConnection("con1", , False, strConnect)
'**********************************
Set dbCons = wks.OpenConnection("con1", dbDriverNoPrompt, , "ODBC;DATABASE=Your_DB_Name;UID=Your_Id;PWD=Your_PWD;DSN=DSN_Name")

Set rstPhone = dbCons.OpenRecordset("Table_Name", dbOpenDynamic)

'Get first table to process Area code changes

With rstPhone
.MoveFirst

While Not .EOF
.Edit
!someField = strData
.Update

.MoveNext
Wend
.Close
End With
Set rstPhone = Nothing
End Sub

If you have any problems with the code drop me a line. I wouldalso highly recomend checking out ADO 2.5. It is the native way Access 2000 connects to databases. It is a lot easier, faster and cleaner than your DAO code.

Good Luck

Neal Martinelli

Collapse -

SQL 7 access using ODBC

by dean.held In reply to SQL 7 access using ODBC

Set rstPhone = dbCons.OpenRecordset("Table_Name", dbOpenDynamic)

The issue was with the open statement and using the right static vars to be able to read write.

Thanks for the try though

Collapse -

SQL 7 access using ODBC

by dean.held In reply to SQL 7 access using ODBC

This question was closed by the author

Back to Software Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums