Access Feature or Bug?

[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. They both update the same file, the only
difference is that one reads from a local table. What's the problem?

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

 

[Albert D. Kallal]

Are you talking about a sql server backend, or just a jet (shared) mdb back
end?

If a jet share, then just try:

Set db = OpenDatabase("path to mdb file")

Further, any reason why linked tables are not being used?

 

[David F.]

The server is Firebird 2 and works without linked tables in that first
example, but in the second one, it says it's read-only? I didn't try
linking the tables since it worked in the first example and I may want to
open the mdb for other work when the firebird server is shutdown (maybe the
linked table wouldn't interfere?).

Is there some kind of limitation of opening a local table while trying to
update a remote table via odbc?

 

[aaron.kempf]

dude you're a fucking retard

if you want a real database use SQL Server, MSDE or SQL Server 2005
express


seriously kid, Firebird?

are you a dipshit or just a faggot?

 

[Albert D. Kallal]

I would test with linked tables. In fact, I would launch a 2nd copy of the
application with that table open...., and simply again open up that table to
see if you edit it...

It is possible that the file is open exclusive already. Thus, a linked table
would work, but opening a database connection such as you have would
fail...(if that back end is already opened by the current program, or
someone else).

Note that if users don't have create rights to that directory, then the ldb
locking file can't be created in that dir..and ms-access will default to
read only operation...

So, make sure users have create rights. (they don't have to have delete
rights..but, they should also, as that allows ms-access to delete the ldb
locking file upon exit).

That file server has to support windows network file locking, and that why I
suggesting to test this manually with out code to see if you can get more
then one user in (and, starting a new connection as you have is much like a
2nd user opening the file...that why I am suggesting to use linked tables.
as then you not opening two seperate connections (users) to that back end
file....).

 

[David F.]

Someone pointed out that "tablename" isn't a parameter in the second version
which is probably the problem. I'm sure that's it... (haven't tried yet).

 

[aaron.kempf]

stop blaming everything else under the sun

MDB is unreliable; and you're a retard if you're still using it