Unable to addnew to ODBC db table?

[David F.]

Hello,

I have two different functions created in Access which I'm calling via a
macro to test. One works and the other one complains that the database is
read-only when issuing AddNew. What am I doing wrong?

This one Works and the table is updated correctly.
************************************************************
Option Compare Database

Public Function AddIP(tablename As String, ip As String, d As Date)
Dim db As Database
Dim rs As Recordset
Dim databasename As String

If ip = "" Then Exit Function
databasename = DLookup("[OrdersIPDSN]", "Settings", "[id]=1")

Set db = OpenDatabase("", dbDriverNoPrompt, False, "ODBC;DSN=" &
databasename)
Set rs = db.OpenRecordset(tablename, dbOpenDynaset)

rs.FindFirst ("IP='" & ip & "'")
If rs.NoMatch Then
rs.AddNew
rs![ip] = ip
rs![ODate] = d
rs.Update
End If

rs.Close
db.Close

End Function


This one does NOT works - AddNew gives read-only halt
************************************************************
Public Function ExportIPData()
Dim db As Database
Dim rsfrom As Recordset
Dim rsto As Recordset
Dim databasename As String
Dim d As Date

databasename = DLookup("[OrdersIPDSN]", "Settings", "[id]=1")
Set db = OpenDatabase("", dbDriverNoPrompt, False, "ODBC;DSN=" &
databasename)
Set rsto = db.OpenRecordset(tablename, dbOpenDynaset)
' tried this - same thing: Set rsto = db.OpenRecordset(tablename,
dbOpenDynaset, 0, dbOptimistic)

Set rsfrom = CurrentDb.OpenRecordset("oinfo", dbOpenDynaset, 0,
dbReadOnly)

While Not rsfrom.EOF
ip = Trim(rsfrom![IPAddress])
d = rsfrom![ODate]
If ip <> "" Then
If IsNull(d) Then
d = #1/1/1980#
End If

rsto.FindFirst ("IP='" & ip & "'")
If rsto.NoMatch Then
rsto.AddNew ' <<<<<<<<<<< Complains that it's read-only (3027)
rsto![ip] = ip
rsto![ODate] = d
rsto.Update
End If
End If
rsfrom.MoveNext
Wend

rsto.Close
rsfrom.Close
db.Close

End Function

 

[John W. Vinson]

Hello,

I have two different functions created in Access which I'm calling via a
macro to test. One works and the other one complains that the database is
read-only when issuing AddNew. What am I doing wrong?

Do you have a Primary Key in this table - and does Access know what that field
is? If not, it won't let you update.

John W. Vinson [MVP]

 

[David F.]

Hi,

It does, and it must know about it because that other function that works is
adding records to the same table in the same database. The only difference
is the one that doesn't work also opens a table in the current database and
reads from it.

 

[John W. Vinson]

Hi,

It does, and it must know about it because that other function that works is
adding records to the same table in the same database. The only difference
is the one that doesn't work also opens a table in the current database and
reads from it.

Please post your code.

John W. Vinson [MVP]

 

[John W. Vinson]

Hello,

I have two different functions created in Access which I'm calling via a
macro to test. One works and the other one complains that the database is
read-only when issuing AddNew. What am I doing wrong?

oops! Sorry - you DID post your code. I'll take some time to look at it and
get back to you. Apologies!

John W. Vinson [MVP]

 

[David F.]

No luck??

oops! Sorry - you DID post your code. I'll take some time to look at it
and
get back to you. Apologies!

John W. Vinson [MVP]

 

[John W. Vinson]

This one does NOT works - AddNew gives read-only halt
************************************************************
Public Function ExportIPData()
Dim db As Database
Dim rsfrom As Recordset
Dim rsto As Recordset
Dim databasename As String
Dim d As Date

databasename = DLookup("[OrdersIPDSN]", "Settings", "[id]=1")
Set db = OpenDatabase("", dbDriverNoPrompt, False, "ODBC;DSN=" &
databasename)
Set rsto = db.OpenRecordset(tablename, dbOpenDynaset)
' tried this - same thing: Set rsto = db.OpenRecordset(tablename,
dbOpenDynaset, 0, dbOptimistic)

Set rsfrom = CurrentDb.OpenRecordset("oinfo", dbOpenDynaset, 0,
dbReadOnly)

While Not rsfrom.EOF
ip = Trim(rsfrom![IPAddress])
d = rsfrom![ODate]
If ip <> "" Then
If IsNull(d) Then
d = #1/1/1980#
End If

rsto.FindFirst ("IP='" & ip & "'")
If rsto.NoMatch Then
rsto.AddNew ' <<<<<<<<<<< Complains that it's read-only (3027)
rsto![ip] = ip
rsto![ODate] = d
rsto.Update
End If
End If
rsfrom.MoveNext
Wend

rsto.Close
rsfrom.Close
db.Close

End Function

Sorry... I don't have any useful ideas. I'd suggest you start a new thread.
The only things that come to mind are the Primary Key in the backend table. If
you use File... Get External Data... Link, selecting ODBC connections, to link
to this table, is it updateable?

John W. Vinson [MVP]